There are two ways to import data from CSV files to Excel. Open a CSV file by double-clicking on it or use the import function. The import function has a major advantage: You can define the details of the import, for instance, set the meaning of thousand separators. In this article we walk through all the steps for importing CSV data into Excel.
How to import CSV data manually into Excel
If you decide to import CSV data manually (not by double clicking on the file) into Excel, follow these steps: (the numbers are corresponding to the picture above)
- Start the import by clicking on “From Text” on the Data ribbon.
- Select your file to be imported.
- Select “Delimited” if the values are separated by a specific character (comma, semi-colon). Sometimes – and depending on the regional and language settings of your file – you have to specify the file origin. Also, if you want to skip the first rows, you can define that with “Start import at row”.
- Click “Next”.
- Choose the separator. In our case it is the semi-colon. But it is often the comma (as the abbreviation CSV already says…).
- Click “Next”.
- If you want to make sure every column is recognized correctly, click on each column header…
- …and define the data format.
- You can specify which character indicates decimal- and thousand separators by clicking on “Advanced”. Confirm with “OK”.
- Finalize the import by clicking “Finish” and define the import location.
Unfortunately, there is no option to save these import steps (except writing a VBA macro).
One more advice: If you want to redo the import (for example if some settings were wrong), you should use a new worksheet. Sometimes, existing connections can’t be overwritten.
