Maintaining Number Formats from Excel to CSV File Conversions

[laura1027]

Scenario:

Converting an Excel File to a CSV File. Saving on a disk and than downloading
it to a DOS program.


Problem:

When I convert the excel file to the CSV File and save, it does not save the
same number format.

Example:

In Excel, the number format is 6.90 or 33.00.
After converting to a CSV file for upload to a DOS program, the 6.90 changes
to 6.9 and the 33.00 changes to 33 - the ending zeros disappear.

Question:

How can I maintain the numbers with the ending zeros after the decimal point?

Any Solutions?

Thank-You

 

[Nick Hodge]

Laura

Try formatting the data to text before saving

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

 

[Dave Peterson]

Are you sure?

If you re-open that CSV file in excel, then formatting will be gone.

But what do you see if you open the CSV file in NotePad?

It's not the saving as CSV that's the problem, it's the reopening within excel
that's the problem.

 

[laura1027 via OfficeKB.com]

Nick, text formatting the colums do not work. The ending zeros still
disappear when converting the excel file to CSV format.


Dave, saving the file to notepad works in so far as the ending zeros do not
disappear and remain when converting to notepad. However, I have to find out
if the notepad format is the correct format for downloading into the DOS
program. I do not do this part, my current employer, who created the order
entry DOS program that he uses for his business, does the downloading. I
will see if it works tomorrow.

Thank-You both for your help.

Laura

 

[David Biddulph]

Laura,

I would check again your answer to Nick, if I were you. I'm pretty sure
that you'll find it's the same situation that Dave described. If you look
at the CSV file with Notepad, or any other text editor, I expect you'll see
the data in the format you had originally. The only problem is if you use
Excel to read the CSV. If you rename the .csv file to .txt, then you can
allow Excel to treat each column as text, and not reformat it. Hopefully
your DOS program won't reformat the data when it reads it from the CSV.

Your reply to Dave suggests that you've used Notepad to save the file again.
You don't need to do that, as the file content is already OK. If you've
seen any effect, it is probably that you've saved as .txt rather than .csv,
and thus affected how Excel treats the data when it reads it back in, but
the content of the file should be the same.

 

[laura1027 via OfficeKB.com]

Hi David B.

Maybe I do not understand it that well. I will look at it tomorrow again.

Thanks
Laura

 

[Earl Kiosterud]

Laura,

I can't see your original post (it just ain't there -- just your reply to Nick's reply), and
you haven't quoted the replies, but I'll add this. Saving as CSV writes a text file exactly
as it appears (as formatted) on the sheet. If you open that saved text file with Notepad,
you'll see exactly what's in the file, character for character. There is no formatting
information in a text file -- just characters, and Notepad shows you what they are. Thats's
all. If you reopen the text file in Excel, formatting gets applied that isn't always
desired (leading zeroes dropped, stuff that looks like a date but ain't a date gets
converted to a date, etc.). In short, it's probably getting saved as is, but if reopened in
Excel, it's getting changed.

If saving it as a CSV truly isn't doing what you need, try the Text Write Program (and Excel
Workbook with a macro) at www.smokeylake.com/excel.
--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...