Number formats in CSV files

[Mervyn Thomas]

I have a little confusion with number formats in CSV files created
/processed in VB. Using a custom number format 00000 I get numbers such as
00392 to display OK when in an xls file and when saved as a CSV file these
numbers display OK immediatley before saving then using a text editor on the
file they appear to be OK in the saved file. However when the CSV file is
reopened in excel the number format is lost and appears as 392 rather than
00392. This also happens when the number is input as '00392 rather than
just using the custom format..

Can you explain what is happening here and what do you think external
systems will do with these numbers? Is there any way to maintain the 00000
formats?

Mervyn

 

[arno]

Hi Thomas,

I do not know what your "external Systems" are, however, usually when
importing data you can define the datatype of every field (this would be
alphanumeric or text in your case). When you import what is 00123 in your
csv-file (viewed with notepad) as just 123 then Excel "guesses" that it is a
number, like when you type into a cell 000123 which will also give you just
123.

Now, one solution would be instead of "opening" the csv-file, make a
"text-import", and there you can define the field as text (Menu Data/import
external Data/Import Data, first set the file type to "csv, txt..." and
select your file, then you can specify the separator as ";" and then you
have options for each field).

Best regards

arno

 

[Rohan Khan]

Have you tried putting dittos (") around each field..
eg. "00392"