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

96 comments

  1. Trying to convert a CSV file to Excel and it only converts the last total line, but not the rest of the data. What do I need to do? I cant work with the CSV file since it is only an export button I can get to in our program used from another company.

    • Kim:
      It's not clear to me what you're trying to do.
      I think you're either trying to open a CSV file in Excel or you are trying to export a data file from another program into Excel.
      Are you importing a CSV file into Excel from another program or do you have the file itself?
      What do you mean it converts the last total line? Is it the last line in the file? Maybe you need to select all the data and then export it?

  2. Thank you so much!

  3. One solution is to change your regional settings for the "List separator" attribute to the character you want Excel to default to using, e.g. a comma (,)

    This can be changed in:

    Control Panel / Region / Additional Settings / List separator:

  4. I want to upload a csv file into excel without separating the data. Instead I want to use a macro to sort the data by criteria onto different worksheets first because the different records have different fixed width. I've put an example below. I would want to put HeaderRecord on one sheet
    all RecordType1 on one sheet
    all RecordType2 on one sheet
    all RecordType3 on one sheet
    TrailerRecord on one sheet

    After I do that, I already have the formula to text to column based on each record types fixed column width counts.

    Every ID will have RecordType1 and can have additional rows of RecordType2 and RecordType3. Pretend there are 500 Unique IDs (recordtype1), 900 Records total (400additional rows of data for RecordType2 & 3)

    Example:
    HeaderRecord DateDateHourDate L
    RecordType1 First Last ID01 TitleDate1Date2 zerofilldate3
    RecordType2 ID01 Date1Date2 YesNo Spacefill
    RecordType3 ID01 DataInfoDate1 ZerofillDate
    RecordType1 First Last ID02 TitleDate1Date2 zerofilldate3
    RecordType2 ID02 Date1Date2 YesNo Spacefill
    RecordType3 ID01 DataInfoDate1 ZerofillDate
    RecordType1 First Last ID03 TitleDate1Date2 zerofilldate3
    RecordType2 ID03 Date1Date2 YesNo Spacefill
    RecordType3 ID03 DataInfoDate1 ZerofillDate
    TrailerRecord Count

  5. Thank you.

  6. Thanks Your Present

  7. Dear,

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

    Best regards

  8. Excellent article which explains each step in detail. I have been battling to figure it all out with trial and error and this proves some of my findings!

    Thank you for the effort in documenting and posting!

  9. your are amazing girl thanks

  10. Thank you very much for the very valuable and useful information.

  11. Thank you. very useful info.

  12. Hi, can someone advise when I convert a csv tect file to excel, the number is
    1.7122E+12 as number, I tried to converted to normal 1712200000000 with text " signed ! in front, thus I converted to number and turned out the same 1.7122E+12 as original one.

    Kindly help.

    Thanks,
    May,

  13. This is very helpful..can i have a soft copy..Thank you

  14. Hey, I consider this really helpfull and with the "old version" of excel 2016 i used to do the data import. However, excel has got an update and know when i go to Data > From text/CSV the Text import Wizard does not show up, in his replace there's something called Power Query. Now, with that thing I have problems on setting the numbers as decimals when with the Text Import Wizard i had no problem. Any idea on how to help me with that?

    • Just found the solution. Regional setting. I changed it to United States and it worked.

  15. Many thanks for this very helpful tutorial! Was very happy to see that this was so easy to do :-)

  16. Hello sir
    we are trying to convert Excel file to CSV file how can i convert for GST return

  17. It was Really helpfull.. Thanks a lot

  18. we have csv file with more than 1048576 Rows, but trying to open in xl format, we are not egtting the data more than the above rows.

  19. Thank you for this useful and resourceful share. I specially enjoyed your descriptions. They are right on slot and bound to help save time!

    So far I have been using Websites like zamzar and Convert.world for the conversions and they are great too. My best is Convert.world. It has such a great interface and does the job in No Time.

    • Convert.world is sham. I tried it couple of times..each time having to wait for 1 or more minute and end result of file that looked exactly like the source file. What a waste of time?! and potentially unsafe website with horrible pop-ups and ads.

  20. im importing csv file to excel sheet but it is not properly separating it. my csv file contains text, numeric value also date plzzz help me out

    • Hi, Keerti,
      please, try to follow the steps once again. If it doesn't help, try our add-in. If neither way helps, make the screenshots of the results and send them together with your .csv file to support@ablebits.com. Make sure to provide a link to this article and your comment number. We will get back in touch.

  21. Thank you very much, this was really helpful and saved me a lot of time. Keep up the good work.

  22. Dear all,

    I have one file created as .csv, so i just need to correct some information, and save it again. I have create a Macro that open the file as a normal excel file, and i'm saving the information as Normal way. But the problem is when i save the file again on the normal way, it's not consider the fields that were corrected as a TXT value, and when a save "01", in fact just save "1". Has any manner to save the information in text format and the csv format accept once i save it ?

    Thanks in advanced.

  23. Hi - conditional formatting was working great. I pulled in data via a CSV feed, data types / conditional formatting rules all worked perfectly. Then the publisher changed the format of the CSV URL -- two notable changes I have to reverse the row order and then take the first row as the header row (by default the feed now puts the header row at the bottom). The columns are still the same, but with the rows reversed the conditional formatting will not take. I apply formatting the appropriate data type to the column (e.g., format a number), and reapply the conditional formatting to the column still no luck. Any ideas?

  24. Hello.,

    Iam unable to modify a .csv file (I mean like formatting and highlighting etc.,)Is there any alternative that i can modify a .csv file and save it in the same .csv format.

    Your concern is highly appreciated...

    Thank You...

  25. Thank you so much, It was great help!

  26. great article
    thanks very much

  27. Hi,
    your blogs are so informative . Keep it up. !
    I am facing a problem while copying word database to excel. All the columns in word table are getting arranged in a single column in excel in splitted manner .
    Please help me to tacle this problem.
    Thank You ..!

  28. this discussion is perfect for learning, thank-you. now, can i ask about a variation?
    1. i downloand a supposed csv file from a website, but it saves as a .xls and opens in excel 2013 in 'compatibility mode'. i can't use the 'get data' capability as it saves as .xls, and i can't use it as is, it needs to be imported into a workbook already saved as an .xlsm.
    2. did i miss anything in your presentation? or is the only way to do this is try and record a macro that opens the .xls, saves it to .xlsm and then tries to add it to the larger .xlsm as a simple worksheet?
    3. i guess i see all the options in 'get data', except the one that says 'get data' from an older format excel file.

    thanks,
    ron

    • Hello, Ron,

      If you want to work with the file in Excel 2013, it's necessary to save it as .xlsm.

  29. Hi.is it possible to refresh data after the import.If it works lemme know!

  30. Is it possible to open a file with a different file extension as a csv type with Excel 2010. We have a process that renames files from a.csv to a.csv.1 and a.csv.2 etc. I need to have excel open these as csv type.

    • Hello, Mario,

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

  31. 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.

  32. Dear sir\ madam
    I need csv file

  33. 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

  34. 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.

  35. 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.

  36. Awesome work, Thanks a ton

  37. 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 ?

  38. 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.

  39. 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 :)

  40. Dear Admin,

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

    Thanks again,
    Sher Ali Pakistan

  41. 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.

  42. 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 ?

  43. 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.

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

    Thanks,
    Hugh

  45. 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

  46. MANYYYYY THANKKKKKKKKSSSSSSSSSSSSSSSSSSSSSSS

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

  48. tank u so much

  49. 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?

  50. 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 :)