How do you change default delimiter in the Text Import in Excel?

25
2014-04
  • Lars

    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?

  • Answers
  • RustyTheBoyRobot

    (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. Text Import Wizard - Step 1 Text Import Wizard - Step 2

    Or, if you're using the newer interface, go to the Data tab > From Text:

    Text Import 2007

    This will pull up a File Open dialog, followed by the same Text Import dialog.

  • datatoo

    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
    
  • fergulator

    This is a Windows setting that will be used by all programs that refer to it (Excel in this case).

    1. Click the Start button, and then click Control Panel.
    2. Open the Regional and Language Options dialog box.
    3. Do one of the following: In Windows Vista/7, click the Formats tab, and then click Customize this format. In Windows XP, click the Regional Options tab, and then click Customize.
    4. Type a new separator in the List separator box.
    5. Click OK twice.

  • Related Question

    How can you make Excel 2007 stop formatting large numbers as scientific notation?
  • John Rudy

    Kind of the inverse of this question.

    I frequently download CSVs from outside sources as part of my programming work. I like to examine their data in Excel because, let's face it, Excel is an ideal tool for this sort of thing.

    Except for one behavior: Excel attempts to outwit the data in the CSV, and therefore, if something looks like a number (like, say, many ISBN/EAN codes), Excel will treat it as such. And if this number is really big (like pretty much all ISBN/EAN codes), Excel will turn the number into scientific notation. Finally, if the number isn't particularly big, with most of these data formats, that indicates that Excel has decided that my number shouldn't have leading zeroes.

    So ... Is there a way to open a CSV (via double-click if possible, but I'm willing to compromise here) in such a way that I can have Excel 2007 treat all the columns as text?


  • Related Answers
  • BradC

    You can, but it takes a couple of manual steps. These steps are from Excel 2003, but I'd assume the wizard is similar in 2007:

    1. Rename the file as TXT. Don't double-click it.
    2. Open Excel
    3. Click File, Open
    4. Locate and double-click the text file
    5. It should open a "Text Import Wizard"
    6. In step one, choose "Delimited"
    7. In step two, uncheck "tab" and check "comma"
    8. In step three, scroll-over to the last column, shift-click to select all columns, then click "Text" as the format.
    9. Ignore the annoying "Number stored as text" warnings everywhere

    Goes pretty fast with some practice.