Converting CSV to Excel: problems and solutions

CSV not opening correctly in Excel? The tutorial investigates typical issues and provides the most effective solutions.

The CSV format is commonly used for importing/exporting data between various spreadsheet programs. The name CSV (comma separated values) implies the use of comma to separate data fields. But that is in theory. In practice, many so-called CSV files separate data using other characters such as semicolon or tabs. Some implementations enclose data fields in single or double quotation marks, while others require a Unicode byte order mark (BOM), for example UTF-8, for correct Unicode interpretation. The lack of standard spawns various problems with CSV to Excel conversions.

CSV file opens in one column in Excel

Symptoms. When opening a csv file in Excel, all data appears in a single column.

Cause. To split data in columns, Excel uses the list separator set in your Windows Regional settings. This may be either a comma (in North America and some other countries) or semicolon (in European countries). When the delimiter used in a particular .csv file differs from the default separator, that file opens in one column.

Solutions. There are several possible solutions for this case including VBA macros or a global change in the Windows settings. We'll show how to quickly fix the issue without changing the default list separator on your computer, so none of your applications will be affected.

Change delimiter in CSV file

For Excel to be able to read CSV with a different separator, you can define the delimiter directly in that file. To have it done, open the file with any text editor (Notepad will do fine) and add the below text in the first line. Note, it should be a separate line before any other data:

  • To separate with comma: sep=,
  • To separate with semicolon: sep=;

In the same manner, you can set any other custom separator - simply type it after the equality sign.
Specify the separator in the csv file.

With an appropriate separator defined, you can now open the file in the usual way, from Excel itself or from Windows Explorer.

Specify delimiter when importing CSV file to Excel

Instead of opening a csv file in Excel, import it using either Text Import Wizard (in all versions) or Power Query (in Excel 365 - 2016).

The Text Import Wizard (Data tab > From Text) provides a few choices for delimiters in step 2. Generally, you'd choose:

  • Comma for comma separated values files
  • Tab for text files
  • Semicolon for semicolon separated values files

If you are not sure what separator your data contains, try different delimiters and see which one works right in the Data preview.
Choose the delimiter when importing CSV file.

When creating a Power Query connection, you can pick the delimiter in the Preview dialog window:
Choose the delimiter when creating a Power Query connection.

For the detailed step-by-step instructions, please see the above-linked examples.

Split cells using Text to Columns feature

In case your data is already transferred to Excel, you can separate it into different columns by using the Text to Columns feature. Essentially, it works like the Text Import Wizard: you select a delimiter and Data preview reflects the changes on the fly:
Select a delimiter to split text values into different columns.

For full details, please see How to split cells in Excel.

How to keep leading zeros in Excel CSV

Symptoms. Some values in your csv file contain leading zeros. When the file is opened in Excel, the preceding zeros are lost.

Cause. By default, Microsoft Excel converts csv files to the General format that strips off leading zeros.

Solution. Instead of opening, import your CSV to Excel and select the Text format for problematic columns.

Using Text Import Wizard

To start Import Text Wizard automatically, change the file extension from .csv to .txt, and then open the text file from Excel. Or enable the From Text (Legacy) feature, and start importing CSV to Excel.

In step 3 of the wizard, select the column containing values with leading zeros and change its format to Text. This will import the values as text strings keeping all leading zeros in place.
Keep leading zeros in Excel CSV.

Using Power Query

If you prefer importing a csv file to Excel by connecting to it, there are two ways to keep leading zeros.

Method 1: Import all data in Text format

In the preview dialog box, under Data Type Detection, choose Do not detect data types. The contents of your csv file will be loaded into Excel as text, and all leading zeros will be retained.
To keep leading zeros, load the CSV file to Excel as text.

Note. This method works fine if your file contains only text data. If there are different kinds of values, then use method 2 to define an appropriate format for each column individually.

Method 2: Set format for each column

In situation when your csv file contains various data types such as text, numbers, currencies, dates and times, you can explicitly indicate which format should be used for each particular column.

  1. Underneath the data preview, click Transform Data.
  2. In the Power Query Editor, select the column where you want to retain preceding zeros, and click Data Type > Text.
    To retain preceding zeros, choose Text for Data Type.

  3. Define data types for other columns if needed.
  4. When done with editing, on the Home tab, in the Close group, click either:
    • Close & Load - this will load the results to a new sheet in the current workbook.
    • Close & Load To… - this will let you decide where to load the results.

Tip. These methods can also prevent other manipulations with your data that Excel tries to perform automatically. For example, if the imported data starts with "=", Excel will try to calculate it. By applying the Text format, you indicate that the values are strings, not formulas.

How to fix CSV date format problems in Excel

Symptoms. After converting CSV to Excel, dates are formatted incorrectly, days and months are swapped, some dates are changed to text, and some text values are auto formatted as dates.

Cause. In your csv file, dates are written in the format different from the default date format set in your operating system, because of which Excel fails to interpret the dates correctly.

Solution. Depending on exactly what problem you are faced with, try out one of the following solutions.

Days and months are mixed up

When the date formats in the Windows Regional settings and csv file are different, there is no way for Excel to determine that mm/dd/yy dates it is looking for are stored in the dd/mm/yy format in that particular file. As a result, the day and month units are reversed: Jan-3 becomes Mar-1, Jan-10 becomes Oct-1, and so on. Moreover, dates after Jan-12 are converted to text strings because there exist no 13th, 14th, etc. months.

For the dates to be imported correctly, run Text Import Wizard, and pick the appropriated Date format in step 3:
Pick the appropriated <b>Date</b> format.

Some values are converted to dates

Microsoft Excel is designed to make it easier to enter various kinds of values. Therefore, if Excel believes that a given value represents a date, it is auto formatted as a date. For example, the text string apr23 looks very much like April 23, and 11/3 resembles November 3, so both values are converted to dates.

To stop Excel from changing text values to dates, use the already familiar approach: convert CSV to Excel by importing it. In step 3 of the Text Import Wizard, select the problematic column and change its format to Text.
Stop text values from being converted to dates.

Dates are formatted incorrectly

When a csv file is opened in Excel, the dates are normally displayed in the default format. For example, in your original file, you may have 7-May-21 or 05/07/21, while in Excel it appears as 5/7/2021.

To display dates in the desired format, make use of the Format Cells feature:

  1. Select the column of dates.
  2. Press Ctrl + 1 to open the Format Cells dialog box.
  3. On the Number tab, choose Date under Category.
  4. Under Type, pick the desired formatting.
  5. Click OK.
    Changing date format in Excel

If none of the preset formats is right for you, then you can create your own one as explained in How to make custom date format in Excel.

Prevent Excel from converting numbers to scientific notation

Symptoms. After converting CSV to Excel, long numbers are formatted as scientific notation, e.g. 1234578900 appears as 1.23E+09.

Cause. In Microsoft Excel, numbers are limited to 15 digits of precision. If numbers in your csv file exceed that limit, Excel automatically converts them to scientific notation as a way to conform to that limit. If a number contains more than 15 significant digits, all "extra" digits in the end are changed to zeros.

Solution. Import long numbers as text or change the Number format directly in Excel.

Import long numbers as text

To accurately transfer big numbers from CSV to Excel, run the Text Import Wizard and set the format of the target column(s) to Text.
Import CSV to Excel without converting numbers to scientific notation.

This is the only real solution to accurately import numeric strings without losing data, i.e. without replacing the 16th and subsequent digits with 0's or removing leading zeros. It works great for entries such as product ids, account numbers, bar codes, and the like.

However, if your values are numbers, not strings, it is not the best method as you won't be able to do any math on the resulting text values.

This method will also help you prevent other unwanted automatic data formatting when converting a CSV file.

Change Number format in Excel

If your data is already in Excel, you can change the format from General to either Text or Number like shown below:
Changing format from General to Number

Note. This method won't restore the deleted preceding zeros or digits after the 15th position that were replaced with zeros.

For more information, please see How to format cells in Excel.

Make the column wider

In a simplest case, when a number contains fewer than 15 digits, it's sufficient to make a column a little wider to get the numbers displayed normally.
To get the numbers displayed normally, resize the column.

For more details, please see How to resize and auto fit columns in Excel.

That's how to fix the most common problems that may occur with CSV to Excel conversions. Thank you for reading and see you next week!

Table of contents

22 comments

  1. Hello,

    Could someone share, please what setting is needed to make sure that a csv file would always open with the data already split/arranged in columns?

    I have already checked and tested excel settings, region settings. It obviously works applying the instructions above for each file at a time. But I know there is a way to default all csv files to open with the data already moved to columns. I am using Office 365. Could it be that it was a setting in a previous Office version?

    Thanks everyone and looking forward to hearing your thoughts!

    Corina

  2. Hello

    My problem when exporting from CSV is that the last column will probably exceed the maximum number of characters in an Excel cell. The information in that column is separated initially by commas, so Excel "helpfully" puts the extra content in a row below and then splits it using the commas. Can I make Excel add the additional characters in consecutive columns without applying the delimiter interpretation?

    Thanks!

  3. I am trying to import CSV file with comma as a separator.
    I am using Data Tab-> From Text.
    What I get is only first row is separated into columns, the rest of the rows in the file are in the first column, without separation.
    This is funny because all rows have the same separator.
    Is it a bug ? Or I am doing something wrong ?

  4. I have data saved to n Excel as CSV and the data total is x amount, but when I import data in Excel as CSV the data total does not match with the CSV total.

    Kindly guide.

    Regards/Irfan

  5. Your advice is terrible. The newer versions of Excel simply don't do this anymore. What junk

  6. When i download csv file, numbers are in text format. When i try to transform data type to number, the whole column turns into error.
    2nd way, If i change csv into excel and download the data, numbers are in text format and formatting column into number format doesnt do anything.
    downloaded numbers in text format use dot as decimal, and thousand separator are in comma. In my version of excel, decimal is comma, thousand separator could be a space or nothing.
    I am going crazy trying to fix this for past 3 months.

      • I am having the same problem and dont understand your advice

        • Hi!
          The delimiters in your file must match the delimiters that Excel uses. With a text editor, change the comma to nothing, then change the dot to a comma.

  7. When I convert my bank statement the amount is not usable in excel. When highlighting more that one cell to get the combined amount it give the number of cells and not a total amount.

  8. I have had problems with CSV text data that contains line breaks. For example, exported spread sheet to CSV with heading cells that had line breaks. Ideally there should be an export/import CSV type variant that decodes escape chars, so it understand escaped chars like \n; \r in a quoted string. I have not seen any easy way to do this in the old version of Excel I use and not expert on latest versions of excel. I usually have to run a custom macro to convert text columns for that kind of text data.

    Is this any better in latest versions of Excel?

    I have seen CSV exports that just put a line break in the middle of the text data item. This CSV data is difficult to import. Some CSV importers (not necessarily spreadsheet software) do not even respect quoted strings.

    I had not heard of "sep=x" in CSV what a shame there is no way to put a header line that indicates CSV column data types rather than having to use import wizard for every to you import a CSV file or letting Excel best guess the CSV column data - that is typical lack of meeting power user needs.

  9. The trick with sep=; even works on my old Excel 2003. Many thanks for the tip!

  10. I came here due to csv issue :)

    As a developer that later has to work with those text formats I urge you. Just stop using csv as exports, if source data can contain a delimiter value in user filled text It will most certainly at one point will. XML based files are safer.

    • You'll be surprised, how often appear in user text... =)
      So even with xml you're not totally save.

      • Sorry, html deleted the characters, which was exactly my point =)
        I was talking about "lower than" (<) and "greater than" (>);

      • * "greater than" and "lower than" were excluded from my previous comment, which was my point exactly =)

  11. The worst problem for me when converting CSV files to Excel (or just opening one from File Explorer) is that if I have some data that starts with "=" then Excel automatically tries to calculate it. It does not matter if I quote strings - this can break things unexpectedly based on data originating from somewhere and in some cases could represent a security issue.

    Anyone knows to to tell Excel to load data from CSV only as data (meaning if the data is not date or numeric, it should assumed to be a string)?

    • Hi Dror,

      Import your CSV to Excel by using Text Import Wizard or Power Query and choose the Text format for all the columns that contain data starting with "=" . For more information, please see the section "How to keep leading zeros in Excel CSV" - these methods will also fix your issue.

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