38
6
I want to import a CSV file to Excel. The CSV file has comma separated data (go figure), but the delimiter in the Text Import Wizard is set to "Tab" by default. How can I change the default to "Comma" instead?
38
6
I want to import a CSV file to Excel. The CSV file has comma separated data (go figure), but the delimiter in the Text Import Wizard is set to "Tab" by default. How can I change the default to "Comma" instead?
12
This is a Windows setting that will be used by all programs that refer to it (Excel in this case).
Excel ignores this locale setting for common data file extensions like .dat
and .txt
– patricktokeeffe – 2015-08-21T20:12:07.920
1An additional solution is to start your csv files with sep=,\n This will allow users of all Language Options to read the file but dots will not automatically be used as the fixed point digit seperator so numbers might be very wrong! – Oliver Zendel – 2017-03-19T13:43:46.670
6This doesn't works... – Stormenet – 2011-09-16T08:33:04.710
3It works when you use the normal file->open procedure – pypmannetjies – 2012-10-30T14:19:23.047
23
(Assuming the Office 2003 interface)
Don't use File > Open.
Instead use Data > Import External Data > Import Data...
This will invoke the Text Import Wizard, which lets you choose the delimiter.
Or, if you're using the newer interface, go to the Data tab > From Text:
This will pull up a File Open dialog, followed by the same Text Import dialog.
Works for Excel 2017 on the Mac as well ;-) – Giel Berkers – 2017-10-13T12:34:20.313
10Yes, I am aware of the Text Import Wizard, I should have mentioned it in the question, not just the title. What I want to do is change the default from "Tab" to "Comma" in that wizard. Its a pain to have to change it when I never have tab delimited imports. – Lars – 2011-06-01T11:13:20.977
@Lars: datatoo's pointer to Dave Peterson's solution works perfectly well for me. – PonyEars – 2014-01-09T01:56:57.590
6
Excel appears to use the last used delimiter in the session. Dave Peterson describes a possible solution here You essentially create a macro that sets the default delimiter and place it in the xlstart folder. If you are doing this for a file on other peoples machines, that is not probably going to work, but this is how you would approach it, programatically on your own system, and you may adapt it to your situation. Running the following code should set the delimiter for you beforehand. This will operate on an existing session. So if you have content that expects specific delimiters setup, run something like this prior to your import, setting whatever parameters you like
With ThisWorkbook.Worksheets(1).Range("a1")
.TextToColumns Destination:=.Columns(1), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False
End With
ThisWorkbook.Close SaveChanges:=False
It does not use the last one on my system I have to change it every time -- even if I just changed it in the same workbook. – Hogan – 2017-08-11T19:18:57.110
1fergulator has an answer that works well – pypmannetjies – 2012-10-30T14:19:59.660