Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Swap . and , when importing to Excel 2007
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Swap . and , when importing to Excel 2007
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Swap . and , when importing to Excel 2007
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Swap . and , when importing to Excel 2007
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. -- Gary''s Student - gsnu200821 "Lasse" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Swap . and , when importing to Excel 2007
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. Thats 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 -- Petr Bezucha "Lasse" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Swap . and , when importing to Excel 2007
I created a CSV file with lines like this:
1.000.000,00;2.000.000,00;3.000.000,00;4.000.000,0 0 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 "Lasse" wrote in message ... | 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Crystal Reports into Excel 2007 possible? | Excel Worksheet Functions | |||
problems importing Excel 2007 table into SharePoint 2003 | Excel Discussion (Misc queries) | |||
Importing textfiles in Excel 2007 | Setting up and Configuration of Excel | |||
Importing Text files to Excel 2007 | Excel Discussion (Misc queries) | |||
Importing local OLAP cube in Excel 2007 | Excel Worksheet Functions |