How to use CSV's

Comma-separated values, or CSV's as they're more commonly known, are simple text files that, as the name suggests, delimit data with commas.

Each record of data in a CSV is represented on a separate line. Most times, the first line in a CSV is the headings for each of the columns. Because of their simplicity, CSV files are commonly used to import/export data from different systems.

This article will help you understand CSV files better and show you the best way to work with them so that you never have to deal with malformed data or unexpected behaviour when using the files in another system.

  Are CSV files the same as Excel files?

In short, no. It's a very common misconception is that CSV files are Microsoft Excel files. This is inherently wrong. It's quite likely that on your computer, the ".csv" file extension is automatically associated with Excel, hence why the files open with it.

However, many times the default guesswork that Excel does in order to parse the data and display it in the spreadsheet ends up being incorrectly formatted.

It's common to see dates being converted to Excel date objects or large integer numbers like credit card numbers, SINs, or phone numbers being converted to scientific notation. This usually causes a lot of confusion and makes people weary of CSV files.

If you're feeling unsure about CSVs, don't worry–you're in the right place!

Importing CSVs

In this section, we'll walk you through how to import CSV files to Google Sheets and Microsoft Excel. Use the drop-downs below to learn more:

How to Import a CSV using Google Sheets

Let's begin by creating a blank spreadsheet. Instead of using the "Open with Google Sheets" option that may pop up, it's better to start with a blank Google sheet. 

Screen_Shot_2019-05-14_at_11.06.05_AM.png

Once we have the spreadsheet created, we're ready to import the CSV data.

  1. Click on File and select Import
  2. In the pop-up, click on the Upload tab on the far right
  3. Select the CSV file you want to import
  4. After you've selected your CSV and uploaded it, you'll be presented with the import file dialogue
  5. This is the most important step. The settings you select in the import file pop-up will determine how easy or hard it will be to work with the data

    Screen_Shot_2019-05-14_at_11.12.52_AM.png

    To help, this is what we suggest:

    Import Location
    Since we created a brand new spreadsheet, the "Replace spreadsheet" option would be the best option. If you're importing the CSV into an existing spreadsheet, then the "Insert new sheet(s)" may be a better option.
    Separator Type
    It is important that you select "Comma" here. Google is pretty good at detecting the comma automatically; however, if you want to be sure, it's best to select it yourself.

    Note

    When dealing with Tab-separated value (TSV) files, the "Tab" option should be used.

    Convert text to numbers, data, and formulas
    This is where you'll choose what to do with text, numbers, dates, and formulas. If you don't want Google to automatically adjust your data, it's best to select "No" on this option. This will allow the values to be imported as simple text and not be formatted in any way.
  6. Once you hit the "Import data" button, the page will refresh, and your data will show up in all its glory in the google spreadsheet

     

Screen_Shot_2019-05-14_at_11.17.30_AM.png

From this point on, you can filter, format, and edit the CSV data as you wish without worrying about it being formatted or mangled due to improper handling.

How to Import a CSV using Microsoft Excel

Open Excel and create a new workbook - don't choose the "open with Excel" pop-up that you might automatically get. 

    1. Navigate to the "Data" tab at the top of the Excel sheet

    2. In the "Get external Data" dropdown, select the "From Text" option. (Since CSVs are simple text files, this is the best way to get them into Excel).

      excel.png

    3. This will give you a prompt to select the file you wish to import. Select the file and click "Get Data."
      get-data.png

    4. The "Text Import Wizard" window will pop up. Choose "Delimited" and then click "Next>" 

      delimited.png

    5. On the next page, under Delimiter, click "Comma" and unselect the default "Tab" delimiter. Once you do this, you will see in the preview box that the data is being parsed.
      Click "Next >" to proceed.

      comma.png

    6. On this page, we'll tell excel how to process the values in each column.

      You're able to select the boxes in the preview at the bottom of the dialogue. We need to select all columns in the preview box. To do this, click the first column, hold shift, and select the last column.

      In "Column. data format," choose "Text." This will apply to all selected columns. 

      general-text.png
    7. Click "Finish."
    8. You'll then be prompted about where to put the data. Since we've already created a brand new sheet, we can put the data in "Sheet1" starting at cell "A1."

      Note

      If you wanted to import the data into an existing spreadsheet, you can specify the exact cell or new sheet into which to put it.

    9. Once you are happy with your selection, click "Ok."
      A1.png
    10. You're done! The data will import, and you'll see your CSV data now showing on the spreadsheet.

Screen_Shot_2021-11-11_at_12.30.45_PM.png

Exporting CSVs

In this section, we'll walk you through how to export CSV files from Google Sheets and Microsoft Excel. Use the drop-downs below to learn more:

How to export a CSV from Google Sheets

This process is much easier than importing, and it can be done in 3 clicks - literally. 

Click File > Download as > Comma-separated values (.csv, current sheet)

The file will be generated and downloaded instantly.

Screen_Shot_2019-05-14_at_1.32.57_PM.png

Note

It's recommended to delete any unused columns or rows in the spreadsheet before exporting to reduce the size and complexity of the file. Less useless data equals fewer headaches.

How to export a CSV from Microsoft Excel

In order to export your Excel data, all you have to do is go to File > Save As... and from the dialogue, select the appropriate File Format.

There are two viable options you can select:

CSV UTF-8 (Comma delimited) (.csv) - This file format will automatically convert any accented characters (commonly seen with French or other non-English languages) to a UTF-8 encoded value. Depending on the system that you will be using this file into this may not be the best option because it could result in incorrectly encoded data.

Comma Separated Values (.csv) - This file format is the pure unedited values being put out in a CSV structure. Therefore this is the recommended option to use.

Screen_Shot_2019-05-14_at_2.25.36_PM.png

Was this article helpful?
1 out of 1 found this helpful

Comments

0 comments

Article is closed for comments.