Text data files are one of the most common data storage methods in the modern world. This is because text files tend to take the last place 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.
If you want to insert CSV or TSV into an Excel worksheet, you only need to know how exactly the data in the file is split. You don’t need to know the details of the data, unless you want to convert those values ??to strings, numbers, percentages, and more.
In this article, you will learn how to insert a CSV or TSV file into an Excel worksheet and how to reformat this data during the import process to save time.
How to insert a CSV file into an Excel worksheet
Before you can insert a CSV file into an Excel sheet, you need to make sure that the data file is actually comma separated (also known as “comma separated”).
Make sure it’s a comma separated file
To do this, open a window explorer and change to the directory where the file is stored. Select the View menu and make sure the View panel 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 containing 2010 SAT College Board student results.
As you can see, the first line is the title line. Separate each field with a comma. Each line following it is a data line, each data point of which is separated by a comma.
This is an example of what a comma separated values ??file looks like. Now that you have confirmed the formatting of the original data, you are ready to paste it into the Excel sheet.
Insert CSV file into your worksheet
Insert the CSV file into your worksheet
To insert the original CSV data file into an Excel sheet, open a blank sheet.
- Choose Data from the menu.
- Select Get Data from the Get and Transform Data group on the ribbon.
- Select from file
- Select from text / CSV
Note. Alternatively, you can also select From Text / CSV directly from the ribbon.
This will open a file browser. Find where you saved the CSV file, select it and click Import.
The Data Import Wizard will open. Excel parses the input and sets all the dropdowns to match the input file format based on the first 200 lines.
You can customize this analysis by changing any of the following options:
- File Source: If the file is of a different data type, such as ASCII or UNICODE, you can change this here.
- Separator: If semicolons or spaces are used as alternate separators, you can select that here.
- Datatype Determination: You can force Excel to perform analysis on the entire dataset, not just the first 200 rows.
When you’re ready to import your data, select “Download” at the bottom of this window. This will transfer the entire dataset to your blank Excel sheet.
Once you have this data in your Excel sheet, you can reorganize that data, group rows and columns, or perform Excel functions on the data.
Import CSV file into other Excel items
Import CSV file into other Excel elements
A table is not all you can import CSV data into. If in the last window you choose “Load to” instead of “Load”, you will see a list of other options.
Options in this window include:
- Table: This is the default option, which imports data into a blank or existing worksheet.
- PivotTable Report: Transfer data to a PivotTable report that allows you to summarize the incoming dataset.
- Pivot Chart: Displays data in a pivot chart, such as a bar or pie chart.
- Create Connection Only: Creates an external data connection file that you can use later to create tables or reports on multiple sheets.
The PivotChart option is very powerful. It allows you to skip the steps of saving data to a table and then selecting fields to create charts or graphs.
During the data import process, you can select fields, filters, legends, and axis data to create these graphs in one step.
As you can see, inserting CSV into an Excel worksheet is very flexible.
How to insert a TSV file into an Excel worksheet
What if your input file is separated by tabs, not commas?
The process is basically the same as in the previous section, but you’ll want to use the Delimiter dropdown to select tabs.
Also remember that when you view a data file, Excel automatically assumes that you are looking for a * .csv file. Therefore, in the file browser window, do not forget to change the file type to All files (*. *) To see the file type * .tsv.
Once you’ve selected the correct delimiter, importing data into any Excel sheet, PivotChart, or Pivot Report will work the same way.
How does data conversion work
If you select Convert Data instead of Load in the Import Data window, the Power Query Editor window opens.
This window gives you an idea of ??how Excel automatically converts the imported data. Here you can also configure how this data is converted during import.
If you select a column in this editor, you will see the intended data type in the Transform section of the ribbon.
In the example below, you can see that Excel assumed that you wanted to convert the data in this column to integer format.
You can change this by clicking the down arrow next to the data type and choosing the data type you want.
You can also change the order of the columns in this editor by selecting the column and dragging it to the desired location on the sheet.
If the incoming data file does not have a header line, you can change the Use First Line As Headers option to use headers as the first line.
Usually, you will never need to use the Power Query Editor because Excel parses input data files quite well.
However, if these data files do not conform to the data format, or if you want to change the way the data is displayed in the worksheet, Power Query Editor allows you to do so.
Is your data in MySQL database? Learn how to connect Excel to MySQL to get this data. If your data is already in another Excel file, there are also ways to combine data from multiple Excel files into one file.
–