Swap . and , when importing to Excel 2007

[Lasse]

Hi

I don't quit know how to explain this but I will give it a try

One of our users want to import a .CSV file to Excel 2007. During the import
wizard it's possible to change how Excel handles "." and "," (Without the
quotes) in numbers when importing. It should be possible to swap them around,
it worked in Excel 2003 but not in 2007.

Any idea?

/Lasse

 

[Gary''s Student]

Define the field as Text and you should have no problems

 

[Lasse]

Hi

Thanks for the reply.

I have already defined the field as Text but that doesn't work.

We live in Denmark so our Regional Settings in Windows XP is of course set
to Danish. We use "," and "." different from GB/US. So if you define a number
(A million) in Denmark it's: "1.000.000,00" where as in GB/US it's:
"1,000,000.00"

If we change our Regional Settings to English, import the .CSV file, save
the file and then change Regional Settings back to Danish everything is
working perfectly!

It seems like the function to swap "," and "." in Excel 2007 doesn't work
properly when our Regional Settings are set to Danish.

Hope this makes sense.

/Lasse

 

[Gary''s Student]

Your explanation is clear.

..CSV files are tricky. If you open them with a double-click or:
File > Open > .....
there is a possibility Excel will mis-interpret the values.

The safest thing is to:

Data > Import External Data > Import Data and then tell the text import
wizard what the fields really mean.

 

[PBezucha]

Lasse,

I had suspected this was the heart of the matter. The .csv is for (our)
Middleeuropian a source of nuisance.

The following insertion into your macro would probably help. After normal
opening the csv.file and possible copying the whole range so that the values
are all in the column ‘A’, it splits each one-word value from a row and
spreads the fragments into L adjacent columns, according to the disposition
of dividers. You must first check by opening the file from Wordpad that the
divider, which the system uses, is really semicolon. That’s why the value of
const Divider was set like follows.


Dim J As Long, I As Long, L As Long, M As Range, S As Variant, FirstRow as
Long, LastRow As Long ‘these rows must be naturally found out
Const Divider As String = ";"

For J = FirstRow To LastRow
Set M = Cells(J, 1)
S = Split(M.Value, Divider)
L = UBound(S)
For I = L To 0 Step -1
M.Offset(0, I).Value = S(I)
Next I
Next J

Regards

 

[Jim Rech]

I created a CSV file with lines like this:

1.000.000,00;2.000.000,00;3.000.000,00;4.000.000,00

Then I changed my Regional Settings to Danish. Then I opened the file in
Excel 2007. Being a CSV the file was directly opened by Excel (i.e., no
Text Import Wizard). The data came in perfectly. Excel 2007 SP1.

--
Jim
| Hi
|
| Thanks for the reply.
|
| I have already defined the field as Text but that doesn't work.
|
| We live in Denmark so our Regional Settings in Windows XP is of course set
| to Danish. We use "," and "." different from GB/US. So if you define a
number
| (A million) in Denmark it's: "1.000.000,00" where as in GB/US it's:
| "1,000,000.00"
|
| If we change our Regional Settings to English, import the .CSV file, save
| the file and then change Regional Settings back to Danish everything is
| working perfectly!
|
| It seems like the function to swap "," and "." in Excel 2007 doesn't work
| properly when our Regional Settings are set to Danish.
|
| Hope this makes sense.
|
| /Lasse
|
| "Gary''s Student" wrote:
|
| > Define the field as Text and you should have no problems
| > --
| > Gary''s Student - gsnu200820
| >
| >
| > "Lasse" wrote:
| >
| > > Hi
| > >
| > > I don't quit know how to explain this but I will give it a try
| > >
| > > One of our users want to import a .CSV file to Excel 2007. During the
import
| > > wizard it's possible to change how Excel handles "." and "," (Without
the
| > > quotes) in numbers when importing. It should be possible to swap them
around,
| > > it worked in Excel 2003 but not in 2007.
| > >
| > > Any idea?
| > >
| > > /Lasse