Skip to content
Blogguide

GUIDE

Import invoice CSV into Excel or Google Sheets without losing IDs

A good extraction can still become a bad spreadsheet at import time. The usual culprits are separators, automatic type conversion and repeated totals.

By Kaliits · Reviewed · Editorial standards

Preview the CSV instead of double-clicking it

CSV is a plain-text table format. It does not carry the cell types of an XLSX workbook. Your spreadsheet guesses where columns end and whether values are dates, numbers or text. Those guesses are not always what an invoice needs.

Import through the spreadsheet’s data or file-import dialog. Select the separator matching the export preset and inspect the preview. The Kaliits invoice CSV export is a ZIP containing separate tables, so unzip it first. A ZIP file is not itself a CSV table.

Invoice numbers belong in text columns

An invented reference 000042 can become 42 if a spreadsheet treats it as a number. A long identifier can also be shown in scientific notation. Set invoice-number and identifier columns to text while importing rather than adding zeros after the fact.

Do the same for product references when their leading zeros matter. Amounts and quantities should become numbers using the correct decimal convention. Keep currency in a separate column so a symbol does not turn an otherwise numeric value into unstructured text.

Match separators and date conventions

A French-style export may use a semicolon between fields and a comma for decimals. An English-style import may expect comma-separated fields and decimal points. Select the matching options rather than performing a global find-and-replace that might damage descriptions.

Google documents File > Import and separator selection for text files. In Excel, use the supported text/CSV import options for your installation and inspect the preview. For dates, verify a value whose day is greater than 12; it makes an incorrect month/day interpretation easier to detect.

Keep invoice totals out of repeated line sums

Invoices.csv and Line_items.csv represent different levels of detail. One invoice can have many line items. If you join its total onto every item row and then sum that total column, you multiply the invoice’s value by the number of items.

Use document_id for the relationship. Sum line amounts when analyzing items, and sum each invoice total once when analyzing invoices. Check Manifest.csv for excluded records and confirmation state so an incomplete batch is not mistaken for a complete export.

A five-cell import check

Before appending a large batch, verify one reference with leading zeros, one accented description, one decimal amount, one date and one currency. Compare them with the original and inspect the actual cell values, not only the displayed formatting.

If the destination keeps making incorrect guesses, use the XLSX export instead and confirm its imported types. Exporting data is only one step; the destination spreadsheet still needs a quick review.

Sources and further reading