The Text Import Wizard has been part of Excel for decades. For heavy use, Power Query has become a more powerful tool for importing and managing large amounts of data. But the wizard remains a useful tool in its own right.
Automatic Data Conversions
When you type or paste data into Excel, Excel will “helpfully” convert the entry into standard Excel types. Excel will convert whatever it can into a number, which means it will strip off any leading zeros and truncate it to 15 significant figures. If the text contains numerical digits surrounding the letter E, Excel will convert it to a number in scientific notation. If Excel thinks the text looks anything like a date, BAM! Excel turns it into a date. Sometimes these conversions are more like Automatic Data Perversions.
This problem has become critical especially for biochemistry data sets, for example, because researchers give genes names like MARCH1 (short for Membrane Associated Ring-CH-Type Finger 1) and SEPT1, but Excel converts them into the dates that they resemble, March 1st and September 1st. An estimated 20% of research papers in the field have been found to have such errors in gene names. The problem has gotten so bad that Scientists rename human genes to stop Microsoft Excel from misreading them as dates rather than learn how to deal with data in Excel.
Excel has recently added the ability for users to selectively disable these automatic conversions. Go to File > Options > Data, and use the checkboxes at the bottom of the dialog.
This is apparently a very recent feature, described in Control data conversions in Excel. Excel is changing so fast, it’s difficult to keep up with all of the innovations.
Text Import Wizard
If you use File > Open to open a regular text file (which may be comma-separated but has a file extension of .txt), Excel automatically opens the Text Import Wizard so you can import the data the way you want, bypassing Excel’s automatic conversions. If you open a regular text file by dragging it onto the Excel window, Excel just opens it as a new worksheet with the comma-separated in the first column. You can then use Text to Columns to split the data into columns, also bypassing any automatic data conversions.
CSV (Comma-Separated Values) files are a native file type. This is convenient, but also dangerous, because when Excel opens a CSV file, it can perform numerous unexpected conversions on the imported values. If we trust the new Data Conversion options (described above), Excel will warn us about them. But I think it’s best to explicitly control your data.
The usual suggestion by experts like me is to use Power Query to import your CSV files. Power Query allows you to import files singly or in batches and control the conversion and formatting of the data in each field.
If you’re only importing CSV files occasionally, one at a time, Power Query seems like a bit of overkill. Wouldn’t it be nice to simply use the Text Import Wizard on a CSV file? Well, you can, but you first have to activate it.
Go to File > Options > Data, the same dialog that has the Automatic Data Conversions options. Under Show Legacy Data Import Wizards, check From Text (Legacy). This will enable the Text Import Wizard for any new text files you open, including CSV files.
Using the (Legacy) Text Import Wizard
When importing a CSV file, the Text Import Wizard will not activate if you drag the file onto the Excel window or if you use File > Open. You have to follow the sequence Data > Get Data > Legacy Wizards > From Text (Legacy).
Select your file
This opens the Import Text File Dialog, which is the File Open dialog with a new caption and the text file filter activated. In this example I’m using a CSV file containing Microsoft stock price information.
Select your file and click Open. This starts the Text Import Wizard. If you use the Text to Columns Wizard, the Text Import Wizard will be very familiar. The term Legacy is a bit misleading, though. If you used the Text Import Wizard in pre-Power Query versions of Excel, the Legacy Text Import Wizard will look familiar, but new features have been added.
Step Through the Wizard
In step 1 of the wizard, you can decide whether the file is delimited (Excel guesses that it is), you can indicate which row to start importing from, and you can specify whether the first row of the file contains headers (the headers checkbox is new). There is a preview of the file to help you choose these options.
Click Next to go to step 2 of the wizard. Here you can select which character(s) to use as a delimiter. The preview shows the data split into columns based on your selections.
Click Next to proceed to step 3. Here you can define the import of each column. In this case, I left them all as General, which means Excel will do what it wants to do. But you can specify whether to import columns as text (so no converting genes into dates or truncating credit card numbers), whether to change the date conversion (in case the file came from a region that uses different day-month-year order), or to skip a column altogether.
Click Finish to exit the wizard.
Drop Your Data
You’re not done yet. There is one more dialog, which looks like the Close and Load dialog of Power Query. The default settings are to drop the data into a Table at the active cell and add it to the Data Model.
With these selections, the data is dropped into a Table, just as if it were imported using regular Power Query. Note the name of the Table: Excel uses the prefix “Table_” plus the imported file name without the file extension. The file MSFT.csv becomes Table_CSV.
You can choose not to add the data to the Data Model. This disables the options for a Table, Pivot Table, Pivot Chart, or Connection Only.
In this case the data is dropped into the worksheet, but not as a Table. Note that Excel names the range using the imported file name minus the file extension: MSFT.csv becomes the range named MSFT. Import the file again into the same workbook, and it will be named MSFT_1, etc.
The Text Import Wizard is a quick and useful way to import CSV files cleanly into Excel, avoiding any issues with data corruption.
Leave a Reply