Autoformatting woes

[Kevin N]

Hello,

I am trying to open a CSV file in Excel 2010, and it clobbers the
data. I am using Germany for my Windows Language/Region Settings. As
an example, a snippet of the original CSV data might look like:

"25";"";"64.79";"";"2.80"

but it will open as:

25 64.79 Feb 80

And when I attempt to format cells as "text," I get

25 64.79 29252

How it turns "2.80" into "29252" is a mystery to me. Is there anything
I can do about this, preferably a default setting for Excel?
Thanks

 

[Victor Delta]

Hello,

I am trying to open a CSV file in Excel 2010, and it clobbers the
data. I am using Germany for my Windows Language/Region Settings. As
an example, a snippet of the original CSV data might look like:

"25";"";"64.79";"";"2.80"

but it will open as:

25 64.79 Feb 80

And when I attempt to format cells as "text," I get

25 64.79 29252

How it turns "2.80" into "29252" is a mystery to me. Is there anything
I can do about this, preferably a default setting for Excel?
Thanks

I can answer the last para! 29252 is equivalent to 1 Feb 80 (i.e. the number
of days since January 1, 1900), so Excel is converting 2.80 into a date.

Not sure how to stop it doing this unless you can find a way of formatting
the appropriate cells as decimal numbers.

Good luck!

V

 

[isabelle]

hi Kevin,

look at Step 3 of 3 on this link :

http://office.microsoft.com/en-us/excel-help/text-import-wizard-HP010102244.aspx?CTT=1


--
isabelle



Le 2012-01-09 16:37, Kevin N a écrit :

 

[Kevin N]

hi Kevin,

look at Step 3 of 3 on this link :

http://office.microsoft.com/en-us/excel-help/text-import-wizard-HP010...

Thanks Isabelle,

Unfortunately this does not satisfactorily solve our problem. Our
clients typically view data on the screens of our products or click a
button to open a spreadsheet (Excel) containing the same data in a
CSV format. For our US and UK customers, fine. Now we have German
clients, and the problems start. First Excel will expect semicolons as
delimiters in "comma" separated value data. Small problem, we spend
about a week refactoring and testing our product to give clients a
delimiter choice. Next, Excel will treat numerical data such as 5.80,
3.2000 as dates. Big problems, as there are 1000s (or tens of 1000s)
of places in our code where data will have to be modified, which will
take months to implement and test, in addition to bloating our
codebase for some more or less recondite reason.

I find it almost inconceivable that there does not exist a way to
either preempt Excel from auto formatting data, or at least undo the
auto formatting.

 

[GS]

Kevin N submitted this idea :

Thanks Isabelle,

Unfortunately this does not satisfactorily solve our problem. Our
clients typically view data on the screens of our products or click a
button to open a spreadsheet (Excel) containing the same data in a
CSV format. For our US and UK customers, fine. Now we have German
clients, and the problems start. First Excel will expect semicolons as
delimiters in "comma" separated value data. Small problem, we spend
about a week refactoring and testing our product to give clients a
delimiter choice. Next, Excel will treat numerical data such as 5.80,
3.2000 as dates. Big problems, as there are 1000s (or tens of 1000s)
of places in our code where data will have to be modified, which will
take months to implement and test, in addition to bloating our
codebase for some more or less recondite reason.

I find it almost inconceivable that there does not exist a way to
either preempt Excel from auto formatting data, or at least undo the
auto formatting.

Is there any reason why the "button" your clients click to generate the
Excel version of your data can't format that data BEFORE dumping it
into a worksheet? Sounds to me like whomever programmed that button
only did half the job! They should have made sure the results are
identical to the stored data<IMO>!

 

[isabelle]

hi Kevin,

did you try when you convert the csv file in Excel file, to do specify for this column a type "text".
and once the conversion is complete, you change the text values for numeric values

--
isabelle



Le 2012-01-10 09:15, Kevin N a écrit :

 

[Kevin N]

hi Kevin,

did you try when you convert the csv file in Excel file, to do specify for this column a type "text".
and once the conversion is complete, you change the text values  for numeric values

Yes; see my original post: 2.80 has morphed into 29252.

 

[Kevin N]

Kevin N submitted this idea :













Is there any reason why the "button" your clients click to generate the
Excel version of your data can't format that data BEFORE dumping it
into a worksheet? Sounds to me like whomever programmed that button
only did half the job! They should have made sure the results are
identical to the stored data<IMO>!

We have legacy code that is about 15 years old and in that entire time
there never has once been the possibility that 5.80 might be
interpreted as anything other than a floating-point number in a
spreadsheet. Like I said before, to format the numbers before dumping
would involve altering code in thousands of places over hundreds of
modules. And considering that dots are used as decimal markers by many
people (USA, UK, and India, for example), it sounds just as much as if
whoever programmed Excel's CSV parser only did half the job if they
neglected the possibility that German users of Excel might
occasionally come across numbers formatted as such.

 

[isabelle]

no, you do not described the steps that you perform on the window to convert. this steps are important.

 

[GS]

It happens that Kevin N formulated :

We have legacy code that is about 15 years old and in that entire time
there never has once been the possibility that 5.80 might be
interpreted as anything other than a floating-point number in a
spreadsheet.

What you're talking about is the differences of international settings.
It would be presumptuous to assume MS would also include file
conversion for every possible scenario. Nevertheless, it also is
presumptuous on the part of MS to configure Excel to 'interpret'
numeric input however it feels. Clearly, the 'button' should have
pre-formatted the target cells for the intended data given that Excel's
behavior in this context is the same today as it was 15 years ago when
your program was written.

Like I said before, to format the numbers before dumping
would involve altering code in thousands of places over hundreds of
modules.

Why would you need to format the numbers? It only takes a single line
of code to format a target column. Also, it only takes a single line of
code to 'dump' an entire CSV file into a spreadsheet. As far as your
program is concerned, the CSV file content is text. Why has it taken 15
years for your programmers to learn they can't trust how Excel 'might'
interpret input and so they need to ensure it happens the way they want
it to be interpreted?

And considering that dots are used as decimal markers by many
people (USA, UK, and India, for example), it sounds just as much as if
whoever programmed Excel's CSV parser only did half the job if they
neglected the possibility that German users of Excel might
occasionally come across numbers formatted as such.

Perhaps.., since your program is (as you say) 15 years old that it's
time it was updated???<g>

 

[GS]

Kevin,
I'm not trying to be antagonistic here. I've learned that when
importing stored data from a delimited file that it's always best
practice to NOT ASS-U-ME Excel will AutoFormat that data correctly in
all cases!

 

[Kevin N]

no, you do not described the steps that you perform on the window to convert. this steps are important.

Hi Isabelle,

I'm not sure I'm following you. There is no import wizard available
once Excel has opened a document. You click a "download" button on the
web site; a dialog pops up asking whether to Open (with Excel) or Save
the document. You chose "Open" and your data is corrupted. Clients
would prefer not to have to jump through too many hoops just to
retrieve their data, as clicking "Save" and going through the Get
External Data operations. At worst they should simply select the
document (or specific columns) and choose to have them formatted as
Text.

 

[GS]

Kevin N brought next idea :

Hi Isabelle,

I'm not sure I'm following you. There is no import wizard available
once Excel has opened a document. You click a "download" button on the
web site; a dialog pops up asking whether to Open (with Excel) or Save
the document. You chose "Open" and your data is corrupted. Clients
would prefer not to have to jump through too many hoops just to
retrieve their data, as clicking "Save" and going through the Get
External Data operations. At worst they should simply select the
document (or specific columns) and choose to have them formatted as
Text.

And so we gleen a bit more useful info. You could have stated this was
a web download process. In this case I retract all my previous
comments!

 

[Kevin N]

Kevin,
I'm not trying to be antagonistic here. I've learned that when
importing stored data from a delimited file that it's always best
practice to NOT ASS-U-ME Excel will AutoFormat that data correctly in
all cases!

I'm sorry but suggesting that our coders only do half a job, or that
they should just be able to wave a magic wand or something and update
a system comprising more than a million lines of code, as if they had
nothing better to do, does come off as a bit antagonistic.

And I apologize if I do come across as such. I do not ask that Excel
will AutoFormat correctly, just that there is some way to turn it off
or undo it. That there seems to be no way to do so is endlessly
frustrating, especially as that seems to be a conscious design choice
rather than any sort of legacy handcuffs.

 

[Zaidy036]

I can answer the last para! 29252 is equivalent to 1 Feb 80 (i.e. the
number of days since January 1, 1900), so Excel is converting 2.80 into
a date.

Not sure how to stop it doing this unless you can find a way of
formatting the appropriate cells as decimal numbers.

Good luck!

V

Why not run the file thru a process to eliminate all of the "s in the
CSV BEFORE importing.

 

[GS]

Kevin N formulated on Tuesday :

I'm sorry but suggesting that our coders only do half a job, or that
they should just be able to wave a magic wand or something and update
a system comprising more than a million lines of code, as if they had
nothing better to do, does come off as a bit antagonistic.

And I apologize if I do come across as such. I do not ask that Excel
will AutoFormat correctly, just that there is some way to turn it off
or undo it. That there seems to be no way to do so is endlessly
frustrating, especially as that seems to be a conscious design choice
rather than any sort of legacy handcuffs.

My continued apologies! My point is mute as per my reply to your reply
to Isabelle. Otherwise, the only way around the problem that I've
learned is to ALWAYS explicitly set target cell format for imported
data, and NEVER rely on Excel to always automatically format imported
data as you expect. That way, n years down the road your software
doesn't break (for this reason, at least)!<g>

 

[GS]

Just for clarity...

I say my point is mute because of the revelation you made that users
download the file from a website via the 'Open' button of the download
dialog. In this respect your legacy program has nothing to do with what
Excel does with the CSV file. Since there's no way to embed formatting
in a CSV in any manner that Excel will recognize, there's no way for
your app to control how Excel interprets the data.

My comments were addressing the context that your app was automating
Excel and putting the data into a worksheet. This was derived from your
explanation as set out in your original post. Right or wrong, it would
have saved us both a lot of spent time had you mentioned the CSV was
being opened from a website.

Sorry if you feel I came off as being antagonistic, whatever the
measure. I assure you it was NOT intentional!

 

[Kevin N]

Just for clarity...

I say my point is mute because of the revelation you made that users
download the file from a website via the 'Open' button of the download
dialog. In this respect your legacy program has nothing to do with what
Excel does with the CSV file. Since there's no way to embed formatting
in a CSV in any manner that Excel will recognize, there's no way for
your app to control how Excel interprets the data.

My comments were addressing the context that your app was automating
Excel and putting the data into a worksheet. This was derived from your
explanation as set out in your original post. Right or wrong, it would
have saved us both a lot of spent time had you mentioned the CSV was
being opened from a website.

Sorry if you feel I came off as being antagonistic, whatever the
measure. I assure you it was NOT intentional!

No problem, and thanks for the help. There are quite a few
idiosyncrasies with Excels AutoFormatting; I just wish there were some
workaround for them.