Text-based data files are one of the most common methods of storing data in the world today. This is because text files generally take up the last space and are the easiest to store. Fortunately, it is very easy to insert CSV (Comma Separated Values) or TSV (Tab Separated Values) files into Microsoft Excel.
To insert CSV or TSV into an Excel worksheet, you just need to know specifically how the data in the file is separated. You don’t necessarily need to know the details of the data unless you want to reformat those values into strings, numbers, percentages, and more.
In this article, you’ll learn how to insert a CSV or TSV file into your Excel worksheet and reformat that data during import to save yourself some time.
Insert a CSV file into an Excel worksheet?
Before you can insert a CSV file into your Excel worksheet, you must confirm that the data file is actually comma-separated (also known as “comma-separated”).
Check if it is a comma separated file
To do this, open Windows Explorer and navigate to the folder where the file is stored. Select the Vision menu and make sure the Preview window is selected.
Then select the file that you think contains comma-separated data. You should see a comma between each piece of data in the text file.
The example below is from a government dataset of 2010 SAT College Board student score results.
As you can see, the first line is the headline. Each field is separated by a comma. Every other line after that is a line of data, with each data point separated by a comma.
This is an example of what a comma separated values file looks like. Now that you’ve confirmed the formatting of your source data, you’re ready to insert it into your Excel worksheet.
Insert a CSV file into your worksheet
To insert the source CSV data file into your Excel worksheet, open a blank worksheet.
- Select Facts from the menu
- Select Get data from the Get Data & Transform group on the ribbon
- Select From file
- Select From Text/CSV
Note: Alternatively, you can also select From Text/CSV directly on the ribbon.
This will open the file browser. Browse to where you saved the CSV file, select it and select Import†
This opens the data import wizard. Excel analyzes the incoming data and sets all drop-down lists according to the format of the input data file, based on the first 200 rows.
You can customize this analysis by changing one of the following settings:
- File origin: If the file is of a different data type, such as ASCII or UNICODE, you can change it here.
- separator: If semicolons or spaces are used as alternative separator, you can select it here.
- Data type detection: You can force Excel to analyze against the entire dataset instead of just the first 200 rows.
When you are ready to import the data, select load at the bottom of this window. This will put the entire data set into your blank Excel worksheet.
Once you have that data in an Excel worksheet, you can reorganize that data, group rows and columns, or perform Excel functions on the data.
Import CSV file into other Excel elements
A worksheet isn’t the only thing you can import CSV data into. If you select in the last window: Load to instead of Loading, you will see a list of other options.
Options in this window include:
- Table: This is the default setting that will import the data into a blank or existing worksheet
- PivotTable Report: Include data in a PivotTable report that allows you to summarize the incoming dataset
- PivotChart: Display the data in a summarized chart, such as a bar chart or pie chart
- Connect only: Connects to the external data file, which you can later use to create tables or reports on multiple worksheets
The PivotChart option is very powerful. This allows you to skip the steps to save the data in a table and then select fields to create charts or graphs.
As part of the data import process, you can choose the fields, filters, legends, and axis data to create those images in one step.
As you can see, there is a lot of flexibility when it comes to inserting CSV into an Excel worksheet.
Insert a TSV file into an Excel worksheet?
What if your incoming file is tab delimited instead of comma delimited?
The process is much the same as in the previous section, but you will separator drop-down list to select tab†
Also note that when you browse to the data file, Excel automatically assumes that you are looking for a *.csv file. So, in the file browser window, remember to change the file type to All files (*.*) to see the *.tsv type file.
After you select the correct separator, importing data into an Excel worksheet, pivot chart, or pivot report works exactly the same way.
How data transformation works
If you select in the Import Data window: Transforming data instead of selecting loadthe Power Query Editor window opens.
This window gives you insight into how Excel automatically converts the data that is imported. This is also where you can customize how that data is converted during import.
If you select a column in this editor, you will see the assumed data type under the Transform section on the ribbon.
In the example below, you can see that Excel assumed that you would like to convert the data in that column to an integer format.
You can change this by selecting the down arrow next to the data type and selecting the desired data type.
You can also reorder columns in this editor by selecting a column and dragging it to the desired position in your worksheet.
If your incoming data file does not have a header, you can change it: Use first row as header until Using headers as the first row†
Normally, you should never use the Power Query Editor, as Excel is quite good at analyzing incoming data files.
However, if those data files are inconsistent in the formatting of the data, or if you want to reorganize the way the data is displayed in your worksheet, you can do that with the Power Query Editor.
Is your data in a MySQL database? Learn how to connect Excel to MySQL to get that data. If your data is already in another Excel file, there are also ways to merge data in multiple Excel files into one file.