Forum Discussion
Text Import Wizard issues
HI everyone,
I am having big issues when converting a text file into excel using the text import wizard. In the last step, under the "advanced" settings, I am asked whether I want to use a comma or a dot as both decimal and thousands separators. I proceed by setting dots as decimal separators and commas as thousand separators. However, when I import the text, I still get the opposite of what I chose. I thought it was related to my regional settings, but they are all set to US English. The only thing that is not set to US is my keyboard, which is in Italian QWERTY.
2 Replies
On the third step you shall select dot and comma mean in your regional settings, but how they are interpreted in the source, i.e. in the file you import.
For example, with importing "12.345,67" select on third step comma as thousand separator and dot as decimal separator. Above text will be converted to number 12,345.67
In Microsoft Windows, click Start, and then click Control Panel.
Open the Change Regional and Language Settings dialog box.
Enter a new separator in the List Separator field.
Click OK twice.
Does it work?
Import or export text (.txt or .csv) files
No?
then try this please.
(Link is in German)
Create your query.
The 0 before the decimal point is gone. Right click on the query => Edit.
Then click in the editor in the column header of the corresponding column => change type => text. Repeat for the next column.
The 0 is now available again. However, the cells are then formatted as text.
You can then either replace the point with a comma via Find & Replace or with data => text in columns => fixed width and then in the 3rd step of the assistant under "More" change the decimal separator to point.
Then your query will have decimal numbers separated by commas.However, both columns are then reset to text with each update.
You can get around this by inserting data as values into another sheet.Thank you for your patience and time.
Nikolino
I know I don't know anything (Socrates)
