Comments on: How to convert (open or import) CSV file to Excel

Generally, there are two ways to convert a CSV file to Excel: by opening it in Excel 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. Continue reading →

Comments page 2. Total comments: 97

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

  2. That was precious for me, thanks.

  3. thank you
    that was great
    it did the job

  4. thank u so much

  5. Thank you so much sir!!

    This article really helped me.

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

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

  7. Thank you so much!

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

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

  10. Thank you.

  11. Thanks Your Present

  12. Dear,

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

    Best regards

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

  14. your are amazing girl thanks

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

  16. Thank you. very useful info.

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

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

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

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

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

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

  22. It was Really helpfull.. Thanks a lot

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

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

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

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

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

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

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

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

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

  31. great article
    thanks very much

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

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

    1. Hello, Ron,

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

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

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

    1. Hello, Mario,

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

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

  37. Dear sir\ madam
    I need csv file

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

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

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

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

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

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

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

  41. Awesome work, Thanks a ton

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

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

    1. Hello, Diana,

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

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

  45. Dear Admin,

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

    Thanks again,
    Sher Ali Pakistan

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

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

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

    1. Hell Avinash,

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

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

    Thanks,
    Hugh

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

    1. Text to Columns

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