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