Excel Save As CSV - Numeric Precision 1

[lameid]

My personal experience and expectation of how Excel behaves is that Saving an Excel file to a CSV format saves the format of the data in the cells.

Thefore, formatting all the cells as general should show all precision of the data stored should allow the actual data to be saved.

Is anything flawed in my premise or are there any exceptions?

I believe the source is Excel 2007 but possibly 2010.

I am hoping to have a copy in hand shortly to verify the data...

My issue is one of precision.... numeric values seem to be rounded to 4 decimal places (possibly truncated I didn't look extremely closely).

 

[SkipVought]

hi,

formatting all the cells as general should show all precision of the data

[red]FORMATTING changes NOTHING![/red]
When you format a cell or range, NOTHING in the underlying values change. You are merely changing what is DISPLAYED in the cell.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Sorry, I missread your statement.

Naturally you are correct.

What value(s) in partivular are not exporting correctly.

EXAMPLES.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Thanks Skip that is what I thought.... Like I said I am waiting to get the source Excel file to verify...

A counterparty told me

when you pick general it makes some arbitrary decision on where to round to for display and what is written to CSV.

To which I responded to send me the Excel file as I don't believe it...

Since your story is the same (and your the Excel expert) I am confident that the data is bad...

 

[SkipVought]

[tt]
123.12345681234500000000,123.1234568
[/tt]
These two values were saved as .csv. I opened the .csv in NOTEPAD.

the number one the LEFT was formatted as "0.00000000000000000000" while the right as GENERAL.

GENERAL truncates.
Using the NUMBER places format perserves that many places, probably up to some limit.

Opening this .csv in Excel results in this DISPLAY
[tt]
123.1234568 123.1234568
[/tt]
In the Formula bar
[tt]
123.123456812345 123.1234568
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

I confirmed this on the MS Site...

My personal experience and expectation of how Excel behaves is that Saving an Excel file to a CSV format saves the format of the
data in the cells.

And through testing I am also seeing that the general format does not preserve the data for display.... In my example I saw 6 decimal places...

This is what happens when you store data in Excel, you can't get it out without code.

 

[xlbo]

This is why you should never use "general" format for anything except maybe text!!

One thing that should clue people in to the issues regarding it is that the number "changes" based on cell size - it will round numbers up the smaller the width of the cell - that just shows that it is a volatile format that should not be trusted for export purposes

I would advise against using excel for outputting to text files as it has a habit of trying to help you where you need no help....if you do really want to save as csv from excel I would suggest converting all to text before doing so as text is the only thing that excel generally doesn;t try to "help" with and as long as the character count is less than 1024, nothing untoward should happen to the data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lameid]

And I would recommend to keep your data in an RDMS system and not to create data to send to your client in Excel...

 

[zelgar]

I made a simple spreadsheet, 2 colums, column A values 1 to 10 and colum B 1/col A (e.g., 1/1, 1/2, ...). Without doing any number formatting, when I first saved this file as csv, the data was saved up to 9 decimal places, but only 6 were shown on the display.

Opening the file in Notepad and later reopening the file in Excel, the data was the same.

If you apply a number format, however, things are changed. I changed the number format to 20 decimal places and saved the file in cvs format. Opening the file in Notepad shows that the data does go up to 20 decimals. Once the file is opened in Excel, however, it automatically changes the data into a General Numeric format that drops the additional decimals after the 9th placement.

 

[lameid]

One thing that should clue people in to the issues regarding it is that the number "changes" based on cell size - it will round numbers up the smaller the width of the cell - that just shows that it is a volatile format that should not be trusted for export purposes

Just to clarify, General also truncates if the cell width is wide enough to show all the data. This seems to vary from system to system based on reports here and what I am seeing.... This makes me wonder what controls the precision in the display format (number of places shown after the decimal) for the general format.

 

[xlbo]

you should never use "general" format for anything except maybe text!!

all formats on a spreadsheet should be explicitly defined if you value the data that resides there

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lameid]

I have to take that a step further...

If you value your data or your trying to communicate data, you need to use a product with strong datatyping and export from it which is not Excel.

 

[xlbo]

Excel is great - don;t get me wrong...people just use it for the wrong things.

It should not be used as a data repository or a data transfer mechanism unless you have no other choice

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question