Number format problem in CSV files

[Mervyn Thomas]

I have some confusion with number formats in CSV files. Using a custom
number format 00000 I get numbers such as
00392 to display OK when on the screen but appears as 392 when the file is
repopened after saving.
The formats do appear OK in the saved file when opened with notepad.
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

 

[Nikos Yannacopoulos]

Mervyn,

CSV files are palin text files, and as such they cannot hold any formatting
information, such as number formatting.
If it is important to you that your numbers are stored with the leading 0's,
then they must be saved as text rather than numbers; apparently this is
indeed the case, since you can see it in notepad.
Then, when you open the file in Excel, though, Excel "decides" it's all
numeric data, since it finds no non-numeric characters in there, and
displays it as general numbers. To overcome this and display the leading
zeroes, you need to change the column data format for the particular
column(s) from general to text in step 3 of the Text Import Wizard.

HTH,
Nikos

 

[Peo Sjoblom]

Problem is that if you just open a csv file in excel it won't trigger the
text import wizard.
Unless you rename the file to *.txt it won't trigger the import wizard,
however if you instead do as follows
Data>Import External Data>Import Data, then under fileformat (files of type)
select
All files (*.*)
find the csv file and open it, then in step 3 select text under column data
format




Peo Sjoblom

 

[Mervyn]

thanks Guys - that explains it well