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

"Kevin N" <(E-Mail Removed)> wrote in message
news:8cc8b7b5-ce87-4188-ad9e-(E-Mail Removed)...
> 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

hi Kevin,

look at Step 3 of 3 on this link :

http://office.microsoft.com/en-us/ex...244.aspx?CTT=1


--
isabelle



Le 2012-01-09 16:37, Kevin N a écrit :
> 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

On Jan 9, 6:55*pm, isabelle <i...@v.org> wrote:
> hi Kevin,
>
> look at Step 3 of 3 on this link :
>
> http://office.microsoft.com/en-us/ex...t-wizard-HP010...
>
> --
> isabelle

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.

> Le 2012-01-09 16:37, Kevin N a écrit :
> > 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

Kevin N submitted this idea :
> On Jan 9, 6:55*pm, isabelle <i...@v.org> wrote:
>> hi Kevin,
>>
>> look at Step 3 of 3 on this link :
>>
>> http://office.microsoft.com/en-us/ex...t-wizard-HP010...
>>
>> --
>> isabelle
>
> 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>!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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 :
> 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.
>

On Jan 10, 11:14*am, isabelle <i...@v.org> wrote:
> 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.

On Jan 10, 10:41*am, GS <g...@somewhere.net> wrote:
> Kevin N submitted this idea :
>
>
>
>
>
>
>
>
>
> > On Jan 9, 6:55 pm, isabelle <i...@v.org> wrote:
> >> hi Kevin,
>
> >> look at Step 3 of 3 on this link :
>
> >>http://office.microsoft.com/en-us/ex...t-wizard-HP010....
>
> >> --
> >> isabelle
>
> > 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>!

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.

> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

--
isabelle


Le 2012-01-10 11:18, Kevin N a écrit :
> On Jan 10, 11:14 am, isabelle<i...@v.org> wrote:
>> 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.

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>

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc