How to use CSV's

Have more questions? Submit a request

When working with data, it's common to come across requests for the .csv file type. For many, working with CSVs can feel a little daunting. This article will help you to understand CSV files and show you the best ways to work with them, so you can feel confident importing and exporting data like a pro.

What is a CSV file?

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. Thanks to their simplicity, CSV files are commonly used to import/export data from different systems.

  Are CSV files the same as Excel files?

In short, no. It's a common misconception that CSV files are Microsoft Excel files as many computers automatically associate the ".csv" file extension with Excel, hence why the files open with it.

However, the default guesswork that Excel does to parse the data and display it in the spreadsheet often needs to be correctly formatted after the fact.

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
  1. 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 the spreadsheet is created, we're ready to import the CSV data.

  2. Click on File and select Import
  3. In the pop-up, click on the Upload tab on the far right
  4. Select the CSV file you want to import
  5. After you've selected your CSV and uploaded it, you'll be presented with the import file dialogue
  6. 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. However, if you're importing the CSV into an existing spreadsheet, then the Insert new sheet(s) may be a better option. 
    Separator Type
    It's 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 set it yourself.

    Note

    The Tab option should be used when dealing with Tab-separated value (TSV) files.

    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 adjust your data automatically, 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.
  7. 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
  1. Open Excel and create a new workbook - we recommend ignoring the "open with Excel" pop-up you might get automatically, as it doesn't work as well as creating a new workbook from the start.

  2. Navigate to the Data tab at the top of the Excel sheet

  3. In the Get External Data drop-down, select the From Text option. (Since CSVs are simple text files, this is the best way to get them into Excel)

    excel.png

  4. This will prompt you to select the file you'd like to import.

  5. Select the file and click Get Data
    get-data.png

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

    delimited.png

  7. On the next page, under Delimiter, select Comma and unselect the default Tab option. Once you do this, you will see in the preview box that the data is being parsed

  8. Click Next > to proceed

    comma.png

  9. In this step, we'll tell excel how to process the values in each column by selecting the boxes in the preview at the bottom of the dialogue

  10. First, we need to select all columns in the preview box. To do this, click the first column, hold shift, and select the last column

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

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

    Note

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

  14. Once you are happy with your selection, click Ok
    A1.png
  15. You're done! The data will be imported, and your CSV data will show 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

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

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 in French or other non-English languages) to a UTF-8 encoded value. Depending on the system 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 denoted in a CSV structure. We recommend you choose this option.

Screen_Shot_2019-05-14_at_2.25.36_PM.png

 

  Discover more about Company

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