DataPort problem

[Mike718NY]

My .CSV has these fields:

Name|Manufacturer|Categories|Sku|SearchKeywords|Description|Price|MSRP|ImageUrl|ImageAltText|HtmlHead|VisibilityId

I used Manual Mapping, selected the pipe as delimeter,
and matched the "Able Commerces fields" to "Your Import Sheet"
using the drop down lists in the Grid.

When I hit Next, then Import, I get this message: "No product avaialbe for Import"

Do I need to have all the fields in the CSV file, even if they contain no data?

[Mike718NY]

Here is where I'm at now:

[sohaib]

No you don't need to have all the fields. For importing products the only required field is 'Name'.
More details here http://wiki.ablecommerce.com/index.php/ ... rt_Utility

Don't know why you are getting an error. Can you for the sake of testing create a few products CSV with standard delimiter and text qualifier?

[Mike718NY]

I can't use the comma delimiter because my description field
has a lot of commas in it.

I just noticed it says "Please select xml file: " where I opened by .csv file, at the top.
Is that a mistake? I did select CSV Upload from the menu.

[sohaib]

I can't use the comma delimiter because my description field
has a lot of commas in it.

You can use comma delimiter . The purpose of Text Qualifier is just that : Distinguishing Coma in text vs Coma as separator.

This will work

Code: Select all

Name, Description, SKU
"My Test Product, with coma", "A long description with comas, full stops anthing", "MTPP"
"Another Product, with coma", "Another description with comas, It has coma also", "ATPP"

I just noticed it says "Please select xml file: " where I opened by .csv file, at the top

This is a typo on our part ... It has been fixed recently.

[Mike718NY]

The comma delimeter did work, thanks sohaib
But it inserted 3000 blanks records also . . I only made the .csv file
with 2 records for a test and deleted the other 3000 , but it
still created them, blank.
I backed up the database before, but now I get an error message when I Restore:

"the media set has two medi families but only one are provided. All
members must be provided".

Not sure what this means. I just did a regular backup and I"m using that one file.

[sohaib]

The comma delimeter did work, thanks sohaib
But it inserted 3000 blanks records also . . I only made the .csv file
with 2 records for a test and deleted the other 3000 , but it
still created them, blank.

This is strange. I wonder why it created exactly the same number of records that was in the original file ... probably a bug.
Another bug seems to be that non default separator is not working ... can you share your file (or part of it) so that we can confirm.

"the media set has two medi families but only one are provided. All
members must be provided".

I have no idea what this means.... I guess the 'Ablecommerce Angel' Judy with her was experience in backup and restore might have seen something like this before.

[Naveed]

Strange!!!

I just tried the following csv for products with pipe delimiter and it is working:

Code: Select all

"Name"|"Price"|"MSRP"|"Manufacturer"|"Sku"|"Description"|"Categories"
"Product1, papers, printers"|"24"|"23"|"ABC manufacturers ltd."|"SKU232"|"Description of the product1"|"My Category"
"NOW L-Carnitine, 500 mg, 180 caps"|"34"|"33"|"ABC manufacturers ltd."|"SKU2df3"|"Description of the product2"|"My Category"
"Product3, Flowers and Gifts"|"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"

There is one limitation, that when you are using custom templates, you must have to specify a text qualifier. And this is what your csv data seems to be missing.....

I also imported a 2 record comma delimited csv file for testing, and only the respective records are created...

What you mean by blank records???
Can you reproduce this problem consistantly?? If yes please share your csv data and let me try myself.

[Naveed]

If you are able to reprodce the 3000 blank records insertion problem, then please post the import log as well, the import log which is shown after a csv import.

[Mike718NY]

I just opened the original .csv file in Excel (with pipe delimiter)
and it seems to be corrupted. I was ok 2 days ago.
Maybe that was the problem. Let me try this again.

[Mike718NY]

I'm having problems with Backup and Restore and need to recreate the database.

Can I create it using those scripts in the Install folder:

AbleCommerce2000.sql
AbleCommerce.sql
AC7_Basic_Data.sql
AC7_Required_Data.sql
AC7_Sample_Data.sql
DefaultIndexes.sql

Would this create the database ok? thanks

[Mike718NY]

Forget that last request.
I finally got the #&$#@%$# thing to finally work.

[Mike718NY]

I got the import to work using both comma and pipe delimiters.

Question: Does the DataPort remove special characters like " ™ " ?
This character was replaced with a "box" symbol.
This character was present in the .csv file.
Other special characters were replaced also.
If this is going to happen I'll have to replace all those characters with spaces
in Sql Server and then do another import.
I figured these characters were going to be a problem.

[sohaib]

Question: Does the DataPort remove special characters like " ™ " ?
This character was replaced with a "box" symbol.

If it does so it shouldn't. It should be considered as a bug. I guess this is some character encoding issue. Naveed should be able to shed some more light on this.

[jmestep]

The blank product "could" have been if you deleted the data in the Excel rows, but not the rows themselves. (Don't ask me how I know.)

[Naveed]

The encoding issue of special chars with DataPort has been fixed long ago...
The latest version of DataPort .19 can export/import the special characters successfully.

For confirmation I just created a product with special characters and exported the data for it. Then I reimported and All is working fine. I tried both in CSV and XML formats.

Here is the CSV I got when I exported that product:

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","MSExcelWorkAround"
"54","1","Test product with special ™ chars #&$#@%$#","0","0","0","0","0","0","0","","","","","","Yes","1","AC7 main dev store","0","0","0","","Test product with special ™ chars #&$#@%$#","","Test product with special ™ chars #&$#@%$#","special chars in summary #&$#@%$# ™","#&$#@%$# ™","","","7/16/2008 4:11:06 PM","7/16/2008 4:14:15 PM","","False","False","False","False","","False","","False","0","0","0","","","Test product with special ™ chars #&$#@%$#","False","Test","False","0","0","","False","Do not delete"

Notice the special characters (special ™ chars #&$#@%$#) are there in CSV exported by DataPort.

The issue you are facing might be due to the text editor your are using, or Ms Excel is corrupting your data while editing and saving.

[Mike718NY]

I think my description field is a special case. I attached a pic of this example:

<TABLE cellSpacing=2 cellPadding=2 width="98%" align=center border=0>
<TBODY>
<TR>
<TD><B style="FONT-SIZE: 20px; COLOR: #6189bc">Water Out™ - 100 Vcaps®&nbsp; Code 1908</B><BR>
<TABLE cellPadding=2 width="100%" border=0>
<TBODY>
<TR>
<TD vAlign=top align=left><IMG title="" alt="" src="http://www.nowfoods.com/_cspimg/1908.jpg" border=0 longDesc=?action=itemdetail&item_id=>
<TABLE cellPadding=4>
<TBODY>
<TR>
<TD noWrap>
<P><U><FONT color=#34479e></FONT></U><BR>&nbsp;</P></TD></TR></TBODY></TABLE><BR>
<TABLE border=0>
<TBODY><FONT color=#34479e></FONT></TBODY></TABLE></TD>
<TD vAlign=top align=left width="100%"><BR>
<TABLE cellPadding=4 border=0><B class=productDetails>
<TBODY>
<TR>
<TD vAlign=top align=left>
<UL>
<LI><STRONG>Herbal Diuretic</STRONG>
<LI><STRONG>with Standardized Uva Ursi, Dandelion, Potassium, Vitamin B-6, and more!</STRONG>
<LI><STRONG>Naturally Supports Fluid Balance</STRONG>
<LI><STRONG>Healthy Urinary Tract</STRONG>
<LI><STRONG>Vegetarian Formula</STRONG></LI></UL>
<P><A href="http://www.nowfoods.com/index.php/cat/N ... at_id/1294" target=_blank><IMG title="" style="MARGIN: 1px 10px 1px 1px" height=51 alt="" src="http://www.nowfoods.com/view.php?id=130808" width=60 align=left border=0></A>NOW® Water Out™ is a synergistic herbal blend that supports urinary tract health.&nbsp; Uva Ursi, Dandelion, Goldenrod and Juniper are herbs that can help to relieve minor, temporary water retention while supporting healthy kidney function.&nbsp; These and other ingredients in Water Out™ are known to act in a gentle manner to maintain water equilibrium, as well as essential electrolytes.<BR></P></TD></TR></B></TBODY></TABLE></TD></TR></TBODY></TABLE></TD></TR>
<TR>
<TD align=left>
<TABLE class=mediumBlack cellSpacing=0 cellPadding=0 width=523 bgColor=#f4f4f4 border=0>
<TBODY>
<TR>
<TD vAlign=top align=left width=14><IMG height=20 src="http://www.nowfoods.com/now_images/gray ... top_cs.gif" width=14 border=0><BR></TD>
<TD class=black vAlign=top align=left width=684>
<CENTER><B style="FONT-SIZE: 20px; COLOR: #6189bc">Nutrition Info </B></CENTER></TD>
<TD vAlign=top align=right width=10 bgColor=#f4f4f4><IMG height=20 src="http://www.nowfoods.com/now_images/inte ... und_ur.gif" width=20></TD></TR>
<TR>
<TD vAlign=top align=middle width=14 bgColor=#f4f4f4>&nbsp;</TD>
<TD vAlign=top align=left width=684 bgColor=#f4f4f4>
<TABLE cellSpacing=0 cellPadding=0 width="100%" border=0><!-- Header of specifications -->
<TBODY>
<TR>
<TD style="BORDER-BOTTOM: black 2px solid" vAlign=bottom colSpan=3><SUB>Serving Size 2 Vcaps®</SUB> <BR><SUB>Servings Per Container 50</SUB> <BR></TD></TR><!-- Body of specifications -->
<TR>
<TH style="BORDER-BOTTOM: black 2px solid">&nbsp; </TH>
<TH style="BORDER-BOTTOM: black 2px solid"><SUB>Amount Per Serving</SUB> </TH>
<TH style="BORDER-BOTTOM: black 2px solid"><SUB>% Daily Value</SUB> </TH></TR>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>Vitamin B-6 (from Pyridoxine HCl)&nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>25 mg &nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>1250%&nbsp; </TD></TR>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>Potassium (from Potassium Chloride)&nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>99 mg &nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>3%&nbsp; </TD></TR>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>Uva Ursi Extract (<I>Arctostaphylos uva-ursi</I>)&nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>200 mg &nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>†&nbsp; </TD></TR>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>(Leaf) (Standardized to min. 20% Arbutin)&nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>&nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>&nbsp; </TD></TR>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>Dandelion (<I>Taraxacum officinale</I>) (Leaf)&nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>120 mg &nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>†&nbsp; </TD></TR>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>Goldenrod (<I>Solidago odora</I>) (Herb)&nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>120 mg &nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>†&nbsp; </TD></TR>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>Juniper (<I>Juniperus communis</I>) (Berries)&nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>100 mg &nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>†&nbsp; </TD></TR>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>Buchu (<I>Agathosma betulina</I>) (Leaf)&nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>100 mg &nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>†&nbsp; </TD></TR>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>Bladderwrack (<I>Fucus vesiculosus</I>) (Thallus)&nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>50 mg &nbsp; </TD>
<TD style="BORDER-BOTTOM: black 1px solid" bgColor=#ffffff>†&nbsp; </TD></TR><!-- Footer of Specifications -->
<TR>
<TD style="BORDER-BOTTOM: black 2px solid" colSpan=3><SUB>* Percent Daily Values are based on 2,000 calorie diet.<BR>† Daily Value not established.</SUB> <BR></TD></TR></TBODY></TABLE><BR></TD>
<TD vAlign=bottom align=right width=10 bgColor=#f4f4f4>&nbsp;</TD></TR>
<TR>
<TD vAlign=bottom align=left width=14 bgColor=#f4f4f4><BR><IMG height=14 src="http://www.nowfoods.com/now_images/gray ... tmleft.gif" width=14 border=0></TD>
<TD vAlign=top align=left width=684 bgColor=#f4f4f4 height=20><BR><BR></TD>
<TD vAlign=bottom align=right width=10 bgColor=#f4f4f4><IMG height=20 src="http://www.nowfoods.com/now_images/inte ... _right.gif" width=20></TD></TR></TBODY></TABLE><BR></TD></TR>
<TR>
<TD style="BORDER-BOTTOM: highlight thin solid" width="50%"><B>Details</B> </TD></TR>
<TR>
<TD height=43>
<DIV><STRONG>Suggested Usage:</STRONG>&nbsp; As a dietary supplement, take 2 Vcaps® twice daily with meals or a glass of water.&nbsp; Consider taking this product in combination with NOW® Magnesium & Potassium Aspartate, Cranberry and D-Mannose.</DIV>
<DIV>&nbsp;</DIV>
<DIV><STRONG>Other Ingredients:</STRONG>&nbsp; Cellulose (capsule), Rice Flour, Stearic Acid (vegetable source) and Magnesium Stearate (vegetable source).&nbsp; <STRONG>Vegetarian/Vegan Product.</STRONG></DIV>
<DIV>&nbsp;</DIV>
<DIV><STRONG>Contains no:</STRONG>&nbsp; sugar, salt, yeast, wheat, gluten, corn, soy, milk, egg, shellfish or preservatives.</DIV></TD></TR></TBODY></TABLE>

[Mike718NY]

The special characters appear now.
I fixed it by just swapping the description field from
the old products table to the new one:

update ac_Products
set Description = c.Description
from ac_Products a
inner join sv_products c on a.SKU = c.SKU

The blank product "could" have been if you deleted the data in the Excel rows, but not the rows themselves.

Yeah. I think I just hit Delete instead of r-clicking the row(s) and selecting Delete.
Just pressing Delete doesn't remove the rows. I hate Excel.

[jmestep]

Ditto on Excel. Access makes a whole lot more sense to me.

[mfreeze]

I am having a problem importing a pipe delimited CSV file with the following format. I am using pipes because of the commas in the category fields and a single quote for text delimiter because the category names contain double quotes (for inches).

'SKU' | 'Name' | 'Price' | 'Categories'
'A600-79'|'DC MOTOR CONTROL'|'329.11'|'Dispersers (Dispershear):D-6000 Dispershear:Parts'
'A600-75'|'1/3 DC MOTOR AUGER DRIVE'|'223.12'|'Dispersers (Dispershear):D-6000 Dispershear:Parts'
'W31FLX'|'EXTRA HEAVY FLAT WASHER'|'0.52'|'Dispersers (Dispershear):D-6000 Dispershear:Parts'
'W50LM'|'LOCKWASHER (4)'|'0.26'|'Dispersers (Dispershear):D-6000 Dispershear:Parts,Dispersers (Dispershear):D-6006 Dispershear:Parts,Dispersing Grinders:6 X 3 Dicon:Parts,Reversible Homogenizers:Model C-1-1 33"

After opening the file and switching to manual matching with the proper delimiters, I receive the following message, which I think should not be happening since the fields do match.

Following columns in your provided CSV file does not match while using the current template:
----------------------
Sku
Name
Price
Categories

After going through the mapping of the fields and starting the import, I get the message

'No products available for Import (Upload)'.

I can't see anything wrong with this file and need another set of eyes to look at it. I have attached the full file in case it is needed.