May
1

Convert CSV to Excel: how to import CSV files into Excel spreadsheets

In this article you will find 3 quick ways to convert CSV to Excel. You will also learn how to import multiple CSV files in a single workbook and how to handle the cases when some data from a CSV file isn't correctly displayed in an Excel sheet.

Last week we started to investigate the specificities of the comma-separated values format (CSV) and different ways of converting Excel files to CSV. Today, we are going to do the opposite - import CSV to Excel.

This article will show you how to open CSV in Excel and import multiple CSV files at a time. We will also red-flag possible pitfalls along the way and suggest the most effective solutions.

How to convert CSV to Excel

If you need to transfer some data from your company's database to an Excel spreadsheet, exporting the database to a CSV file and then importing the CSV file to Excel is the first option that comes to mind.

There exist 3 ways of converting CSV to Excel: you can open a .csv file directly in Excel, double-click on the file in Windows Explorer, or import CSV into Excel as external data. Below I will provide the detailed guidance on these methods and point out the strengths and limitations of each:

How to open CSV file in Excel

If you created a CSV file in another program, you can still open it as an Excel workbook in the usual way by using the Open command.

Note. Opening the CSV file in Excel does not change the file format. In other words, this won't convert your CSV file to Excel's .xls or .xlsx , the file will retain the original .csv (or .txt) extension.
  1. With your Microsoft Excel open, head over to the File tab and click Open.
  2. The Open dialog box appears and you select Text Files (*.prn,*.txt,*.csv) from the drop-down list in the lower right-hand corner.

    Opening a CSV file in Excel

  3. Browse for the CSV file and open it as usual by double-click.

    If you are opening a .csv file, Excel will open it straight away and display the data in a new workbook.

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

    Note. When Microsoft Excel opens a .csv file, it uses your default data format settings to understand how exactly to import each column of the text data. If your data contains one of the below characters or entries, then use the Import Text Wizard instead:

    • different delimiters are used in your csv file;
    • the csv file contains different date formats;
    • you convert numbers with leading zeroes and want to preserve leading zeroes in an Excel sheet;
    • you want to see a preview of how your CSV data will be imported into Excel;
    • you are looking for more flexibility in general.

To get Excel to launch the Import Text Wizard, you can either change the file extension from .csv to .txt before opening the file, or import CSV to Excel as explained further.

How to open a CSV file using Windows Explorer

This is the quickest way to open CSV in Excel. In Windows Explorer, just double click a .csv file and have it open as a new workbook.

However, this method works only if you have Microsoft Excel set as the default program for .csv files. If so, then you will see the familiar green Excel icon Excel icon displayed next to the .csv file's name in Windows Explorer.

If Excel is not the default program, you can set it as such in this way:

  1. Right-click any .csv file in Windows Explorer, and select Open with... > Choose default program from the context menu.
  2. Click Excel (desktop) under Recommended Programs, make sure the "Always use the selected program to open this kind of file" is selected and click OK.

    Setting Microsoft Excel as the default program to open .csv files.

How to import CSV to Excel

Using this method you can import data from a csv file into the existing or a new Excel worksheet. Unlike the previous two methods, it does not simply open CSV in Excel but really converts a .csv file to .xlsx (in Excel 2016, 2013, 2010 or 2007) or .xls (in case of Excel 2003 and lower).

  1. With the needed spreadsheet open, click the cell where you want to import the data from a .csv or .txt file.
  2. On the Data tab, in the Get External Data group, click From Text.
    To convert a CSV file to excel, click From Text on the Data tab.
  3. Browse for the csv file you want to import, select it and click the Import button (or simply double click the .csv file).
    Double click the .csv file to run the Text Import Wizard.
  4. The Text Import Wizard will appear and you follow its steps.

    Before we move further, please have a quick look at the screenshot below that shows the original CSV file and the desired result in Excel. Hopefully, this will help you better understand why we choose this or that setting in the example that follows.
    Data to be converted from CSV to Excel

    • Step 1. Choose the file type and the row number to start importing the data. Typically, you choose Delimited and row 1, respectively. The preview window in the lower part of the wizard shows the first few entries of your CSV file.
      Choose the file type and the row number to start importing the data from CSV to Excel.
    • Step 2. In this step, you select a delimiter(s) and text qualifier.Delimiter is the character that separates values in your .csv file. If your CSV file uses some character other than available in the pre-defined list, select the Other check box and then type your custom character. In our example, we specify Tab and Comma since we want each product (separated by tabs) to start with a new row and the product details such as IDs and sales numbers (separated by commas) to be imported in separate cells.

      Text qualifier is the character that wraps the values in your text file. All text enclosed in the text qualifier symbols, e.g. "text1, text2" will be imported as one value, even if the text contains a delimiter you specified.In this example, we have chosen a comma as the delimiter and a quotation mark as the text qualifier. As the result, all of the numbers with a decimal separator (which is also a comma in our case!) will be imported in one cell, as you can see in the preview section below. If we did not specify the quotation mark as the text qualifier, then all the numbers with decimal separators would be imported into two adjacent cells.

      Sect the delimiter and text qualifier

    • Step 3. If you are happy with the Data preview, then click the Finish button.
    Tip. If more than one consecutive comma or other delimiter characters may occur in your CSV file, then check Treat consecutive delimiters as one check box to prevent empty cells.
  5. Choose the destination for the imported data, either an existing or a new worksheet, and click OK to finish importing your CSV file to Excel.
    Choose the destination and click OK to finish importing your CSV file to Excel.

    Tip. You can click Properties... to set some advanced options such as refresh, layout and formatting for the imported data.
    Click the Properties button to set advanced options for the imported data.
    Note. If your CSV file contains some numerical data or dates, Excel may not convert these values correctly. To change the format of the imported data, select the problematic column(s) in Excel, right click and choose Format cells from the context menu.

Convert CSV to Excel: problems and solutions

The CSV format has been around for over 30 years, but notwithstanding its long history it has never been officially documented. The name CSV (comma-separated values) implies the use of comma to separate data fields. But this is in theory. De facto, many so-called "CSV" files use other characters to separate data, such as:

  • Tabs - TSV files (tab-separated values)
  • Semicolon - SCSV files (semicolon separated values)

Some implementations of CSV files wrap data fields in single or double quotation marks, while others require a Unicode byte order mark (BOM), e.g. UTF-8, for correct Unicode interpretation.

This lack of standard spawns various problems you may be faced with when trying to convert an Excel file to CSV and especially when importing a .csv file to Excel. Let's investigate these issues beginning with the most common one.

CSV file is incorrectly displayed in Excel

Symptoms. You are trying to open a .csv file in Excel and all of the data is displayed in the first column.

Cause. The root of the problem is different separators set in your Windows regional and language settings and the csv file. In North America and some other countries, the default List Separator is a comma. While in European countries the comma (,) is reserved as the Decimal Symbol and the List Separator is set to semicolon (;).

Solutions. There are several possible solutions for this problem, you can quickly look through the recommendations below and choose the one most suited for your particular task.

  1. Specify the right separator directly in the CSV file. Open the CSV file with any text editor (even the usual Notepad will do) and add the below text in the first line. Note, it should be a separate line before any data:
    • To separate with comma: sep=,
    • To separate with semicolon: sep=;

    As you can guess, you can set any other custom separator in the same way, simply type it after the equality sign.

  2. Choose the needed separator in Excel. In Excel 2016, 2013 or 2010, go to the Data tab > Data Tools group > Text To Columns.
    Run the Text To Columns wizard in Excel.

    The Convert Text to Column wizard, choose the Delimited file type ad click Next. Then select the needed separator in the next step and click Finish.
    Select the needed separator for the CSV file.

  3. Change the .csv extension to .txt. Opening a .txt file in Excel will start the Text Import Wizard and you can choose any delimiter as discussed in importing a csv file to Excel.
  4. Open a semicolon delimited CSV file with VBA.

    Here is a sample VBA code to open a semicolon delimited CSV file in Excel. Though the code was written a few years ago for older Excel versions (2000 and 2003), if you are pretty comfortable with VBA, you won't have problems with updating it or modifying for comma delimited CSV files.

    Note. All of the solutions above change the separator for a given CSV file only. If you'd rather change the default separator once and for all, proceed with solution 5.
  5. Change separators in Region and Language settings.

    Click the Start button and open the Control Panel, then click Region and Language > Additional Settings. The customize Format dialog window will open and you choose a dot (.) as the Decimal symbol and a comma (,) as the List separator.

    Change separators in Windows Region and Language settings.

    Click OK twice to close two dialog windows and you are finished. From now Microsoft Excel will open and display all CSV (comma separated) files correctly.

    Note. Setting new decimal and list separators in Control Panel changes the default separator characters for all programs on your computer, not only Microsoft Excel.

Leading zeros are lost when opening a CSV file in Excel

Symptom. Your .csv file contains values with leading zeros and those zeros are lost when you open the CSV file in Excel.

Cause. By default, Microsoft Excel displays SCV files in the General format, which cuts off leading zeros.

Solution. Instead of opening a .csv file in Excel, run the Text Import Wizard as explained in Importing CSV to Excel in order to convert CSV to Excel.

In step 3 of the wizard, select the column(s) that contains values with leading zeros and change the column's format to Text. This will covert your CSV file to Excel keeping all zeros in place.
Convert  a CSV file to Excel keeping all leading zeros intact.

Excel converts some values to dates when opening a CSV file

Symptom. Some values in your .csv file look like dates and Excel automatically converts such values from text to a date.

Cause. As mentioned above, Excel opens CSV files in the General format and converts values that happen to resemble dates from text to a date. For example, if you are opening a .CSV file containing user logins, the entries similar to apr23 will be converted to dates in Excel.

Solution. Convert CSV to an Excel file by running the Text Import Wizard (Data tab > From Text). In step 3, select the column(s) with records that look enough like dates and change the column's format to Text.

Change the column's format to Text

If you are looking for the opposite, i.e. you want the values in a certain column to be converted to dates in Excel, then set the format of that column to Date and choose the desired date format from the drop-down list.

How to import multiple csv files into Excel

As you probably know, Microsoft Excel allows opening several CSV files using the Open command.

  1. You click File > Open and select Text Files (*.prn, *.txt, *.csv) from the drop down list in the lower right-hand corner.
  2. 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 in-between will get selected.

    To select multiple non-adjacent files, hold down the Ctrl and click each .csv file you want to open.

  3. With multiple CSV files selected, click the Open button.
    Opening multiple csv files into Excel

    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 Excel workbook. In practice, switching back and forth between several Excel files might be quite inconvenient and burdensome.

Now let me show you how you can import multiple CSV files into one Excel workbook, placing each .csv file on a separate sheet or consolidating all the files in a single spreadsheet.

Importing multiple CSV files in a single Excel workbook

To import several .csv files into one workbook, we are going to use the Consolidate Worksheets Wizard. This add-in works with all versions of Excel 2016, 2013, 2010, 2007 and 2003. The evaluation version is also available and you can download it right now to follow this example more closely. The installation will take you just a couple of minutes.

  1. Go to the Ablebits Data tab on the Excel ribbon and click the Consolidate Worksheets icon.
    Click the Consolidate Worksheets icon.
  2. Select the CSV files you want to import in Excel. If you have the files open, the wizard will display their names in the dialog window. Otherwise, click the Add files... button in the lower right-hand corner.
    Select the CSV files you want to import in Excel.

    Select the files as you usually do in Windows Explorer (hold Shift to select contiguous files or Ctrl to select non-contiguous files). The result will look similar to what you see the screenshot below, and you click Next.

    Select the CSV files you want to import to Excel.

  3. Choose how exactly you want to import the selected CSV files to Excel. As you can see in the screenshot below, you have several choices. Typically, you click the last open that imports all of the selected CSV files into a single workbook, each file on a separate sheet.
    Choose how exactly you want to import the selected CSV files to Excel.

    Tip. If you are unsure which option to choose, click the i icon right to the option in question and you will see a mockup of the imported files. For example, to consolidate data from multiple CSV files in a single worksheet, choose the second option - "Copy data from the selected ranges to one worksheet".

    Consolidate data from multiple CSV files in a single Excel worksheet.

  4. In the next step, you decide whether you want to import all the data or values only. When importing .csv files, you typically go ahead with the default setting "Paste all" and simply click Next.
    Choose how you want to import the data in Excel.
  5. The last thing for you to do is to choose the destination for the imported CSV files. And again, you proceed with the default setting since the New workbook is the only available option.
    Choose the destination for the imported CSV files.
  6. Click Finish, wait for a few seconds and get the desired result - multiple CSV files imported into one Excel workbook. Fast and painless, isn't it?

As you understand, this was just one of the many possible applications of the Consolidate Worksheets Wizard. You can find more about this Excel add-in on its homepage.

Hopefully, now you are able to convert any CSV files to Excel with ease. If you have any questions or have run into any issues, don't hesitate to drop me a comment. And thanks for your patience everyone who has read this long tutorial to the end! : )

50 Responses to "Convert CSV to Excel: how to import CSV files into Excel spreadsheets"

  1. Trevor says:

    Dear Admin,

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

    Thanks again,
    Trevor

  2. t alt says:

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

  3. mohamad says:

    tank u so much

  4. Elias says:

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

  5. Anonymous says:

    MANYYYYY THANKKKKKKKKSSSSSSSSSSSSSSSSSSSSSSS

  6. Anonymous says:

    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

  7. Hugh says:

    Excellent article, it got my conversion done first time.

    Thanks,
    Hugh

  8. avinash says:

    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

  9. Vaibhav jain says:

    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 ?

  10. chandrasekhar says:

    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.

  11. Sher Ali says:

    Dear Admin,

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

    Thanks again,
    Sher Ali Pakistan

  12. venkat says:

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

  13. Diana says:

    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 !

  14. sajjad says:

    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 ?

  15. Nitin says:

    Awesome work, Thanks a ton

  16. DELFONICS says:

    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.

  17. David says:

    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+...)

  18. srihari says:

    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

  19. jabir says:

    Dear sir\ madam
    I need csv file

  20. Jitesh Mishra says:

    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.

  21. Mario says:

    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.

  22. sree says:

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

  23. ron says:

    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

  24. Amit says:

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

  25. ashkan says:

    great article
    thanks very much

  26. DaniOR says:

    Thank you so much, It was great help!

  27. zeeshan says:

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

  28. guy from that thing says:

    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?

  29. Tadeu dos Santos Pereira says:

    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.

  30. Mahlatse says:

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

  31. keerti says:

    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

  32. Zoya Sikdher says:

    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.

  33. mahadevan says:

    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.

  34. Ashwini says:

    It was Really helpfull.. Thanks a lot

  35. rohit says:

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

  36. Jo says:

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

  37. Juan Mora| says:

    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?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard