Convert CSV to Excel - How to import CSV into Excel

Quick links

Import csv into excel 2016 or Excel 2013

Copy pasting CSV (Comma Separated Values) is pretty easy in Microsoft Excel !

What is a CSV file (Comma Separated Values) on Wikipedia

In short : use the Data > Text to Columns option.

How to open csv file in excel with columns

Starting with a CSV file, like the following one, open it in Excel by using the standard Excel file opening, using keyboard shortcut CTRL+O, or by opening it in Windows Explorer with right clic and selection Open with Excel 2010 / Excel 2013 / Excel 2016, depending on the version you have installed.

Each row has been put in one single cell, while you might want them to be separated by columns, therefore the upcoming steps.

Convert CSV to Excel - How to import CSV into Excel : CSV Comma Separated Values file
CSV Comma Separated Values file

Convert csv to excel 2016

Select the column containing the CSV data spreadsheet that has just been pasted into an open Excel spreadsheet.

Convert CSV to Excel - How to import CSV into Excel : Select CSV data in microsoft excel column
Select CSV data in microsoft excel column

Then click on the Data > Text to Columns option, which will allow Excel to convert CSV to columns from a single column.

Convert CSV to Excel - How to import CSV into Excel : Excel Data > Text to Columns option
Excel Data > Text to Columns option

In the Convert Text to Column Wizard, step 1, leave the default option, Delimited, as the CSV file usually delimits cells with a character, such as comma, tabulation, pipe, or semicolon.

Convert CSV to Excel - How to import CSV into Excel : Step 1 select Delimited data type
Step 1 select Delimited data type

In the Convert Text to Column Wizard, step 2, select Comma as a Delimiters (or another one if necessary, like semicolon ; or pipe |).

When the right microsoft excel csv delimiter will have been chosen, the CSV comma delimited Excel will be displayed properly, as below, with data from CSV into Excel columns as below example.

Convert CSV to Excel - How to import CSV into Excel : Step 2 In Excel change CSV separator if necessary, from comma separated file format by default
Step 2 In Excel change CSV separator if necessary, from comma separated file format by default

In the Convert Text to Column Wizard, step 3, you might want to change all the non numerical columns formatting to Text, this to avoiding loosing leading zeros for example.

CSV data format

As the comma delimited file format is text only, if it is storing any column with numbers, MS Excel might recognize at this step these columns as numerical for example.

In some cases, it might be correct - if the column really only contains numeric values.

But in other cases, it might be wrong. Excel bases its detection of the column format on the first 100 rows, but it might be that a column has numbers only in the first 100 rows, but contains text afterwards.

If the format Text has not been chosen, then they will be detected as numbers, and an error will be displayed.

Another case, if a number like a barcode is stored, for example numbers 001234, then, Excel will detect them as numbers and store only 1234, while they should be stored as text 001234.

What is leading zero on webtopedia
Convert CSV to Excel - How to import CSV into Excel : Step 3 change column format to text
Step 3 change column format to text

Simply click on the first column, then while holding Shift butting, click on the last one of your selection - you can also add columns to the selection by holding Ctrl and clicking on the column - then select Column data format as Text to format them all as text and lose no data.

Column format can always be fine tuned as necessary afterwards.

Convert CSV to Excel - How to import CSV into Excel : Step 3 select multiple Excel spreadsheet columns
Step 3 select multiple Excel spreadsheet columns

Before hitting the Finish button, in case you get the error message "There's already data here. Do you want to replace it ?", or to avoid getting it.

Convert CSV to Excel - How to import CSV into Excel : There's already data here. Do you want to replace it ?
There's already data here. Do you want to replace it ?

Select another Destination cell, and click on an empty cell, either in the current excel sheet, or in another excel sheet columns.

Convert CSV to Excel - How to import CSV into Excel : Select an empty cell
Select an empty cell

You can then click Finish - and voilà ! Your CSV values have been pasted properly in Excel cells.

Convert CSV to Excel - How to import CSV into Excel : Result after formatting CSV file into Excel spreadsheet cells
Result after formatting CSV file into Excel spreadsheet cells

What is a CSV file

A CSV (or Comma Separated Values), is a plain text file which contains the equivalent of a spreadsheet, or a table, stored as plain text. Each cell is typically separated by a comma, therefore the name, but another character can be used, for example tabulation or semicolon.

How to force excel to open csv files with data arranged in columns

This is not possible directly. Data has to be opened as explained above, and then arranged to columns.

Convert csv to excel without opening

This is not possible. CSV file first has to be opened in Excel, converted properly to columns using Excel built-in options, and then saved as Excel file.

How to copy paste in excel sheet

The above trick can also be used to paste CSV data in Excel ! Simply paste the whole CSV file in an open Excel spreadsheet, and convert it to Excel columns.

Buy Microsoft Office 2016

Similar articles

Comments (0)

Leave a comment