Convert a CSV File to Excel 2007 XLS Tutorial
How To Convert a CSV File to Excel 2007 XLS Tutorial
This is a guide on how to convert a CSV to Excel 2000, Excel XP, Excel 2003, Excel 2007 and Excel 2010.
This tutorial covers Excel 2007. If you use a different version of Excel click on the version below:
Open the CSV File
When you select the option to open a file in Excel, you will see the option Text Files that includes CSV files. The CSV file has an icon indicating that it is supported by Excel. This is the fastest method and the default action when you double click on a CSV file, although it does not work with all CSV files.
Depending on the CSV file contents and the regional settings configuration of Windows, the data in the CSV file may not show correctly in Excel. The most common errors are:
- data is shown on a single column
- date columns are shown as text
- text columns containing only numbers are treated as number with leading zero removed
- currency values treated as text
Download the file advanced-csv-sample.csv to reproduce some of these errors.
Open the CSV file in Notepad and notice that the leading zeroes in the Order ID field were removed by Excel. The Order Date value in the date format YYYYMMDD (year, month and day) wasn’t automatically recognized. Neither was the Amount in EUR value because this computer is configured to use period as decimal symbol. Both values appear as text instead of their correct formats, limiting the use of the data.
Use the Text Import Tool described below to mitigate these errors.
Import CSV File using Excel Text Import Tool
Click the Data tab on the Excel ribbon, then click the Get External Data option and select From Text on the popup menu, as shown below:
Select the CSV file to import with the Import Text File dialog and click Import button:
Define the import settings (step 1 of 3). The contents of the file are shown, notice the use of comma to separate values and double quotes enclosing the EUR amount value because it contains a comma. Keep the data type as delimited to configure the delimiter in the next screen. You may need to change the File Origin to Windows (ANSI) depending on the file contents. Change it only if you notice weird characters in the processed data.
Define the import settings (step 2 of 3). Select the right separator, comma in this example, to show the values into columns.
Define the import settings (step 3 of 3 – part 1). Review and if necessary change the data format of each column. As the column Order Id contains only numbers Excel treats it as number and removes the leading zeros. If we change the column data format to text Excel will not change its contents.
Define the import settings (step 3 of 3 – part 2). Excel recognizes some date formats. If it does not, change the data format manually defining one of the supported formats. In the example we change the column Order Date to the right format. YMD means that the date is in the format Year – Month – Day.
Define the import settings (step 3 of 3 – part 3). Sometimes even number columns need to be configured. In this example the Amount EUR column is using a different decimal symbol than the system’s configuration. To configure these cases, keep the data format as General, click the Advanced button and configure the decimal and thousands separators used in the column.
Define the destination of the imported data. After you finish configuring the wizard, Excel opens the Import Data dialog asking where to put the results. Select the proper location but do not click the OK button yet, as there is still one step left.
Click the Properties button to define the properties of the import process, so it can be used more times without the need of reconfiguring everything:
Keep it checked to save the import configuration (delimiter, column formats)
The refresh control defines if the sheet will be automatically refreshed, if the file name changes and if the sheet should be refreshed and emptied when opened or closed. Uncheck the “Prompt for file name on refresh” option if the file always have the same name and location.
Data formatting and layout
Normally nothing to change
If the number of rows in the data range changes upon refresh
This configuration depends on the source file, if it always contain all data use the overwrite option, the insert options will move existing data down and load the new data above it
Fill down formulas in columns adjacent to data
Check this option if the sheet contains columns with formulas alongside imported data. Its recommended to use another sheet to process the imported data.
Click OK to close the External Data Range Properties dialog and click OK to close the Import Data dialog and import the data on the selected destination. If the data is formatted correctly, save the sheet.
To refresh the data, importing a new file with the same format, click on any of the cells that holds data of a previous import. Then on the Data tab, click on the Refresh All Icon or the Refresh All option on its popup menu.