Other Excel tutorials:
How to create PivotTables in Excel How to budget your money in Excel How to create basic formulae in Excel How to freeze and unfreeze rows and columns in Excel
How to import CSV files to Excel
Launch Excel then go to the File tab at the top of the screen, click it and then click Open from the menu that appears in the panel below.
Navigate to the folder that your CSV file is in and you’ll notice Excel isn’t able to find it. That’s because the Open menu automatically defaults to Excel file types (.xl, .xls, .ods, and a number of others) so we’ll need to change the setting. Just above the Open button at the bottom of the dialog box that’s on the screen you’ll see another box entitled All Excel Files. Click on this and then select Text Files from the long list that appears.
Now the CSV file should available. Double click on it and you’ll see the Text Import Wizard appear. The wizard should have determined that your data is Delimited. This means that fields are separated by commas. As CSV is an acronym for Comma Separated Value, this is correct. Double check that the Delimited option is chosen, then click Next.
The next page of the wizard concerns itself with how the data in the CSV file is separated, and therefore how information is displayed on the worksheet. The Delimiters section allows you to specify which character should be used as the sign for Excel to break up the text. You can toggle each one on and off and check the preview pane to see the effect this has on the data.
One other important option in this section is the Text Qualifier box. In here you’ll see the current character that indicates to Excel where a text entry begins and ends. For example if the Text qualifier is (“) then data reading “London, England” will be placed in a single cell. If any other character has been selected as the Text Qualifier, say (‘) then the same entry will be placed in two cells due to the comma between them which now acts as a separator. This can all seem a little complicated, but so long as the data in the CSV file is consistent you shouldn’t have any need to adjust this setting, or if you do it should only be a matter of selecting the correct Text Qualifier.
The last screen of the wizard asks you to set the format of data for each of the columns in the CSV file you’re importing. This will allow Excel to know exactly how to treat the figures and text in the file. This isn’t a universal setting as data in one column might be text while another could contain only numbers. To select the correct settings highlight each column by clicking on it in the Preview pane, then choose the data format from the four circular buttons above. Numeric fields should be General, while the others explain themselves.
The Date option also has a dropdown menu so you can select the relevant format – DMY, MDY, and so on. Clicking the advanced button will give you further control over how the numeric fields are displayed, should you require it. When you’re happy with the data format click Finish and your CSV file will be opened in an Excel worksheet. Cleverly it will remain in a CSV file format so you can save it and carry on using it with your text editor afterwards.
How to export CSV files in Excel
If you have another file currently in an Excel format but want to convert it to CSV, the steps are a bit simpler. Open the file then select the File tab at the top of the worksheet and double click on Save As. In the windows that appears you’ll see the current format displayed in the box beneath the file name. It should be Excel Workbook (xlsx). Click this field to open the dropdown menu where you’ll find an option for CSV (Comma delimited) (.csv). Select this and click Save.
If your workbook has multiple sheets you’ll see a warning box appear telling you that it can only save the active sheet (the one on top when you open the file) in CSV format. There’s no easy way around this except for the advice Microsoft gives, which is to save each sheet individually with a different name. If you have a multitude of sheets then maybe a CSV file isn’t the best choice as it could take a while to convert them. On the other hand if the other sheets are empty (Excel does often create multiple ones in new documents) then right click each tab and select Delete until you’re left with the one you want. Repeat the Save process and you should now have a shiny new CSV file at your disposal. Martyn has been involved with tech ever since the arrival of his ZX Spectrum back in the early 80s. He covers iOS, Android, Windows and macOS, writing tutorials, buying guides and reviews for Macworld and its sister site Tech Advisor.
