Analyzing exchanged data can sometimes cause frustration, not everyone uses Excel for their own work. And even if they do, it’s not always easy to integrate data from one document into another without having to do a complete recalculation. Luckily, you don’t have to type out endless rows of data by hand to use them in your Excel table, fortunately, there are CSV files for this.
With Excel 2016, Microsoft has added a new function to this successful office program that simplifies such work. With the “Import Data” function, you can now import data into Excel, add your own calculations, and evaluate them.
What is a CSV file?
A CSV (Comma separated values) file is a simplified text or database file that is used in most cases to exchange simply structured data. You can save tables or lists of any length in such CSV files. Most text or database programs such as Excel can export and read CSV files, making this a practical means of exchanging universally readable data sets.
CSV files are often found in the field of accounting in particular, because they can be used to easily exchange large amounts of data about customers, products and invoices. For this reason, many accounting programs can output CSV files so that the company’s accounting or controlling can work with the data without needing access to the actual accounting program or having to copy the data manually.
Import CSV file into Excel
In the “Data” tab, you will find the “Get & Transform Data” group, and here you will find the “Get Data” option. In the drop-down menu, select “From File” and then “From Text/CSV.” A new window will open in which you can search for the file. Confirm your selection by clicking “Import.”
After you have imported the file into Excel, a preview window will open. In this window, you can check whether Excel has correctly recognized the data and its structure. If that didn’t work automatically, you can fix the file origin variant.
By clicking “Transform Data,” you first load the data into Excel’s “Power Query Editor.” In this editor, you can correct individual errors that may have occurred during data transformation. As a rule, however, the import function of Excel works very reliably, so you usually do not have to make any corrections here.
In the query editor, under the “Home” tab, you can remove one or more columns and/or rows, split columns and create groups. In the “Add column” tab you will find various options for adding columns, merging columns, etc. Among them are also useful tools such as automatically adding a date or time to an entry. In the “Transform” tab you can edit the imported data by defining headings, inverting rows, etc.
Edit imported data
After you have checked the display of the data in the Query Editor and confirmed with “Close and Load”, Excel adds the data to the currently selected worksheet.
You can now use the data as if you had created it yourself. If categories, groups and sortings already exist, Excel applies them to the imported data as well.
If these are not yet available, you can use Excel without formulas to organize large amounts of data.
Output Excel spreadsheet as CSV
Conversely, if you want to save your Excel table as a CSV file, go to the “File” tab and on the left to the “Export” menu item.
Under “Export” you will find various options to export your spreadsheet. For a CSV file, select the bottom item “Change file type” and select the option “CSV (comma separated)” (*csv) with a double-click.
Quick guide: Import CSV files into Excel
- Switch to the “Data” tab and click on “From Text/CSV” in the “Get & Transform Data” section.
- Locate and select the CSV file you want to import and click on “Import”.
- Edit the CSV file as you like in the Power Query Editor.
- Confirm your data selection in the Query Editor with “Close and Load”.
