SpreadsheetFix

CSV vs XLSX: which format to use for exports and imports

An import screen asks for a CSV, your colleague sends an XLSX, and the file that looked fine in one format arrives broken in the other: dates in the wrong order, codes without their leading zeros, accents turned into symbols, formulas gone. CSV and XLSX store very different things, and most of these problems come from what one of them leaves out. This guide explains the difference, when each format is the right choice, how to convert between them by hand without losing data, and where SpreadsheetFix fits.

Updated
Applies to
Excel, Google Sheets, CSV
SpreadsheetFix rule
Free up to 5,000 rows

Why this happens

A CSV file is plain text: one line per row, with values separated by commas, or by semicolons in regions that use the comma as a decimal mark. It holds the values and nothing else. There are no column types, no formatting, no formulas, no second sheet and no record of the character encoding, so every program that opens it has to work out what the text means.

An XLSX file is a zipped package of XML documents. For each cell it stores a type and a value, so a date is a date and a code formatted as text stays text. It also keeps formulas, number formats, multiple sheets, charts and comments. That makes it the better container for a person working in Excel, and a heavier one for a system that only needs the data.

The classic CSV problems all come from that missing information. Excel guesses the type of every value on opening, so dates follow your regional settings, 00123 loses its zeros and a 16-digit account number is rounded. The encoding is guessed too, which is how é becomes é. Converting the other way has its own losses: saving a workbook as CSV keeps only the active sheet and only the results of formulas, and the values are written as they are displayed.

Fix it by hand

Saving an XLSX as CSV without losing data

  1. 1

    Check which sheet is active. A CSV holds one sheet, so save each sheet you need as its own file.

  2. 2

    Format columns of codes, IDs and account numbers as Text before saving, so the values written to the file keep their leading zeros and every digit.

  3. 3

    Use File > Save As and choose CSV UTF-8 (Comma delimited) rather than plain CSV, so accented names and currency symbols survive.

  4. 4

    Open the result in a text editor and check a few rows: formulas should have become values, and dates should be in the format the importing system expects.

Opening a CSV as a workbook without conversions

  1. 1

    Don’t double-click the file. In a blank workbook, use Data > From Text/CSV and choose the file.

  2. 2

    Set File Origin to 65001: Unicode (UTF-8) if the preview shows garbled characters, then click Transform Data.

  3. 3

    Set code and ID columns to Text, and date columns to Date using the locale the file was written in, then Close & Load.

  4. 4

    Save the workbook as .xlsx. From now on the types are stored in the file, so it opens the same way on any computer.

Google Sheets imports CSV with File > Import, and its Locale setting under File > Settings decides how dates and decimals are read.

Fix it with SpreadsheetFix

SpreadsheetFix accepts both formats. A CSV is decoded from its raw bytes, with the encoding detected rather than guessed from your computer’s settings, and every value is read as text, so leading zeros and long numbers arrive intact. For an XLSX it reads the first sheet, taking each cell as it is displayed in Excel; formulas come through as their results.

Cleaning then fixes the problems that bite in the next program: dates rewritten as YYYY-MM-DD so no regional setting can swap them, stray spaces, blank rows and columns, and garbled characters. On the free plan you download a CSV in UTF-8 with a byte order mark, which Excel opens with accents intact. Pro adds a native .xlsx download.

If the file is going to an import screen, download CSV. If it is going to a person who will work in Excel, the .xlsx download on Pro saves them the import wizard.

Choose a fileor drop a CSV, TSV or Excel file here, up to 50 MBProcessed in your browser. Nothing is uploaded.

No file to hand?

The same cleaner is on the CSV to Excel Cleaner page.

Before and after

Before
account,opened,owner
00123,03/04/2024,"Dana Lee "
00456,15/04/2024,Omar Haddad
After
account,opened,owner
00123,2024-04-03,Dana Lee
00456,2024-04-15,Omar Haddad

The account numbers keep their leading zeros because every value is read as text. 15/04/2024 shows the dates are day-first, so 03/04/2024 becomes 3 April.

What it won’t catch

  • The .xlsx download writes every value as a text cell, exactly as it appears in the cleaned file. That keeps codes and account numbers safe, but amounts and dates are text too, so use Convert to Number or DATEVALUE in Excel before calculating with them.
  • Only the first sheet of an uploaded workbook is read, and formatting, formulas and comments aren’t carried over. Save other sheets as separate files first.
  • A CSV that Excel has already opened and saved may have lost leading zeros or digits for good. Start from the original export.

Questions

Is CSV or XLSX better for importing data?

CSV, in most cases. Almost every import screen accepts it, it holds only the data, and you can inspect it in a text editor. Use XLSX only when the importer asks for it.

Why does my CSV lose leading zeros in Excel?

Excel reads 00123 as the number 123 when it opens a CSV. Import the file with Data > From Text/CSV and set that column to Text, or keep the file in XLSX with the column formatted as Text.

Does saving as CSV keep formulas?

No. A CSV stores the results of formulas as plain values, and only for the active sheet. Keep the XLSX as your working copy and export a CSV when you need one.

Can SpreadsheetFix convert CSV to XLSX?

Yes, on Pro: clean the CSV and download it as .xlsx. The free plan downloads cleaned CSV files for up to 5,000 rows.