CSV and Custom Fields

[John Persico]

I have a spreadsheet with a custom field: 000000000000.
12 numbers = a UPC code.

However, when I save this as a .CSV file, then field gets converted to
scientific notation.
How can I setup my excel file so that 1) I have 12 characters (numbers) for
a UPC code, and 2) when I save a .csv file the file contains 12 characters
(without scientific notation)?

 

[Pete_UK]

You need to treat it as a text field. In a spare column you could
have:

=TEXT(A1,"000000000000")

and then fix the values in that column and copy them to over-write the
originals. You can ensure that they do not get converted to numbers
when the CSV file is subsequently opened in Excel by including a text
character at the beginning or end, eg "x" or "_" or "~". It would be
easy to extract the number part using LEFT or RIGHT.

Hope this helps.

Pete

 

[John Persico]

I was able to get the column to be text... using the method you mentioned.

Here's the issue.
I upload a .csv file to an online store.
When I save a .csv file and upload it, my UPCs show up as scientific
notation.
Maybe I'm missing something here.
How can I do an upload?
It has to be a character string, right?

You need to treat it as a text field. In a spare column you could
have:

=TEXT(A1,"000000000000")

and then fix the values in that column and copy them to over-write the
originals. You can ensure that they do not get converted to numbers
when the CSV file is subsequently opened in Excel by including a text
character at the beginning or end, eg "x" or "_" or "~". It would be
easy to extract the number part using LEFT or RIGHT.

Hope this helps.

Pete

 

[Pete_UK]

John,

I have the same problem with CSV files sent to me relating to
telephone data. Leading zeros (sometimes double zeros) are important
in phone numbers, but often if you just open the CSV file in Excel
those numbers will be converted to scientific notation. One way around
it is to rename the .CSV file to .TXT, then when you open this Excel
will automatically enter the Data Import Wizard and with this you can
specify exactly how you want each field of data to be treated, i.e. as
Text in this case. I like those suppliers who put "_" or "~" in front
of the number, so tht the digits are not converted when I open the CSV
file directly.

I don't know enough of your application to say if this approach would
work for you, but if you appended a letter x at the end of your 12-
digit number, then that would be interpreted as a text value, and as I
said before it is very easy to get rid of it after importing.

Hope this helps.

Pete

 

[John Persico]

I have to upload a .csv file to a store website.
The UPC code is the one I'm having a problem with.
If I put something before or after the number, then the upload to the store
will include it.

Even when I force the field to be text, the export to a .csv doesn't work
like the way I want it to.

Is there a way to force the .csv file to make that field a string instead of
a number?

I'm not sure if there's a way to do what I want to do.
I use ProStores.
There's many fields, one being UPC Code. Many UPC codes start with a zero.
That's what giving me the problem here.
Products can be listed at Google Base, which I've done, but...
Google base says I have errors with my UPC codes, aptly so.

Any ideas?

I'm not sure how everyone else gets UPC codes to ProStores, to be quite
honest.
Maybe I'll call support and see what they suggest.

John,

I have the same problem with CSV files sent to me relating to
telephone data. Leading zeros (sometimes double zeros) are important
in phone numbers, but often if you just open the CSV file in Excel
those numbers will be converted to scientific notation. One way around
it is to rename the .CSV file to .TXT, then when you open this Excel
will automatically enter the Data Import Wizard and with this you can
specify exactly how you want each field of data to be treated, i.e. as
Text in this case. I like those suppliers who put "_" or "~" in front
of the number, so tht the digits are not converted when I open the CSV
file directly.

I don't know enough of your application to say if this approach would
work for you, but if you appended a letter x at the end of your 12-
digit number, then that would be interpreted as a text value, and as I
said before it is very easy to get rid of it after importing.

Hope this helps.

Pete

 

[Dave Peterson]

Did you check the data in the CSV file by re-opening it in excel or by opening
the CSV file in a text editor (like NotePad)?

 

[John Persico]

Good point.
When I open the file in Excel it shows scientific notation.
But, when I open it in a text editor it doesn't.
My upload works.
My UPCs are showing up as 12 digits, and with zeros in front of them where
appropriate.


Thanks for everyone's help!