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.
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.
When creating a Power Query connection, you can pick the delimiter in the Preview dialog window:
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:
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.
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.
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.
- Underneath the data preview, click Transform Data.
- In the Power Query Editor, select the column where you want to retain preceding zeros, and click Data Type > Text.
- Define data types for other columns if needed.
- 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:
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.
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:
- Select the column of dates.
- Press Ctrl + 1 to open the Format Cells dialog box.
- On the Number tab, choose Date under Category.
- Under Type, pick the desired formatting.
- Click OK.
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.
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:
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.
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!