[theaf]

Hi,
We upload most of our new products from CSV files. We use excel to create the files. We have a lot of trouble with two fields: the options and category fields. Excel changes the number into a formula after we save the file as a CSV file. This seems to happen regardless of whether we format the field to text, special or general. We have also used the ' in front of the first number which does not help and sometimes causes an upload error. It happens whether we are using numbers that are 1, 2, 3 or 4 digits. This has been an ongoing problem for us for which we have not been able to find a solution. When you have to end up adding the categories and options by hand it sort of defeats the benefit of importing products. My understanding is that this is a problem with excel. Does anyone know if the problem occurs when using Access? We are thinking about buying the program but want to make sure it will solve the problem before we spend the money. Also has anyone tried to use Open Office to create the uploads. Does it work any better than excel? Has anyone else encountered this problem? How did you solve it? Any suggestions would be greatly appreciated.
Thanks, Teddy

[rob1]

There is a quick and easy way to convert the all the formulas to the actual values in Excel.

1-Select the cells you want to convert
2-From the edit menu, select Copy (or use the shortcut)
NOTE: after you Copy, be sure to keep the same cells selected
3-From the edit menu, select Paste Special and then select Values

All the formulas will now be replaced with the values that were in the selected cells.

Rob

[theaf]

Hi,
Thanks for forumla suggestion. I was aware of that forumula because we use when we add text files together in excel to apply code. I tried it to see if it would solve the problem but it didn't. The problem is not really getting the correct categories back in a field, it is getting the categories to upload into Volusion. What is happening is that when save the excel worksheet as a CSV file it is changing the categories and then they do not load correctly.We are trying to figure out a way to fix the problem using excel or to determine before we buy MS Acess if that will solve the problem. I am sure there are other people uploading products with several categories and or options. What programs are you using for uploads? I have attached a zip with two files: One in an excel worksheet before we saved them in the CSV and one in the CSV file. Please note that we have the problem whether we format the cells as text, general, special or use the ' mark in front of the cell. I have also copied and pasted some examples (unfortunately I lost the column formating but you can get the idea below:

In an excel worksheet before saving as a CSV:
productcodecategoryids
GBDS 870114517,427,4607,409,410,412,516,4518,326,416,4524,32 7,410,411,425GBDS870124517,427,4607,409,410,412,516,4518,326,416,4524,32 7,410,411,425GBDS810352419,209,327,410,412,516,4518,4637,4640,4518,407,51 0,417,326GBDSML70054640,4519,409,410,412,516,4518,4637,419,4640,4519, 408,510,421,417,209,4640,4524,515,513,326GBDSML7068419,417,4640,510,421,408,4524,515,513,209,409,410, 412,516,4518,4637,326


In an excel worksheet after saving as a CSV:
productcodecategoryidsGBDS870114,517,427,460,740,940,000,000,000,000,000,000,000, 000,000,000,000GBDS870124,517,427,460,740,940,000,000,000,000,000,000,000, 000,000,000,000GBDS8103524,192,093,274,104,120,000,000,000,000,000,000,000, 000,000,000GBDSML700546,404,519,409,410,400,000,000,000,000,000,000,000 ,000,000,000,000,000,000,000,000,000,000,000GBDSML70684,194,174,640,510,420,000,000,000,000,000,000,000, 000,000,000,000,000,000

Here is what happens if you do a paste value:
categoryids4.51743E+484.51743E+484.19209E+454.64045E+704.19417E+54
You can clearly see the difference.
Any suggestions you might have will be appreciated.
Teddy

[Spesh]

We actually have the same problem, and to be honest we don't have a clue how to fix it either.

Most of the time I just delete the optionids and do a upload for just that at a later time.

[snapspeed]

Hi, your problem is because you are reopening the file with Excel after saving it as a csv and that's when the conversion back to a formula occurs. You need to manipulate your data in Excel and save it as a csv. THEN do your import to Volusion, DO NOT open it in Excel again before uploading it to Volusion. You can see that it is correctly formatted if you open the csv file with Notepad immediately after saving it as a csv from Excel. Right click on the csv file and choose "Open with Notrepad" you can also manipulate the data in Notepad if it's not too complicated.
As for Access if you follow the above you do not need it.
Just remember... do your Excel thing, save as csv and Import. It works for us...

[rob1]

I looked at your files, and snapspeed's method should work. Excel is saving to CSV correctly. The problem is when Excel opens the CSV file it is guessing what type of data format to use for each column. It thinks the category column should be formated at numbers, when it should be formated as text. Therefore, it converts that data from text to really long numbers (hence the E symbol).

[rob1]

Teddy,

If you need to re-open the CSV files with Excel to make changes, here is how to do so - while making sure that Excel keeps the categoryids column formatted as text:

1-Change the name of the file from a CSV to a TXT file extension.
2-In Excel, click Open and select the newly named file.
3-Excel will starts the Text Import Wizard.
4-Select the choice Delimited, then click on Next.
5-Select Comma as the delimiter, then click on Next.
6-On the lower part of the dialog box, click and select the entire categoryids column.
7-Click on the text radio button in the column data format area of the dialog box.
8-Click Finish.

Your file should open in Excel with the categoryids column as text data. You can then use Save As, to put it back into CSV format is needed.

Rob

[snapspeed]

Originally Posted by rob1

I looked at your files, and snapspeed's method should work.

change that should to will and we are in agreement :)

I know it works, I have been using it to manipulate thousands of lines of data for many years on ProStores and now on Volusion.
And to simplify the next post all you need to do is create a new blank workbook in Excel and import the csv for it to format correctly before you save it as a csv again. You do not need to go throught the whole process of renaming and the full run on the import wizard, just click finish... Excel knows it's a csv and when you import it it does not convert the info to formulas - and it knows the comma is the delimiter...
Are we havin' fun yet?

[rob1]

Originally Posted by snapspeed

change that should to will and we are in agreement :)

100% agree :)

Originally Posted by snapspeed

And to simplify the next post all you need to do is create a new blank workbook in Excel and import the csv for it to format correctly before you save it as a csv again.

Cool trick - thanks!

[theaf]

Hey Guys,
I just want to tell you a BIG thank you for helping me on this. Your tips are working like a dream. I had been ready to pull my hair out over this problem for over a year. I really appreciate your help!
Teddy