Text Output To CSV

[John Persico]

I have an Excel spreadsheet that has a column formated as text. The colomn
contains UPC codes, many of which have a leading zero. That's why I have
the colomn formated as text, so that I can see the leading zero.

Now....

When I export the file to a CSV, it turns that colomn into a number (ex.
....,0968574433298,...).

I want it to keep it as text (ex. ...,"0968574433298",...).

Is there any way for me to force Excel to do this when it creates a CSV? I
have to be able to do this because my store that I upload these products to
requires a string for that field, in order for the zero to take.

--

 

[ND Pard]

I tried it and it KEPT my leading zeroes.

I use Excel 2007, click on File | Save As | Other Formats and select the
"Save as Type:" of "CSV (Comma delimited) (*.csv).

Then I used my Windows Explore, found the csv file I saved, right-clicked on
it and choose "Open With" Notepad. The leading zeroes were in the CSV file.

Good Luck.

 

[John Persico]

I know that the leading zeros are in the CSV file. That's what I said
previously.
But, I wanted the field to be saved as a text string in the csv file, not a
number.

 

[Dave Peterson]

You mean a quoted string?

Maybe you can add the quotes yourself. Use a helper column to add the double
quotes, convert to values and delete the original column???

="""" & text(c2,"000000000") & """"

 

[John Persico]

Here's an example. I have a spreadsheet with two columns: UPC Code and
Product. The UPC Code column is formated as text. This is so that I can
see my leading zero, if there is one. When I spit it out as a CSV file it
looks like this:

Upc,Product
068888701648,Premier

If I put the UPC Code in quotes, then the file looks like this:

UPC Code,Product
"""098348574839""",Premier


Oddly enough when I use OpenOffice the CSV gets created the way I want:

"UPC Code","Name"
"095849434596","Premier"


I'm guessing that Excel simply won't produce the type of CSV file I want it
to produce.

 

[Dave Peterson]

Not without some help.

I'd use a helper column (a new column C) with:

=""""&a1&""""&b1
and drag down.

Then select column C.
Copy it
Paste into Notepad
Save that as the .CSV file.

Or you could use a macro to write the text file anyway you want.

Here are three sites that you could steal some code from:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html