How to import a CSV into Excel

How to import a CSV into Excel
I have a CSV file - but I want the data in Excel. What should I do?

Luckily, you are already have everything you need to begin working in Excel. A CSV file is just a basic form of an Excel file. It has the data in its raw format but has no saved Excel formatting.

CSV stands for Comma Separated Values. If you open up a CSV file in a text editor you will see the raw data separated by commas. Each of these separated bits of data will be your cells in Excel.

So what next?

Open the CSV file in Excel, and you will see the data in its standard tabular format. You can usually open a file in your preferred software by right clicking and pressing Open with...

At this point, you will want to be careful if you wish to add Excel formatting. You are editing a CSV file in Excel (.csv) and not an Excel file in Excel (.xlsx).

A potential mistake:
  1. Use Excel to open a CSV file (.csv).

  2. Add formatting, formulas and other Excel wizardry to super charge your data.

  3. Press save without switching to .xlsx. The data will save as a .csv, and all of the Excel goodness and a lot of your work will be lost.

To avoid this scenario, be sure to overwrite your file as .xlsx as soon as possible. This can be done by using Save as... and specifying Excel workbook. (We recommend doing this as soon as you load your data, so you do not forget later on.)

Importing CSV Data from Web

This can be helpful if you are working with data made available via a URL. Below is such an example.

https://api.csvgetter.com/AADVgE0BE2k04FgjZJtX

// This URL downloads a CSV file

A URL like this can be easily imported into an Excel workbook. This can be done in a few simple steps.

Step 1.

Open a new Excel workbook.

Step 2.

Click the Data tab in the navigation panel.

Step 3.

Click From Web.

Step 4.

Paste your CSV URL in the URL field.

Step 5.

Click OK, and review your import data.

Step 6.

Click Load.

And your web data is in Excel. 🎉

Advanced setup

Some data links may be protected by a security measure like a bearer token. For example, if you are creating a CSV download URL with CSV Getter, you may want to password protect your data by setting a bearer token.

See examples of bearer tokens here.

If you are wanting to access a URL which is protected by a bearer token in Excel, you can used the Advanced import option.

Locked URL example
https://api.csvgetter.com/Q1st6NzPE9T7d8hOQWcW

// This CSV data cannot be downloaded without the bearer token (401 Unauthorized).
Step 1.

After clicking From Web, click Advanced.

Step 2.
  • Paste in your protected URL as a URL part.

  • In the HTTP request header parameters form section, add Authorization: Bearer <your token>.

In this example, the bearer token we set is csvgetter.

Step 3.

Click OK. Review your data as in previous example, and click Load.

And like before the data should load in Excel.

Gavin
Gavin