Import Products table into AC7 database

[Mike718NY]

I want to Import a Products table from the old website to the one in AC7.
The 6 fields are:

Name
Price
Sale Price
ManufacturerID
SKU
Description

There is over 3,000 rows. Is it possible to do this? thanks

[jmestep]

Yes, this is possible. Is it an old Able 5 website or something else? If something else, you should be able to put the info into a .csv file using the heading names Able uses or even using your own, then mapping them to Able fields during the product csv import routine in the DataPort.

[Mike718NY]

thanks Judy,
This table is coming from an SQL Server 2005 database.
What is the "DataPort"? Should I use that or just use
the DTS in SQL Server?

[jmestep]

Here is a link to the dataport. The first post gives the download location for the most recent build. Definitely use it instead of DTS so that you get everything hooked up OK- there is more than one table involved.
You need to set up your categories first, then the csv file you import should use those category names. If there are any blank fields, it will use the default, like the default warehouse for example.

[Mike718NY]

This Products table doesn't really have any Category fields yet.

On a related note, I have a ManufacturerID in the table.
Do I need to load the ManufacturerName and ManufacturerID into a Manufacturer table?
And if I had CategoryID's would I do the same for a Categories table?

[jmestep]

The manufacturer and category upload work with name, not ID. But I think you would need to add those into the store before you try to use them in an upload file. I had a client whose upload kept failing because he hadn't added the warehouse name he was using in the file and I'm guessing manufacturer would be the same.
Your column headings don't have to match Able exactly because you can map them in the dataport, but there are some things you have to include, like catgories and product names.

[Mike718NY]

>> The manufacturer and category upload work with name, not ID.
It would seem to be bad practice to put Category names instead of
ID's in the products table. Is this how it is done?

I do not have the database installed yet, but is there a
ManufacturerID field and CategoryID field in the Products table?

Is there a Manufacturer table that has an ID and Name field?

[jmestep]

I've looked at export/import files for products. The xml has ManufacturerID and Manufacturer, CategoryID and Path. I had only looked at the .csv files before
In the database:
Manufacturer table has ManufacturerID, StoreID, Name.
Product table has ManufacturerID
Catalog table has CategoryID and the ProductID in a different field.
If you pm me your email address, I can send a short .csv and .xml export file for products if that would help.

[Mike718NY]

I found the database schema :
http://wiki.ablecommerce.com/images/3/3 ... Schema.pdf

Do you know what the "StoreID" is and if I should worry about it:

ac_Manufacturers table:
ManufacturerId int
StoreId
Name

Also, what is the "ParentID"? This is from the DataPort documentation:

Required fields for Categories CSV import:
Name
ParentId

http://wiki.ablecommerce.com/index.php/ ... CSV_import:

Would this ID be the "main" ID for the Category?
If I went into the Products Admin and created a Category (and no sub-categories)
would this be that ID? thanks

[Naveed]

>> The manufacturer and category upload work with name, not ID.
It would seem to be bad practice to put Category names instead of
ID's in the products table. Is this how it is done?

No the Category and Manufacturer names are not kept in the products table instead these are kept in their own tables. But the CSV import can import properly even if you specify the names, the Categories will be created if not found, otherwise the products will be linked to the existing category. Same is the case for manufacturere.

[Naveed]

Hello Mike,
There are not many complications in your case:

you can easily do like the following example to import your products in CSV format. Consider the following CSV data which I created while keeping in mind your situation:

Code: Select all

Name,Price,MSRP,Manufacturer,Sku,Description,Categories
Product1,24,23,ABC manufacturers ltd.,SKU232,Description of the product1,My Category
Product2,34,33,ABC manufacturers ltd.,SKU2df3,Description of the product2,My Category
Product3,22,20,ABC manufacturers ltd.,SKU2dfc,Description of the product3,My Category2
Product4,45,44,ABC manufacturers ltd.,SKU2343,Description of the product4,My Category3

The first line is the CSV header, I have changed the header field names to respective AbleCommerce Product table fields, like from Sale Price to MSRP etc.

Instead of worrying about ManufacturerId, just specify the Manufacturer name, and a manufacturer will be created and linked to the product if it does not exists already otherwise if it already exists then the existing manufacturer with the same name will be associated with the products.

Same way you can specify the category name, and the category will be auto associated with the product.

To import the above CSV save it to a CSV file, Connect the latest DataPort to your store, navigate to Products CSV import page from the left navigation and choose the CSV file. The CSV fields will be automatically mapped to respective AbleCommerce fields. Click next to import and select upload to import your CSV data.

upon successful import, 3 categories will be created ( My Category, My Category2, My Category3). and products will be imported to respective categories. Also a manufacturer (ABC manufacturers ltd.) will be created and products will be linked to that.

You can find a lot of DataPort related information here:
viewtopic.php?f=42&t=5707

[Mike718NY]

Naveed, thank you for that information.

>> just specify the Manufacturer name, and a manufacturer will be created and linked to the >> product if it does not exists . .

Just to clarify, I'm going import the 'Names' of the Manufacturers,
and a ManufacturerID and Manufacturer Name will be created in the Manufacturers table
(and same for the Categories).

Questions:
1. Right now I do not have any 'Category Name' field. Should I create a bunch
of Categories now for some of the products (I can do this in SQL quickly)?

2. For the products that are not going to be in a certain Category, should I just
leave that field 'NULL' or create a "No Category" name or something?

Also, I am going to add a 'SearchTerms' field and a 'Active' field to the Import.
The current Active field is a nvarchar(1) with a value 'N' or 'Y'.
Should I change the value to "False" and "True"?
Thanks

[Naveed]

Just to clarify, I'm going import the 'Names' of the Manufacturers,
and a ManufacturerID and Manufacturer Name will be created in the Manufacturers table
(and same for the Categories).

Yes

Questions:
1. Right now I do not have any 'Category Name' field. Should I create a bunch
of Categories now for some of the products (I can do this in SQL quickly)?

2. For the products that are not going to be in a certain Category, should I just
leave that field 'NULL' or create a "No Category" name or something?

Also, I am going to add a 'SearchTerms' field and a 'Active' field to the Import.
The current Active field is a nvarchar(1) with a value 'N' or 'Y'.
Should I change the value to "False" and "True"?

I am not clear that your old website is an AC55 website or some other and assuming it some other website. However if it is an AC55 website then you can use the AC55 import option.

Regarding product CSV import you can import following fields only:
Complete list of fields for Products CSV Import:

Code: Select all

ProductId
StoreId
Name
Price
CostOfGoods
MSRP
Weight
Length
Width
Height
Manufacturer
Sku
ModelNumber
DisplayPage
TaxCode
Shippable
WarehouseId
Warehouse
InventoryModeId
InStock
InStockWarningLevel
ThumbnailUrl
ThumbnailAltText
ImageUrl
ImageAltText
Summary
Description
ExtendedDescription
Vendor
CreatedDate
LastModifiedDate
ProductTemplate
IsFeatured
IsProhibited
AllowReviews
AllowBackorder
WrapGroup
ExcludeFromFeed
HtmlHead
DisablePurchase
MinQuantity
MaxQuantity
VisibilityId
Theme
IconUrl
IconAltText
IsGiftCertificate
Categories
UseVariablePrice
MinimumPrice
MaximumPrice
SearchKeywords
HidePrice

It is up to you that how you create your csv data, by using some programming skills or manually.

Regarding the boolean fields the value should be either "true" or "false".

Regarding the Category name field you should better specify a category name in CSV data. If no category name will be specified then products will be treated as orphaned catalog items and you can find those at website -> Admin -> Catalog -> Orphaned Items menu.

For You I recommend to create some products and categories at website and then export their data in CSV format and examine that for a deeper experience. This way you will better be able to understand how it will work.

[Mike718NY]

thanks for that, . .

What is the best way to create a csv data file?
The only way I can think of is using SQL Server Export,
to Excel 97-2005, then save that file as .csv.

I tried it but there is going to be a big problem with my Description field, which
has a lot HTML in it, up to 76316 characters. Fields look like this:
<TABLE cellSpacing=1 cellPadding=1 width="95%" border=0 . . . .
It's not picking-up all the characters like:
Wellness Herbal Resistance™ Liquid
which it exported like: Wellness Herbal Resistance? Liquid

This Description field is an "ntext" type field. I guess I would loose those characters
when it is eventually exported to the varChar field. Is that right? thanks

[jmestep]

You could try using FORXML export from SQL Server then tacking the additional tags Able needs to the beginning and ending of the file. Just define your xml tags for the product fields you are going to use.Here is an xml export of one Able product-- your xml import would need to be in the same format, but you wouldn't have to include the fields you don't have data for.
(I stripped out the stuff at the top for store settings)

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<AbleCommerceExport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Products>
      <Product>
        <ProductId>1241</ProductId>
        <StoreId>1</StoreId>
        <Name>Marshmallow Shooter</Name>
        <Price>7.99</Price>
        <CostOfGoods>0</CostOfGoods>
        <MSRP>9.95</MSRP>
        <Weight>1.00</Weight>
        <Length>0</Length>
        <Width>0</Width>
        <Height>0</Height>
        <ManufacturerId>187</ManufacturerId>
        <Manufacturer>Direct Paintball</Manufacturer>
        <Sku>Toy-DP-MsSh</Sku>
        <ModelNumber />
        <DisplayPage />
        <TaxCodeId>1</TaxCodeId>
        <TaxCode>Taxable</TaxCode>
        <ShippableId>1</ShippableId>
        <Shippable>Yes</Shippable>
        <WarehouseId>1</WarehouseId>
        <Warehouse>Direct Paintball</Warehouse>
        <InventoryModeId>0</InventoryModeId>
        <InStock>0</InStock>
        <InStockWarningLevel>0</InStockWarningLevel>
        <ThumbnailUrl>~/Assets/pimages/t/Toy-XT-MsSh-tb.jpg</ThumbnailUrl>
        <ThumbnailAltText>Marshmallow Shooter</ThumbnailAltText>
        <ImageUrl>~/Assets/pimages/t/Toy-XT-MsSh.jpg</ImageUrl>
        <ImageAltText>Marshmallow Shooter</ImageAltText>
        <Summary>Shoot a mini marshmallow up to 50 feet.
<BR>Buy three, get one free!</Summary>
        <Description>TruncatedbyJudy</Description>
        <VendorId>0</VendorId>
        <CreatedDate>2008-04-30T08:25:22</CreatedDate>
        <LastModifiedDate>2008-06-28T16:57:36</LastModifiedDate>
        <ProductTemplateId>0</ProductTemplateId>
        <IsFeatured>false</IsFeatured>
        <IsProhibited>false</IsProhibited>
        <AllowReviews>false</AllowReviews>
        <AllowBackorder>false</AllowBackorder>
        <WrapGroupId>0</WrapGroupId>
        <ExcludeFromFeed>false</ExcludeFromFeed>
        <DisablePurchase>false</DisablePurchase>
        <MinQuantity>0</MinQuantity>
        <MaxQuantity>0</MaxQuantity>
        <VisibilityId>0</VisibilityId>
        <Theme />
        <IconUrl />
        <IconAltText>Marshmallow Shooter</IconAltText>
        <IsGiftCertificate>false</IsGiftCertificate>
        <CouponProduct />
        <ExtendedDescription />
        <Categories>
          <Category>
            <CategoryId>361</CategoryId>
            <Path>Miscellaneous:Marshmallow Shooters/Rockets</Path>
            <OrderBy>-1</OrderBy>
          </Category>
        </Categories>
      </Product>
     </Products>
  </Store>
</AbleCommerceExport>

[Mike718NY]

thanks Judy, that looks like a good idea
I couldn't find FORXML export from SQL Server 2005.
It only had SQLXMLOLEDB and SQLXMLOLEDB 4.0 from the Export drop down.

[jmestep]

It looks like there are plenty of Google posts on xml and SQL 2005. Here's one to start:
http://www.15seconds.com/issue/050803.htm

[Mike718NY]

I think I'm just going to try the .CSV file first and hope for the best.

I noticed the field: <VisibilityId>0</VisibilityId>

I'm guessing this is whether the field is visible or not.
I want to include a Visibility field in my export but I'm not sure how it works:
0 = visible (public) ?
1 = ?
2 = ?
Visibility has 3 options in the Admin: Public, Hidden, and Private
Do you know anything about this field? thanks

[Naveed]

Visibility has 3 options in the Admin: Public, Hidden, and Private
Do you know anything about this field?

There are tool tips associated with many of the fields at admin. These are shown when you hover your mouse over the label for a while. The tool tip for the Product Visibility states:

Code: Select all

Visibility setting indicates how this product is accessed from the retail side.
Public: Published
Hidden: Unpublished (available through direct link)
Private: Access Prevented

public / published means that the product will be visible to all users on your store.
Hidden / unpublished means that the product will not be listed on your store however if a user have a direct link of a product page he can type that and can browse the product
Private means the product will not be accessible at store by any means.

[Naveed]

0 = visible (public) ?
1 = ?
2 = ?

Here are respective int values, which you can use while creating CSV:

0 = Public
1 = Hidden
2 = Private

[Mike718NY]

Just a few more things I'm not sure about it:

Most of the items in the Products table won't have a Category. The Manufacturer
will serve more of a category for the products.

If someone does a product search, will these items still appear?

Also, will this make it more difficult to add those products to Categories created later?

I am not going to first create the Categories (or Manufacturer) in the Admin,
but just have them automatically created during the Import.
Most of the rows in the Category fields will be NULL. Is this ok? thanks

[Mike718NY]

Didn't notice this until now, but the 95% of the Name field rows have commas:

NOW L-Carnitine, 500 mg, 180 caps

I guess this won't work:

Code: Select all

Name,Price,MSRP,Manufacturer,Sku,Description,Categories
Product1,24,23,ABC manufacturers ltd.,SKU232,Description of the product1,My Category
.............

I would need to change the text delimiter but not sure how to do that right now.

[lab_n_chemicals]

I looked at the ac_Products table and it does not show a categories field.
What gives?

[sohaib]

I looked at the ac_Products table and it does not show a categories field.
What gives?

Short answer is : ac_CatalogNodes table

For a more understanding please have a look at
http://wiki.ablecommerce.com/index.php/ ... Categories
viewtopic.php?f=42&t=6810

[Mike718NY]

The ac_Products table has a categoryID field.