Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number formats in CSV files
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number formats in CSV files
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 "Mervyn Thomas" schrieb im Newsbeitrag ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number formats in CSV files
Have you tried putting dittos (") around each field..
eg. "00392" -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defualt number formats When Opening CSV Files | Excel Discussion (Misc queries) | |||
Saving files in two different formats at the same time | Excel Discussion (Misc queries) | |||
Converting file formats into .xls files | Excel Discussion (Misc queries) | |||
importing csv files, problem with date formats | Excel Worksheet Functions | |||
Maintaining formats of files | Excel Discussion (Misc queries) |