Excel is able to handle a range of file types, including
text documents such as TXT and CSV. In this feature we’ll take a look at how
you can import or export the latter. The process is quite straightforward and
only requires a copy of Microsoft Excel and an already created CSV file. We’ll
be using Excel 2010 throughout this tutorial but the commands and procedures
should be the same on newer, or indeed older, editions.
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.
