CSV to Excel: convert without losing leading zeros

A CSV file turns into an Excel workbook in a few clicks, but the values do not always arrive unchanged. Excel guesses a type for every value as it reads the file, so a code such as 12/2 can come out as the date 2-Dec. Microsoft says that guessing cannot be turned off, and a worksheet also stops at 1,048,576 rows whichever tool does the conversion.

Three ways to convert a CSV to Excel, and which one to pick

Pick the route by how your data breaks: double-click and save for plain data, import as text for codes and dates, a converter for several files. A CSV is a plain-text table with one row per line and values split by commas, and an .xlsx file is an Excel workbook. Three routes turn one into the other.

• Double-click the file, then use Save As and pick Excel Workbook (.xlsx). This is the shortest route, but Excel has already guessed a type for every value by the time the sheet appears.
• Import the file through Excel's own data import (the From Text/CSV button on the Data tab). This costs a few more steps and lets you set a column to Text before anything is read.
• Use a converter. This writes the .xlsx file without opening Excel by hand.

Opening a CSV is not converting it. The file stays a CSV until it is saved as .xlsx, so the save step is what produces the workbook.

A file with ZIP codes, product codes or dates belongs on the import route, and the next two sections explain why and how. A file with only plain numbers and text can take the double-click route.

Several files, or a quick conversion in the browser, call for the sakuttoAI CSV to Excel converter, which writes .xlsx output with column widths fitted to the content. Add one or more CSV files, check the row and column counts shown for each, and download the results. Open a column of codes afterwards and confirm that it reads as written.

Why Excel changes leading zeros and dates in a CSV

Excel reads every value as it opens the file, and anything shaped like a date becomes a date, so 12/2 turns into 2-Dec. Microsoft describes this as built-in behaviour meant to make entering dates easier. It also states that there is no way to turn it off, only ways to get around it, and the workaround it gives is to keep values as text.

Digit-only codes suffer the same way. A ZIP code or product code that starts with a zero is read as a number, and a number has no leading zero, so the zero is dropped.

ZIP codes that lost their zeros look like ordinary numbers, and nothing on screen reports the change. Check a code field right after opening a file, because the damage is invisible otherwise.

For a file with codes or dates, any route that lets Excel read the values first is the wrong one. Bring them in as text, as the next section shows. For repairs after the data has already changed, see CSV to Excel formatting fixes for zeros and dates.

How to import a CSV into Excel with Power Query

Import the file with the From Text/CSV button and set the code columns to Text, and Excel never gets the chance to guess their type. Power Query, the import tool behind that menu, shows a preview of the file before anything is loaded, which is the moment to fix the types.

Start from a blank workbook instead of double-clicking the file. On the Data tab, open the From Text/CSV import, choose the CSV file and click Import.

Check in the preview that the columns are split correctly. Then choose Transform Data (older versions label it Edit) to open the Power Query editor, and set every column that holds codes or dates to Text. Load the result into the sheet and use Save As with Excel Workbook (.xlsx) to finish.

The older route is the Text Import Wizard. Current versions of Excel hide it by default, so it has to be switched on on the Data page of the Excel options before it appears in the menu. The wizard asks for the format of each column in the same way, and Text is again the choice for codes.

Opening and viewing a CSV without changing it is a separate job. For that, see View CSV Files Online, or in Excel and Sheets.

Excel row limit: how many CSV rows fit on a sheet

A worksheet holds at most 1,048,576 rows by 16,384 columns, so a CSV longer than that cannot fit on one sheet in any tool, header row included. One of those rows holds the header, which leaves 1,048,575 rows for data. The ceiling belongs to the file format, so Excel, Power Query and a converter all stop at the same place.

A file that large has to be split into several CSV files or several sheets, or kept out of a spreadsheet altogether. Check the row count before converting, because a tool that reads the whole file cannot write more rows than the sheet holds.

The same page of limits gives a second figure that matters for text-heavy exports. A single cell can contain a total of 32,767 characters, so a long description column needs a check against that figure.

Why a CSV shows garbled text in Excel

The exporting program and the program opening the file disagree on how bytes map to characters, and re-saving the file in the exporter's encoding fixes it. Encoding is the rule that maps the bytes in a file to characters. When two programs use different rules, accented letters and non-English characters are the first to break, and a single accented letter can turn into two unrelated symbols.

The right encoding depends on the program that exported the file, so the export settings or the exporting system's documentation name it. Open the CSV in a text editor that lets you choose the encoding and save a copy in the one the exporter used. For the causes and the fix with UTF-8 and Shift-JIS files, see UTF-8 vs Shift-JIS: Why CSV Files Get Garbled.

How to convert many CSV files to Excel at once

For a handful of files, add them all to the converter in one go and download each xlsx file; for a folder that refills on a schedule, an automated job fits better. The sakuttoAI CSV to Excel converter accepts one or more CSV files at once, and each file shows how many rows and columns were parsed before you convert.

That count is a quick check against the row limit above. A file that reports more rows than a sheet holds has to be split before it can become a workbook. Once the counts look right, convert and download each xlsx file, then open one code column to confirm the values read as written.

A folder that refills every week is a different case, because the converter takes files that are added by hand. Automating that is a job for a script or a scheduled import, and each file still has to obey the same row and cell limits.

Import codes as text, double-click plain files

Excel's date reading cannot be switched off, so the route is chosen before the file is read, and anything past the sheet ceiling has to be split first. Each situation has one route.

• ZIP codes, product codes or dates: import through the From Text/CSV button and set those columns to Text.
• Plain numbers and text only: double-click the file and save it as Excel Workbook (.xlsx).
• Several files at once: add them all to the converter and download each xlsx file.
• Rows beyond the 1,048,576 ceiling: split the file before converting.
• Garbled characters: re-save the CSV with the encoding the exporting program used.

Look at the file once before converting and ask which columns must not change. If the answer is none, the double-click route is enough, and if even one column must stay as written, import it as text.