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.
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:
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.
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.
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.
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 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:
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).
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.
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.
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:
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.
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.
As you can guess, you can set any other custom separator in the same way, simply type it after the equality sign.
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.
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.
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.
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.
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.
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.
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.
As you probably know, Microsoft Excel allows opening several CSV files using the Open command.
To select multiple non-adjacent files, hold down the Ctrl and click each .csv file you want to open.
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.
To quickly import several .csv files into one workbook, we are going to use the Copy Sheets tool included with our Ultimate Suite for Excel. This add-in works with all versions of Excel 2019, 2016, 2013, and 2010.
The evaluation version is available for download, so you can follow the steps more closely. After installation, the Ablebits Data tab will be added to your Excel ribbon, and you will find the Copy Sheets button on it.
Importing will take you 3 minutes at the most, a minute per step :)
Allow the tool a few seconds and get the desired result - multiple CSV files imported into one Excel workbook. Fast and painless, isn't it?
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! : )
91 responses to "Convert CSV to Excel: how to import CSV files into Excel spreadsheets"
Dear Admin,
Thank you so much for this useful information.
Your detailed guidance has resolved my issue.
Thanks again,
Trevor
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?
tank u so much
Thank you, thank you, thank you sooooooooo much!!! You've saved me tons of workload!!!! Thank you!!!
MANYYYYY THANKKKKKKKKSSSSSSSSSSSSSSSSSSSSSSS
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
Excellent article, it got my conversion done first time.
Thanks,
Hugh
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.
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 ?
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.
Dear Admin,
Thank you so much for this useful information' Your detailed guidance has resolved my issue.
Thanks again,
Sher Ali Pakistan
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 :)
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.
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 ?
Awesome work, Thanks a ton
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.
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.
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
Dear sir\ madam
I need csv file
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.
Hello, Jitesh,
Sorry, we haven't been able to reproduce the issue. Please send us your csv file for test to support@ablebits.com.
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.
Hi.is it possible to refresh data after the import.If it works lemme know!
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.
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 ..!
great article
thanks very much
Thank you so much, It was great help!
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...
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?
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.
Thank you very much, this was really helpful and saved me a lot of time. Keep up the good work.
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.
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.
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.
It was Really helpfull.. Thanks a lot
Hello sir
we are trying to convert Excel file to CSV file how can i convert for GST return
Many thanks for this very helpful tutorial! Was very happy to see that this was so easy to do :-)
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.
This is very helpful..can i have a soft copy..Thank you
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,
Thank you. very useful info.
Thank you very much for the very valuable and useful information.
your are amazing girl thanks
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!
Dear,
Thank you so much for this useful information.
Your detailed guidance has resolved my issue.
Best regards
Thanks Your Present
Thank you.
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
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:
Thank you so much!
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?
Thank you so much sir!!
This article really helped me.
thank u so much
thank you
that was great
it did the job
That was precious for me, thanks.
Thank you for the explanation, it is very helpful. I have one problem tough, I cannot add up the amounts with auto sum. Is there another way to sum up the amounts?
Thank you
A.Reinbrech
hi,
i have a notepad file and i am able to import the data in excel.
now, this notepad file contains a trigger word "end" at several places.
while importing, can i have some option in excel which will create a new sheet when this particular trigger word is found.
Спасибо, Светлана! Очень помогли!
When I import a csv file, I see in the preview the comma as decimal separator, as it should be, so all ok. But when I click finish, the result is terrible, instead of 5,458170215980170 it gives 5.458.170.215.980.170, so with thousands separator. How to fix this?
Thanks in advance.
I would like to generate http:// and file:// links in my CSV files and have them active links when imported in to Excel. Such as a script that builds a .csv list of files in a directory structure with information in other columns that can be sorted or filtered, allowing quick access to these files from the spreadsheet.
Many thanks in advance.
Thank you.
When import csv into excell how to excel in currency excel table (HRK) after number?
Of course - Column data format: General. Of course I can do this cell by cell, but it takes a long time and creates a possibility of error.
Thank you in advance, Krešimir
Extremely helpful stuff!! Now, how do I get the
"Consolidate Worksheets" add-on?? It wasn't in
the list of "Get Add-ins".
Hi Brian,
Consolidate Worksheets is our own add-in and it's included in our Ultimate Suite for Excel. You can find more info and download a trial version from this page:
https://www.ablebits.com/excel-suite/consolidate-data.php
Dear Sirs/Madam,
I am trying to upload csv file but it comes to our data system. But the error happen.
It say "fist line must be header".
Could you please help to to overcome it?
Thanks
fantastic
Hi, need help.
I was working on CSV file, and did few formulas. but when I Saved, Closed, and then reopened the file, the formulas disappeared and the numbers was shown as a text. please advise how can i retrieve these formulas, as I do not want to repeat my work again.
thanks,
Hello Samer!
You see, CSV stands for comma-separted values. In other words, it is a simle text file thus no formulas can be entered there. If you want to save your formulas, please convers your .csv into .xlsx or .xls format.
I hope it’ll be helpful.
We are using Excel 2016. I have an odd situation, where a third party requires pipes (|) around text data rather than the usual double quotes. I can't seem to get Excel to allow me to choose the pipe as a text qualifier on importing. The default choices are limited to double quote, single quote, and {none}. How can I get the | added to the list?
Hello Scott!
If I understand your problem right, you can open your CSV file in any text editor, e.g. Notepad, and change any symbols in it with the Search and Replace instrument. For example, | to ; and the other way round.
I hope it will solve your problem.
Good Job, it work :)
These directions indicate that you can import CSV files into individual sheets. But the icon is now called "Consolidate Sheets" and it only does calculations on the multiple files, it doesn't actually import. Can this product import multiple CSV files into individual sheets on one workbook? The screen shots shown in the directions above do not seem to exist anymore.
Hi Jeffrey,
You are right, the instructions and screenshots were from an older version, sorry for this. We have just updated that part.
In the current version of our Ultimate Suite, there is another tool, named Copy Sheets, that can import individual worksheets into one workbook. It works with CSV files too.
Hello sir!!, How we can import csv file to wordpress while we measure particular unit by per pieace
Hello!
If you have a specific question about the operation of a function or formula Excel, I will try to answer it. Look for advice on WordPress on special sites.
Thank you soooooooooooooooo much