Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 2 of 2

Thread: Creating Import XLS and/or XML

  1. #1
    bdamore is offline Member
    Join Date
    Jun 2011
    Posts
    52

    Question Creating Import XLS and/or XML

    Hi all,
    I'm having trouble populating the sample xls file I have downloaded from the aspdnsf site. It's for ver 8. I've been trying to use a few diff methods but I cannot for the life of me, populate this XLS... any ideas?
    I was first using imp/exp wiz in mssql.
    Then I tried a SSIS package.
    and using
    INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\blank.xls;','Select
    ProductName,
    blah blah...

    but somehow the columns don't ever line up.
    In the SSIS pkg it was showing F21, F22, etc as header names instead of ProductID, etc.. What do I do?
    I have my SQL statements ready to go get the prods from the old DB... but how to stuff them into a XLS?...
    Here's one of my attempts:
    (p.s. I've tried a blank.xls and the ml8Excelsample.xls from the aspdnsf site) I've also counted my columns and such but something's amiss...
    =================================================

    USE [CLIENTDB]

    INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\blank.xls;','Select
    ProductName,
    ProductTypeID,
    Manufacturer,
    Distributor,
    Category1,
    Category2,
    Category3,
    Category4,
    Section1,
    Section2,
    Section3,
    Section4,
    Summary,
    [Description],
    SEKeywords,
    SEDescription,
    SETitle,
    [SKU],
    ManufacturersPartNumber,
    XmlPackage,
    Colwidth,
    SalesPromptID,
    Published,
    RequiresRegistration,
    RelatedProducts,
    MiscText,
    TrackInventoryBySizeAndColor,
    TrackInventoryBySize,
    TrackInventoryByColor,
    IsAKit,
    IsAPack,
    PackSize,
    ImageFileNameOverride,
    ExtensionData,
    SEAltText,
    VariantName,
    IsDefaultVariant,
    SKUSuffix,
    ManufacturersPartNumber,
    [Description],
    SEKeywords,
    SEDescription,
    SETitle,
    [Price],
    SalePrice,
    MSRP,
    Cost,
    [Weight],
    Dimensions,
    Inventory,
    DisplayOrder,
    Colors,
    ColorSKUModifiers,
    Sizes,
    SizeSKUModifiers,
    IsTaxable,
    IsShipSeparately,
    IsDownload,
    DownloadLocation,
    Published,
    ImageFileNameOverride,
    ExtensionData,
    SEAltText
    from [Sheet1$]')

    SELECT
    TBL_Products.ProductName,
    'Generic Product' AS ProductTypeID,
    TBL_Brands.nameEN AS Manufacturer,
    'Dist1' AS Distributor,
    TBL_Categories.nameEN AS Category1,
    '' AS Category2,
    '' AS Category3,
    '' AS Category4,
    '' AS Section1,
    '' AS Section2,
    '' AS Section3,
    '' AS Section4,
    '' AS Summary,
    TBL_Products.DescriptionEN AS [Description],
    '' AS SEKeywords,
    '' AS SEDescription,
    '' AS SETitle,
    TBL_Products.SKU as [SKU],
    TBL_Products.SKU AS ManufacturersPartNumber,
    'product.simpleproduct.xml.config' AS XmlPackage,
    4 AS Colwidth,
    1 AS SalesPromptID,
    0 AS Published,
    1 AS RequiresRegistration,
    '' AS RelatedProducts,
    TBL_Products.Specifications AS MiscText,
    0 AS TrackInventoryBySizeAndColor,
    0 AS TrackInventoryBySize,
    0 AS TrackInventoryByColor,
    0 AS IsAKit,
    0 AS IsAPack,
    '' AS PackSize,
    '' AS ImageFileNameOverride,
    '' AS ExtensionData,
    '' AS SEAltText,
    TBL_Products.ProductName AS VariantName,
    1 AS IsDefaultVariant,
    '' AS SKUSuffix,
    TBL_Products.SKU AS ManufacturersPartNumber,
    TBL_Products.DescriptionEN AS [Description],
    '' AS SEKeywords,
    '' AS SEDescription,
    '' AS SETitle,
    TBL_Products.Price as [Price],
    '' AS SalePrice,
    '' AS MSRP,
    '' AS Cost,
    '' AS [Weight],
    '' AS Dimensions,
    TBL_Products.Qty AS Inventory,
    '' AS DisplayOrder,
    '' AS Colors,
    '' AS ColorSKUModifiers,
    '' AS Sizes,
    '' AS SizeSKUModifiers,
    1 AS IsTaxable,
    0 AS IsShipSeparately,
    0 as IsDownload,
    '' AS DownloadLocation,
    0 AS Published,
    '' AS ImageFileNameOverride,
    '' AS ExtensionData,
    '' AS SEAltText

    FROM TBL_Categories INNER JOIN
    TLK_Products_Categories ON TBL_Categories.ID = TLK_Products_Categories.FK_CategoryID INNER JOIN
    TBL_Products ON TLK_Products_Categories.FK_ProductID = TBL_Products.ID LEFT OUTER JOIN
    TBL_Brands ON TBL_Products.FK_BrandID = TBL_Brands.ID

  2. #2
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    What is your source database? Is it a MS SQL Server db? And if it is are you using MS SQL Server Management Studio to run your second statement? Because if you are then all you need to do is select all of the results and then copy and paste into your Excel spreadsheet.
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!