| How To Convert a CSV File to Excel XLS Tutorial |
 |
| This is a guide on how to convert a CSV to
Excel. This tutorial covers Excel 2000, Excel XP, Excel 2003,
Excel 2007 and Excel 2010. |
|
| 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, although it does not work
for all CSV files. |
Open file in Excel 2010 |
|
Open file of CSV type in Excel 2010 |
|
|
Depending on the CSV file contents and your
regional settings configuration, the data in the CSV file may
not show correctly in Excel. The most common errors are:
- all data is shown on a single column
- date columns are shown as text
- text columns containing only numbers are treated as number
and leading zero removed
- monetary values shown as text
Download the file advanced-csv-sample.csv
to verify some of these errors. |
|
CSV data incorrectly formatted: Order ID without leading zeroes
and Order Date and EUR Amount as text instead of date and
number |
|
|
Open the CSV file in Notepad and notice that the leading
zeroes in the Order ID field were removed by Excel. The Order
Date field in the date format YYYYMMDD (year, month and day)
wasn't automatically recognized. Neither was the Amount in
EUR because this computer is configured to use period as decimal
symbol. Both fields appear as text instead of their correct
formats, limiting the use of the data.
Use the Text Import Tool described below to mitigate hese
errors.
|
|
| Import CSV File using Excel Text Import
Tool |
| This tutorial is based on Excel 2010. A section
after the tutorial show how to use the tutorial with Excel 2000,
Excel XP, Excel 2003, Excel 2007. |
|
| First, 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: |
|
Get External Data From Text function on Data tab |
|
| Select the CSV file to import with the Import
Text File dialog and click Import button: |
|
Use Import Text File dialog to open CSV file |
|
| 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 content. Change only if you notice strange characters in
the processed data. |
|
Configure CSV file as delimited |
|
| Define the import settings (step 2 of 3).
Select the right separator, comma in this example, to show the
values in columns. |
|
Configure delimiter of CSV file with data preview |
|
| 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 Idcontainsonly numbers Excel treats
it as number and removes the leading zeroes. If we change the
column data format to text Excel will not change its contents. |
|
Configure data format for text value in CSV file |
|
| 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
models, the coding meansY for Year, M for Month and D for Day.
In the example we change the column Order Date to the right
format. |
|
Configure data format for date value in CSV file |
|
| 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,
keet the data format as General, click the Advanced button and
configure the right decimal and thousands separators. |
|
Configure data format for amount value in CSV file |
|
| 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, there is still
one step left. |
|
Import Data dialog to define where to put the imported data
|
|
|
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:
Query Definition
Keep it checked to save the import configuration
Refresh Control
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 only 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 dataabove it
Fill down formulas in columns adjacent to data
Check this option if the sheet contais columns with formulas
alongside imported data. Its recommended to use another sheet
to process the imported data.
|
|
External Data Range Properties dialog to define where to put
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. |
|
CSV file imported with data formatted correctly |
|
| To refresh the data, importing a new file
with the same format, click on any of the cells that holds data
of a revious import. Thenon the Data tab, click on the Connections
option and select the Refresh All Icon or the Refresh All option
on its popup menu. |
|
Refresh All function to import again |
|
|
| How to Use This Tutorial on Older Versions
of Excel |
| Learn how
to convert an Excel XLS File to CSV file, this tutorial
covers Excel 2000, Excel XP, Excel 2003, Excel 2007 and Excel
2010 |
|
| |
