Skillsheet-Import CSV data into Excel

The CSV (comma-separated values) format is used frequently to distribute data, online or on removable media such as USB drives or DVDs. In this skillsheet, we show you how to import a CSV file into Microsoft Excel. We’re using Excel 2016 for the Mac. The process on a Windows computer is pretty much the same.

We’ll use data on people removed from Canada by the Canada Border Services Agency, available from the federal government’s open data site at http://open.canada.ca/data/en/dataset/87ccfc74-1065-4399-aeb7-34a07de25c0b.

We’ll begin with an empty workbook in Excel, and click on From Text on the Data ribbon.

In the dialog that opens, navigate to the file on your computer, and click Get Data. That brings up the Text Import Wizard. You’ll see that it has recognized the file, correctly, as a delimited text file. You can see the first few rows of the file in the preview window at the bottom of the dialogue. Notice that numbers containing commas to separate the thousands are enclosed in quotation marks. The quotation marks will allow Excel to ignore those commas when it is dividing the data into columns.

If we expand the File origin dropdown menu we can indicate the type of character encoding to be used to read the data. Good options to try, depending on whether the file is being imported into a Mac or Windows version of Excel, are Macintosh or Windows (ANSI). If you get garbled data in the imported sheet, go back and try a more expansive option, such as Unicode (UTF-8). You are more likely to run into issues if your file contains non-English language characters. This is frequently an issue in Canada as the French-language alphabet contains several accented characters not used in English. When you are done with this dialogue, click Next to bring up this dialogue.

Here, you can indicate the delimiting character to be used, in this case a comma. Leave Text Qualifier checked to handle any quotation marks around data containing commas not meant to be delimiters. There could be instances when a quotation mark was used literally, as a character within a field. You would need to select None in that instance. You can also choose single quotation marks if they are being used as the text qualifier.

You will see a preview of what the imported data will look like. If it looks good, click Next.

In the third step of the wizard, you can change the data format for individual columns. General will cover most types of data, but you should change the type to Date for date fields, and indicate the order of the elements of the date, with year/month/day being the default. You can also set a field to text format, which is useful particularly in instances such as when a number is meant to be read as text and contains leading zeros or trailing zeros after a decimal point, both of which would be stripped off if the data were treated as numbers. Just click on the top of each column, where it says General, to make the change.  The Advanced options button allows you to change the character used for thousand separators and that used for decimals. Again, this can be important in Canada, where the French-language usage is to use a comma for the decimal separator and a blank space for the thousands separator, as in 2 000,00 $.

When you are finished with this dialogue, click Finish. In the final step, you will be asked whether you want to import the data into the sheet where you began, or into a new sheet. Choose the appropriate option, then click OK to complete the import.

You’re all set to start using the data!

Any questions? Contact us at kingsdata@ukings.ca.