You've exported a tidy workbook, sent the CSV to finance, and received an awkward message: the sort codes look like scientific notation, dates have changed sides of the Atlantic, and a downstream system rejects the file without explaining why. The spreadsheet looked correct because Excel was displaying an interpretation of the data. The CSV contains the text and separators that another programme must interpret for itself.

That's why a reliable Excel to CSV workflow is less about finding “Save As” and more about controlling encoding, delimiters, dates, identifiers, and workbook structure. UK teams face particular risks because regional settings can change separators and date interpretation, while government data publishing practices show how important clean, reusable CSV files are for long-lived analytical workflows. GOV.UK's CSV guidance recommends a single header row, one data type per column, and a structure that doesn't depend on assumptions made by software or people.

Why Excel to CSV Conversions Go Wrong

A finance team can start with a perfectly organised workbook and still produce a damaged export. A UK sort code may lose its formatting or appear in scientific notation. A VAT value may be read with the wrong decimal convention. A postcode or customer reference beginning with zero can arrive without that zero, even though the original cell looked fine.

The root problem is structural. Excel is sheet-centric, with tabs, formulas, merged cells, hidden rows, filters, formatting, and display rules. CSV is a flat text file, normally representing one table separated by a delimiter. It has no native concept of multiple tabs, cell colour, formulas, or merged headings.

What disappears during export

When you save a workbook as CSV, Excel exports only the active sheet. It also removes formatting, formulas, links, and other workbook features, as explained in GOV.UK's spreadsheet creation guidance. A formula usually becomes its last calculated value, not the formula itself. That can be exactly what a reporting system needs, but it can also hide a stale calculation.

Hidden rows and columns deserve special attention. GOV.UK warns that CSV export can expose hidden data, so exporting and then re-importing the file into a fresh spreadsheet is a useful check before distribution. Filters and grouped rows can create similar surprises when the person exporting assumes the visible view is the complete dataset.

Practical rule: Treat a CSV as a new data product, not as a lightweight copy of the workbook.

Locale adds another failure point. A workbook created on a German or French installation may use a semicolon because the system treats the comma as a decimal separator. A UK colleague, accounting package, or government upload tool may expect commas instead. The result can be a file that opens but places entire rows into the wrong columns.

The three decisions that determine whether an export survives another system are character encoding, delimiter selection, and cell-format awareness. Get those right before clicking Save, and most silent corruption becomes visible rather than operational.

Exporting CSV from Desktop Excel and Google Sheets

For a straightforward Windows export, open the workbook, select the sheet you intend to publish, then choose File, Save As, and CSV UTF-8 (Comma delimited). The UTF-8 option is the sensible default when the file contains characters such as £, é, or ñ, because it preserves a broader character set than legacy regional encodings.

Excel will warn that some workbook features aren't compatible with CSV. Accept that only after checking the active sheet, because the warning means Excel is discarding workbook-level features. The export won't carry across other tabs, formulas as formulas, visual formatting, hyperlinks, or merged-cell structure.

Windows Excel checks

Before saving, make the data table itself self-contained:

• Use one header row: Give every column a clear title and keep the order stable.
• Protect identifiers: Format sort codes, telephone numbers, account references, and postcode components as text where leading zeroes matter.
• Inspect hidden content: Unhide rows and columns or export, re-import, and compare the result.
• Check the active tab: Only that sheet travels into the CSV.

If you're working from a PDF-derived table first, clean the extracted text before exporting. The workflow described in this guide to copying text from a PDF is useful when the spreadsheet began as a document conversion rather than a native workbook.

Excel for Mac offers the same broad Save As route, but delimiter behaviour can vary with macOS and Office settings. Don't assume that a file labelled CSV uses the delimiter your receiving system expects. Open the result in TextEdit or another plain-text editor and inspect the first few rows. You're checking whether fields are separated by commas, semicolons, or something unexpected.

Google Sheets behaves differently

In Google Sheets, choose File, Download, then Comma-separated values. The export applies to the current sheet. If you need several tabs, download each sheet separately, or use a workbook export process that creates separate files. This is still a one-table-per-CSV model, but it makes the limitation explicit.

Google Sheets generally produces UTF-8 without a BOM. That's fine for many APIs, Linux tools, and data pipelines, but some Excel-centred workflows expect a byte-order mark to recognise the encoding correctly. Test the receiving tool rather than assuming that one encoding suits every destination.

Office 365 browser Excel can offer fewer Save As choices than desktop Excel. Download the workbook first, open it in desktop Excel or LibreOffice, and then make the encoding and delimiter decision deliberately.

Using LibreOffice and Open-Source Alternatives

LibreOffice Calc is often the safer choice when delimiter control, encoding control, and repeatable exports matter more than preserving a complex Excel presentation. It exposes the decisions that Excel frequently leaves to workbook or operating-system settings.

Open the workbook in Calc, choose File, Save As, select Text CSV, and enable the Use Text CSV Dialog option. The next dialogue lets you choose the field delimiter, text qualifier, and character set together. That matters when you're moving a file between UK, European, and international systems.

A practical comparison

Criterion	Desktop Excel	LibreOffice Calc
Delimiter control	Often influenced by regional settings and export choice	Explicitly selectable in the Text CSV dialogue
Encoding on save	CSV UTF-8 is available in modern desktop versions	UTF-8, UTF-16, ASCII, and legacy code pages are exposed in the export dialogue
Batch handling	Usually needs VBA, PowerShell, or another script	Supports macro-based workflows and command-line tools
Best fit	Complex Excel formatting and familiar workbook behaviour	Controlled interchange and mixed-platform exports

LibreOffice's encoding list is valuable when a receiving system demands a specific code page. UTF-8 remains the sensible general choice, but old accounting and line-of-business tools sometimes require something else. Choose based on the consumer, not on what happens to be installed on the sender's computer.

For repeated work, you can open several XLSX files in sequence and re-save them through a recorded macro using Tools, Macros. That isn't a substitute for a properly tested script, but it's practical for a small controlled batch.

OnlyOffice and Gnumeric are lighter alternatives for headless Linux servers. Gnumeric's ssconvert is particularly useful when a command-line workflow is preferable to starting a full office suite.

Excel still wins when a workbook contains complicated number formats, carefully maintained formulas, or features that Calc may interpret differently. LibreOffice is the better default when a clean, inspectable text export matters more than visual fidelity.

Batch Conversion and Automation with Scripts

Opening dozens of workbooks manually is how inconsistent settings enter a data pipeline. Automation gives you repeatability, but only if you define the output rules and record what happened.

On Windows, PowerShell can call Excel through COM. The following compact example loops through XLSX files, opens each workbook, exports the first worksheet, and saves a CSV. Excel's COM constants vary by installation, so test the encoding and delimiter on a sample before using it for production data.

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
Get-ChildItem "C:input*.xlsx" | ForEach-Object {
  $book = $excel.Workbooks.Open($_.FullName)
  $sheet = $book.Worksheets.Item(1)
  $out = Join-Path "C:output" ($_.BaseName + ".csv")
  $sheet.SaveAs($out, 62)
  $book.Close($false)
}
$excel.Quit()

The trade-off is important: COM requires Windows and an installed Excel desktop application. It also inherits Excel's interpretation of dates, formulas, hidden content, and regional settings. It's convenient in a controlled workstation environment, not an ideal portable server solution.

Python for multi-sheet workbooks

Python with pandas gives you more explicit control over workbook tabs. sheet_name=None loads all visible and hidden worksheet names returned by the reader, so you should decide which sheets are legitimate inputs rather than exporting blindly.

import pandas as pd
from pathlib import Path

source = Path("input.xlsx")
output = Path("csv")
output.mkdir(exist_ok=True)

book = pd.ExcelFile(source)
for name in book.sheet_names:
    frame = book.parse(name)
    safe_name = name.replace(" ", "_")
    frame.to_csv(output / f"{source.stem}__{safe_name}.csv",
                 index=False, encoding="utf-8-sig")

The utf-8-sig setting writes a BOM, which is useful for downstream Excel users. APIs and Unix tools may prefer plain UTF-8 without it, so make the choice part of your interface specification.

Name files with a stable workbook-sheet pattern, such as payroll__employees.csv, rather than relying on a generic Sheet1.csv. If you need a refresher on extensions and file formats, this file-type guide covers the underlying distinction.

For non-coders, pyexcel-cli can provide a simpler command-line route. Whichever tool you use, write a log containing the source path, sheet name, row count, and output file size. That small audit trail can save hours when a government or finance pipeline reports a mismatch.

Handling Encodings, Delimiters and Date Formats

Double-clicking a CSV in Excel is the root cause of many encoding and date disasters. Excel opens the file by guessing how to split and interpret it, then may rewrite values when you save the workbook again. Import the file as text instead, and specify the delimiter, encoding, and column types deliberately.

UTF-8 with BOM is usually the safest option when Excel is the consumer. The BOM helps Excel recognise UTF-8 and reduces the chance that accented names or currency symbols become unreadable. Plain UTF-8 without a BOM is often preferable for APIs, Linux utilities, and data-processing pipelines that already know the encoding.

Delimiters follow local rules

Commas aren't universally safe. Systems using a comma as the decimal separator may select semicolons as field separators, and a UK Excel installation can still inherit a semicolon list separator from regional settings. That creates a nasty failure mode: the CSV appears valid in a text editor, but the receiving application sees one wide column or shifts values into the wrong fields.

In Excel, delimiter behaviour can be influenced through regional and web options. In LibreOffice, select the delimiter directly in the Text CSV save dialogue. Standardise the choice with the receiving team before exporting, especially when the file will pass through accounting software or a public-sector upload service.

Dates and identifiers need protection

Excel stores dates internally as serial values and displays them according to locale. A value intended as DD/MM/YYYY may be interpreted as MM/DD/YYYY, while a two-digit year can be assigned to the wrong century according to application rules. Export dates as ISO-style strings, YYYY-MM-DD, before writing the CSV.

Treat sort codes, phone numbers, postcodes, and reference numbers as text. That preserves leading zeroes and prevents long values from becoming scientific notation. UK-focused guidance on CSV and Excel compatibility highlights the same risks around UTF-8 recognition, regional dates, and careful import.

CSV quoting has its own rules. A field containing a comma, newline, or quote needs quoting, and embedded quotes are normally doubled. Fields beginning with =, +, -, or @ can also create CSV-injection risk when a recipient opens the file in a spreadsheet. Clean or neutralise those values when the file includes user-submitted content.

For a practical text-cleaning example, this handwriting-to-text guide illustrates why extracted content needs validation before it enters a structured export.

A short demonstration of import behaviour can help teams understand why the visible spreadsheet isn't the whole story.

Pre-Export Checklist for Clean CSV Files

Run this sequence before every important export. It's deliberately procedural because memory is unreliable when a deadline is close.

Prepare the workbook

1. Remove hidden rows and columns. Unhide them, decide whether they belong in the published table, then remove anything that shouldn't travel.
2. Clear filters. A filtered view can mislead the exporter and the reviewer. Confirm the intended population before saving.
3. Collapse grouped rows and inspect the result. Grouping is a display feature, not a CSV structure.
4. Resolve merged cells. CSV has no equivalent for merged headings. Repeat the relevant value or redesign the header as a normal table.
5. Delete trailing blank rows. They can confuse row counts and loaders.

Check the data itself

Confirm that the first row contains column titles and that every later row aligns with those titles. GOV.UK's tabular-data publishing guidance recommends a header row in the first row, matching values beneath it, and separate JSON metadata for published CSV data.

Use one date representation throughout. Convert identifiers that require leading zeroes to text, and inspect formulas whose displayed values may be stale. Don't rely on colour, conditional formatting, or cell borders to communicate meaning because CSV won't retain them.

Choose export settings

Select UTF-8 with BOM when Excel is the receiving application. Select plain UTF-8 when an API or data pipeline specifies it. Use a comma unless the agreed locale requires a semicolon, and decide whether the destination expects minimal quoting or every field quoted.

Give the file a stable name, such as a meaningful slug with a date stamp. Avoid spaces and ambiguous versions like final-final-new.csv.

Validate the finished file

Open the CSV in a plain-text editor, not only Excel. Check the delimiter, the header, accented characters, quotes, line breaks, and the first and last records. Run csvlint from csvkit when available, then compare the reported row count and columns with the source.

If the data began as an image, clean the source before conversion. This PNG-to-PDF workflow is a useful reminder that every transformation creates another opportunity for layout and character errors.

Common Questions About Excel to CSV

Do Excel-readable CSV files need a BOM?

Not always. UTF-8 with BOM is the safer choice when Excel users will open the file directly, while UTF-8 without a BOM is commonly better for APIs and command-line data tools. Follow the receiving system's specification and test with a representative file.

Why did my colleague receive a semicolon-delimited file?

Their regional settings may use a comma as the decimal separator, causing Excel or another spreadsheet application to select semicolons as the list separator. Open the file in a text editor, confirm the actual delimiter, and set the receiving application to match it rather than changing values manually.

How do I export a workbook with many sheets?

CSV can carry only one table, so a multi-sheet workbook needs one CSV per sheet. Use a script, pandas, LibreOffice automation, or a controlled export routine, and include both the workbook and sheet names in each output filename.

Why does R, Python, or a SQL loader reject a valid-looking CSV?

The file may contain a BOM the parser doesn't expect, use semicolons instead of commas, include inconsistent quoting, contain embedded line breaks, or have a header that doesn't match the data columns. Inspect the raw text and declare the encoding and separator explicitly in the importing tool.

Should I edit a CSV by opening it in Excel?

Only when you're prepared to import each column deliberately. Excel can reinterpret dates, remove leading zeroes, and change long identifiers. For safer edits, use a text-aware editor or import the CSV with identifier columns forced to text. If you need to add text to a source document first, this PDF text-editing guide explains a related preparation step.

---

Firacard helps distributed teams create a collaborative online leaving card or group greeting card without passing a physical card around. Visit Firacard to organise personalised birthday, farewell, appreciation, or milestone ecards for contributors across the United Kingdom, United States, Australia, Canada, India, and Africa.

Share It