Lost data converting .xls to .csv

[toastyboy]

I need to convert files from excel format to comma delimited text in order to upload them into my office system. The most important column to me is the UPC code and in this case they all start with a zero. But after I convert to .csv, that first zero is gone. It's also strange because right after I "save as" csv it looks fine on my screen. It's only after I exit and reopen the file that it looks messed up. I've tried formatting the columns as general, text, numbers, scientific, and every other choice, but it's the same thing every time. One thing I noticed is that when I open the file in .csv format, the width of the UPC column shrinks to the point that the 12 digit numbers won't fit so it converts them to exponential form. And it's when I widen the column that it changes back to a number. (minus the zero that's supposed to be there) Any help would be greatly appreciated.
Thanks

 

[bryanbayfield]

Are you talking about File > Save as, and then choosing ".csv" format?

I don't think the problem with the column size shrinking is anything that's associated with the problem, that's normal.

I'm assuming "UPC" is some kind of numerical format, in this case zero followed by a host of integers.

The only thing I can suggest is that you format the column as "Text" (I know you've already tried that) and then open the .csv file in Excel. You can choose the option to import this column in text format on the Import Wizard.

Failing that, it looks like you'll have to use VBA on this one.

Bryan. SuperBry!

 

[Hasit]

I don't know what a UPC code is, but have you tried one of the following:

Add a ' character e.g. '012345 to the front of the leading zero.

If that doesn't work, then put the UPC code in double quotes e.g. "012345" or "012345. Then you can replace these quotes once they have been imported into your office system (if thats possible).

There is a third way, but I'm damned if I can remember it.

 

[Dreamboat]

UPC = universal product code. It's a little sticker or imprint on products sold in the US (and elsewhere?) that are scanned at the retail location checkout line. I.e., the UPC code for John Walkenbach's Excel 2000 bible is 780764534492, LOL.

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word