How to convert (open or import) CSV file to Excel

The tutorial explains how to quickly convert CSV files to Excel in any version, from 365 to 2007, avoiding typical issues.

Generally, there are two ways to transfer a CSV file to Excel: by opening it or importing as external data. This article provides the detailed guidance on both methods and points out the strengths and limitations of each. We will also red-flag possible pitfalls and suggest the most effective solutions.

Convert CSV file to Excel by opening it

To bring data from a CSV file to Excel, you can open it directly from an Excel workbook or via Windows Explorer. Whichever method you choose, please keep in mind that:

  • Opening a CSV document in Excel does not change the file format to .xlsx or .xls. The file will retain the original .csv extension.
  • The files are limited to 1,048,576 rows and 16,384 columns.

How to open CSV file in Excel

A comma separated values file created in another program can still be opened in Excel by using the standard Open command.

  1. In your Excel, head over to the File tab and click Open, or press the Ctrl + O shortcut.
  2. In the Open dialog box, select Text Files (*.prn;*.txt;*.csv) from the drop-down list in the lower right corner.
    Opening a CSV file in Excel

  3. Browse for the CSV document, and then double-click it to open.

A comma separated values file (.csv) will be opened in a new workbook straight away.

For a text file (.txt), Excel will start the Import Text Wizard. See Importing CSV to Excel for full details.

How to open CSV file from Windows Explorer

The fastest way to open a .csv file in Excel is to double click it in Windows Explorer. This will immediately open your file in a new workbook.

However, this method only works only if Microsoft Excel is set as the default app for .csv files. In this case, a familiar green Excel's icon Excel icon appears next to .csv documents in Windows Explorer.

If your CSV files are set to open with another default app, then right-click the file, and choose Open with… > Excel.

To set Excel as the default program for CVS files, here are the steps to perform:

  1. Right-click any .csv file in Windows Explorer, and then select Open with… > Choose another app from the context menu.
  2. Under Other options, click Excel, check off the Always use this app to open .csv files box, and click OK.
    Setting Excel as the default app for .csv files

Convert CSV to Excel by importing it

Using this method, you can import data from a .csv file into an existing or a new Excel worksheet. Unlike the previous technique, it not just opens the file in Excel but changes the .csv format to .xlsx (Excel 2007 and higher) or .xls (Excel 2003 and lower).

Importing can be done in two ways:

  • By using Text Import Wizard (in all versions)
  • By creating a Power Query connection (in Excel 2016 - Excel 365)

How to import CSV into Excel with Text Import Wizard

First off, it should be noted that the Text Import Wizard is a legacy feature, and beginning with Excel 2016 it is moved from the ribbon to Excel Options.

If the Text Import Wizard is not available in your Excel version, you have these two options:

  • Enable From Text (Legacy) feature.
  • Get Excel to launch the Import Text Wizard automatically. For this, change the file extension from .csv to .txt, open the text file from Excel, and then follow the steps of the wizard described below.

To import a CSV file to Excel, this is what you need to do:

  1. In Excel 2013 and earlier, go to the Data tab > Get External Data group, and click From Text.
    From Text option in Excel 2013 and earlier

    In Excel 2016 and later, go to the Data tab > Get & Transform Data group, and click Get Data > Legacy Wizards > From Text (Legacy).
    From Text (Legacy) wizard in Excel 2016 - Excel 365

    Note. If the From Text wizard is not there, make sure you have it enabled. If Legacy Wizards is still grayed out, select an empty cell or open a blank worksheet and try again.

  2. In the Import Text File dialog box, browse for the .csv file you want to import, select it and click the Import button (or simply double-click the file).
    Start the Text Import Wizard.

  3. The Text Import Wizard will start, and you follow its steps. First, you choose:
    • The Delimited file type
    • The row number to start import at (normally, row 1)
    • Whether your data has headers

    The preview window in the lower part of the wizard shows a few first entries from your CSV file.
    Choose the Delimited file type and the row number to start import at.

  4. Choose the delimiter and text qualifier.

    Delimiter is the character that separates values in your file. As CSV is a comma separated values file, obviously you select Comma. For a TXT file, you'd typically select Tab.

    Text qualifier is the character that encloses the values in an imported file. All text between two qualifier characters will be imported as one value, even if the text contains the specified delimiter.

    Generally, you choose the double quote symbol (") as text qualifier. To check this, you can click Back and see which character encloses the values in the preview of your CSV file.

    In our case, all numbers with a thousands separator (which is also a comma) are wrapped in double quotes like "3,392", meaning they will be imported in one cell. Without specifying the double quote sign as the text qualifier, the numbers before and after a thousands separator would go into two adjacent columns.

    To make sure your data will be imported as intended, look carefully at the Data preview before clicking Next.
    Select the delimiter and text qualifier

    Tips and notes:

    • If more than one consecutive delimiter is present in your CSV file, then select the Treat consecutive delimiters as one option to prevent empty cells.
    • If the preview shows all data in one column, that means a wrong delimiter is selected. Change the delimiter, so that the values are displayed in separate columns.
  5. Define data format. The default is General - it converts numeric values to numbers, date and time values to dates, and all remaining data types to text.

    To set another format for a specific column, click anywhere within it in the Data Preview, and then choose one of the options under Column data format:

    • To keep leading zeros, choose the Text format.
    • To correctly display dates, choose the Date format, and then pick an appropriate format in the drop-down box.

    When you are happy with the Data preview, click the Finish button.
    Set data format for each column

  6. Choose whether to import data to an existing worksheet or a new one, and click OK.
    Choose the destination worksheet for the imported CSV file.

    Tips and notes:

    • To configure some advanced options such as refresh control, layout and formatting, click Properties… in the above dialog box.
    • If some imported data are displayed incorrectly, you can change the format with the help of Excel's Format Cells feature.

Here are a couple of helpful tips on how to prevent automatic data formatting when importing from a text file.

How to enable Text Import Wizard in Excel 2016 - Excel 365

To activate the Text Import Wizard in modern versions of Excel, this is what you need to do:

  1. Click the File tab, and then click Options > Data.
  2. Under Show legacy data import wizards, select From Text (Legacy), and click OK.
    Enable the Text Import Wizard in Excel 365 - Excel 2016

Once enabled, the wizard will appear on the Data tab, in the Get & Transform Data group, under Get Data > Legacy Wizards.

How to transfer CSV to Excel by connecting to it

In Excel 365, Excel 2021, Excel 2019 and Excel 2016, you can import data from a text file by connecting to it with the help of Power Query. Here's how:

  1. On the Data tab, in the Get & Transform Data group, click From Text/CSV.
    From Text/CSV

  2. In the Import Data dialog box, select the text file of interest, and click Import.
    Choose a CSV file to import.

  3. In the preview dialog box, the following options are available to you:
    • Delimiter. Pick the character that separates values in your text file.
    • Data Type Detection. You can let Excel automatically determine data type for each column based on the first 200 rows (default) or entire dataset. Or you can choose not to detect data types and have the data imported in the original Text format.
    • Transform Data. Loads the data to the Power Query Editor, so you can edit it before transferring to Excel. Make use of this feature to set the desired format for particular columns.
    • Load. Controls where to import the data. To get the csv file imported to a new worksheet, select Load. To transfer the data to an existing or new sheet in the form of a table, PivotTable/PivotChart, or only create a connection, choose Load to.

    Data preview

    Clicking the Load button will import the CSV data in the table format like this one:
    The CSV data is imported as an Excel table.

    The imported table is linked to the original CSV document, and you can update it anytime by refreshing the query (Table Design tab > Refresh).

    Tips and notes:

    • To change the table to a normal range, right-click any cell, and then click Table > Convert to Range. This will permanently remove the query from the sheet and disconnect the imported data from the original file.
    • If values in a certain column are imported in a wrong format, you can try fixing it yourself by converting text to number or text to date.

Converting CSV to Excel: opening vs. importing

When Microsoft Excel opens a .csv file, it uses your default data format settings to understand how exactly to display each column of text data. This works fine in most situations.

If your text file has specific values and you want to control how to display them in Excel, then do importing rather than opening. Here are a few typical use cases:

  • The CSV file uses different delimiters.
  • The CSV file contains different date formats.
  • Some numbers have leading zeroes that should be kept.
  • You want to see a preview of how your CSV data will be converted into Excel.
  • You are looking for more flexibility in general.

How to save CSV file in Excel

Whichever conversion method you used, you can save the resulting file like you normally would.

  1. In your Excel worksheet, click File > Save as.
  2. Browse for the folder where you want to save the file.
  3. To save as an Excel file, select Excel Workbook (*.xlsx) from the Save as type drop-down menu. To save as a comma-separated file, select CSV (Comma delimited) or CSV UTF-8.
  4. Click Save.
    Saving CSV file in Excel

If you saved a CSV file in the .xls format in earlier versions, then in Excel 2010 and higher you may encounter the error "The file is damaged and cannot be opened". Try to follow these recommendations to open a corrupt .xls file.

How to open multiple CSV files in Excel at once

As you probably know, Microsoft Excel allows opening several workbooks at a time using the standard Open command. This also works for CSV files.

To open multiple CSV files in Excel, here are the steps for you to follow:

  1. In your Excel, click File > Open or press the Ctrl + O keys together.
  2. Click the Browse button and navigate to the source folder.
  3. In the drop-down list next to the File name box, select Text Files (*.prn, *.txt, *.csv).
  4. Select your text files:
    • To select adjacent files, click the 1st file, hold down the Shift key, and then click the last file. Both of the clicked files as well as all the files between them will get selected.
    • To select non-adjacent files, hold down the Ctrl key and click each individual file you want to open.
  5. With multiple files selected, click the Open button.
    Opening multiple csv files in Excel

In Windows Explorer, you can right-click the selected files and pick Open from the context menu.

This method is straightforward and quick, and we could call it perfect but for one small thing - it opens each CSV file as a separate workbook. In practice, switching back and forth between several Excel files may be quite inconvenient and burdensome. Instead, you can have all files imported into the same workbook - the detailed instructions are here: How to merge multiple CSV files into one Excel workbook.

Hopefully, now you are able to convert any CSV files to Excel with ease. And thank you for your patience everyone who has read this tutorial to the end :)

Table of contents

97 comments

  1. Hi,
    I am facing a problem while opening a CSV file in XLS format.
    The size of XLS file is more than 65K rows and even if i am trying to open it in excel2010 its throwing an error.
    However if i will reduce the size its working fine.

    The file generating from a tool which has a limitation of supporting excel 2000 or below only.

    I can generate the file using CSV format but can't read it.
    Please Suggest how i can open and read the file of size grater than 65K rows.

  2. Dear sir\ madam
    I need csv file

  3. thank you so much dear admin
    it was very nice & detailed guidelines for
    very neatly u explained the things it was so good
    once again thank you so much for your guide lines

  4. How to prevent a .txt file from converting a long number to scientific notation? I have been importing txt files with the same format and everything worked fine until I get to the last two files. There is a column that is 14 characters long that was behaving as expected but this time, after changing each column to the appropriate type and converting, the 14 characters that show correctly in the data to be converted, are displayed in scientific notation (e.g., 12345678998834 converts to 12345+...)

    • Hi David did you get a response as I have the same problem
      Thanks

      • I have the same issue
        did any of you find a way around this?

    • Hello, David,

      Please do the following:
      1. Run the standard Excel Text to columns option.
      2. On step 3 select the Test radio button for your column with numbers and click Finish.

      After that you will be able to add any format to this column and your numbers will stay as are.

  5. Product : HT630. We have a problem when we want to transfer the data we scanned from the scanner to an excel file on our computer with the CSV format. For example if we put the number 100 on the scanner, the 0 will vanish and we'll get 1 on the escel file. 2000 will become 2, 50 will become 5, and so on. When we use the text format, the 0 will display but not with the CSV format. Do you have an explanation. Does the problem come from the scanner or excel? Thank you very much.

    • Hello,

      To fins the cause of the problem, please try the following:
      1. Create a text document with some numbers like 200, 100, 2000 and so on.
      2. Convert this doc to an Excel file.
      3. See the values you get.

      If they are different most likely the issue is caused by Excel.

  6. Awesome work, Thanks a ton

  7. can i import multiple csv files with first column as name of the CSV file for identification of where data is coming from. I have four regions North south Central and North2 and files are saved as north-date.csv , south-date.csv and vice versa can i add a column which can help me in seing that from which reigon i am getting the data in one worksheet ?

  8. Hello,

    step 1 in the wizard does not show the other files to be merged. When i try to Add files, I cannot see the files as in the drop down to choose the type of file, there is no option to choose CSV files. It is limited to excel workbook 2003 or 2007.
    Please advise how i can merge them if i cannot choose my CSV files.
    P.s i have excel 2007

    Thank you !

    • Hello, Diana,

      Please choose the Excel files option in the Files of type list. This should include the csv files as well.

  9. Hi Team

    I am wondering will the values get change when the csv file is opened and changed the format to Excelfile directly through "Save as" option. Please advise me. Thanks in Advance :)

  10. Dear Admin,

    Thank you so much for this useful information' Your detailed guidance has resolved my issue.

    Thanks again,
    Sher Ali Pakistan

  11. Hi,
    I've created a backup of my contacts via NOKIA PC SUITE, the information have saved in Excel-> contacts.csv
    But here the information is not in the format as i expected, it's not showing the number(phone number), instead of that it's showing some other values.
    Please help me to convert that into an excel file where i can see the contact name, and number as well.

  12. In csv file, I have an <> at the end of each row and | is used a delimiter

    I used | as delimiter in other but how I set so that <> is removed and after it, new row is displayed

    Can you please help ?

  13. while exporting data from excel(total 10 Columns) to csv how would i get 5th - 10th Column on the next line.

    Example
    Data in Excel
    SrNo Name Add Tel No
    1 ABC sdfd 1212

    Data Expected in CSV
    SrNo Name
    Add Tel No
    1 ABC
    sdfd 1212

    • Hell Avinash,

      Most likely you need a special macro to fulfill your task. Sorry, I can't help you with this.

  14. Excellent article, it got my conversion done first time.

    Thanks,
    Hugh

  15. Hello,

    I have an issue with csv file opening in the Excel format.

    When I double-click on the CSV file in Windows, Excel starts and then loads the file. The problem is that every record appears in a single column, ...instead of the Instead of the comma-separated values being in different columns.

    Any advise how to fix this and make the data in csv. file appear as Instead of the comma-separated values being in different columns as soon as the csv file is opened by excel?

    Thank you

    • Text to Columns

  16. MANYYYYY THANKKKKKKKKSSSSSSSSSSSSSSSSSSSSSSS

  17. Thank you, thank you, thank you sooooooooo much!!! You've saved me tons of workload!!!! Thank you!!!

  18. tank u so much

  19. How to automatically load a csv file where all fields are treated as "Text and where "separator=," is done by default?

    • Sorry, I cannot help with this. Automatic loading may be done via a VBA script only.

      • sep=, on fist csv line

      • I have CSV file which have Chinese character when we saved this CSV file in excel by "save as" option. Chinese char had been change.Could you please let me a solution for this?

  20. Dear Admin,

    Thank you so much for this useful information.
    Your detailed guidance has resolved my issue.

    Thanks again,
    Trevor

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)