An Easier Way to Open CSV Files in Excelby
You’ve likely encountered many comma-separated value (CSV) files over the years. These can often be the bane of an Excel users’ existence, as Excel tries to be “helpful” in such a way that often becomes “anti-help”. In this article I’ll show you an alternative to the traditional File, Open command that will enable you to assert far more control over handling CSV files in Excel.
You should find this approach eliminates most, if not all, of your frustrations related to viewing and editing such files in Microsoft Excel. A typical comma-separated value file is shown in Figure 1.
If we use the Notepad app in Windows to view the file, we can see that each field is separated by a comma. This serves as a universal format that many software programs rely on for importing and/or exporting data.
Figure 1: A comma delimited file shown in Notepad.
Typically, users try to open such files by way of Excel’s File menu:
- Excel 2016: Choose File, Open, and then Browse.
- Excel 2013: Choose File, Open, and then double-click Computer (alternatively click once on computer, and then click Browse).
- Excel 2010: Choose File, and then Open.
- Excel 2007: Click the round Office button at the top left-corner of Excel and then choose Open.
Once you’ve made your way into Excel’s Open dialog box, as shown in Figure 2, the next step is to change the File type to Text Files. This dialog box defaults to All Excel Files, which means you won’t see any CSV files until you change the File type. You can then double-click on the file of your choice to open it.
As shown in Figure 2, Excel generally removes any leading zeros from numbers within the text file. This alone is enough to drive most users mad, who then to go to great lengths to restore the zeros back by way of simple retyping or by using formulas such as Excel’s TEXT worksheet function. In general, it’s typically best to avoid using Excel’s File menu to open text-based files. The alternative resides on Excel’s Data menu.
Figure 2: Many unexpected consequences arise when CSV files are opened by way of Excel’s File menu.
As shown in Figure 3, the From Text command will enable you to have far more control over your text files:
- Choose Data.
- Choose From Text.
- Double-click on your file from the Import Text File window that appears. Notice that it automatically shows your CSV files, so there’s no need to change the File type.
- At this point the Text Import Wizard appears. This wizard assumes that your file is delimited, meaning that there is a separator between each field, so click Next on the first screen.
- Excel assumes that all text files are tab-delimited, so on this screen click Comma. The Data Preview window will now show your data in columns, as it will appear within the worksheet. You can generally leave the Tab delimited turned on, I’ve only experienced one instance in my career where it mattered if both checkboxes were selected at the same time.
- Click Next to advance to the next screen of the wizard.
- This screen allows you to assert control over how your data gets treated. Click on any column that contains numbers with leading zeros.
- Choose Text to indicate that Excel should treat this column as text. This will prevent Excel from “helpfully” removing your leading zeros. Repeat as needed for any additional columns.
- Click Finish to close the Wizard.
Figure 3: The From Text command launches a Text Import Wizard.
- As shown in Figure 4, at this point an Import Data prompt will appear, within which you’ll click Properties.
- Within the External Data Range Properties dialog box, you can automate several aspects related to your text file. For instance, the Prompt for File Name or Refresh option allows you to choose whether or not you want to choose a different CSV file every time you open the Excel workbook. The wizard settings you chose will apply to each CSV file. Uncheck this checkbox if you save over the same CSV file every time you export from your other software program, or leave the box checked if you wish to have the option to select a different file each time.
- Check the Refresh Data When Opening the File checkbox so that whenever you open this workbook Excel will automatically pull in your CSV file.
- If you tend to add helper formulas to the CSV files that you open, choose Fill Down Formulas in Columns Adjacent to Data. Excel will then copy or remove formulas you add to the right of the data from the CSV file as the number of rows increases or decreases.
- Click OK to close the External Data Range Properties dialog box.
- Click OK to close the Import Data dialog box.
Figure 4: The External Data Range Properties dialog box allows you to automate, and thus simplify, accessing CSV files.
At this point save your Excel workbook in the usual fashion. Going forward you’ll find launching CSV files to be far easier. You’re still free to save this data back to the CSV format if needed, as illustrated in Figure 5:
- Choose File.
- Choose Save As. In Excel 2013 and later you may need to click the Browse button or double-click on Computer.
- Within the Save As dialog box change the File Type to CSV (comma delimited) (*.csv). The easiest way to make this selection is to type the letter C once within that field.
- Assign the file name of your choice.
- Click Save.
- Click Yes if a prompt warns you about potential loss of data. Excel gets twitchy when we attempt to save files in non-workbook formats.
Figure 5: The Save As dialog box allows you to save the data back to the CSV format.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.