How to import a CSV into Excel

What is a CSV file?

CSV stands for Comma Separated Values. It is a text file where raw table data is separated by comma characters. If you open up a CSV file in a text editor you will see values in between each comma. Each of these separated bits of data will be cells when the file is imported into Excel.

How to Import a CSV into 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. 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 a URL that downloads a CSV file.

https://api.csvgetter.com/AADVgE0BE2k04FgjZJtX

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

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.