All posts

Let users import a spreadsheet (CSV) without the mess

You added "import from Excel" to your app. Now the mail arrives. "My import is all in one column." "The names have weird characters." "The dates are wrong." "Half the rows are missing." "I imported twice and now everything is doubled." Apps built with Lovable, Bolt, Cursor and Replit all hit this, and it is most common with Dutch, German and other European users. Facts below come from RFC 4180, Microsoft Support, Papa Parse, MDN, the PostgreSQL docs, the Supabase docs and OWASP, checked in November 2026.

Short answer: a CSV file is not one format. Separator, text encoding, number style and date style change from computer to computer. So do not guess. Detect the separator, show a preview, let the user map the columns, check every row on the server, report errors by row number, and make a second import of the same file harmless.

1. What CSV is, and what it is not

CSV means comma-separated values. The best-known description is RFC 4180. Its status line says: "This memo provides information for the Internet community. It does not specify an Internet standard of any kind." The RFC itself says the format "has never been formally documented" before it. So it is a description of common practice, not a strict rule that every program follows.

What it describes: fields are "separated by commas", and "Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes." A double quote inside a quoted field is written as two double quotes. It also admits that "some programs, such as Microsoft Excel, do not use double quotes at all". And on text encoding it says only: "Common usage of CSV is US-ASCII", with other character sets possible through a "charset" parameter.

Lesson: do not split lines on commas yourself. That breaks the first time a name or address contains a comma or a line break. Use a parser library, and treat what comes in as untrusted.

2. Comma or semicolon

This is the "all in one column" mail. Microsoft's page on importing and exporting text and CSV files says: "When you save a workbook as a .csv file, the default list separator (delimiter) is a comma. You can change this to another separator character using Windows Region settings." It also explains how to get a semicolon in Excel only: "changing the default decimal separator to a comma - this forces Excel to use a semi-colon for the list separator."

So a computer set up with a decimal comma can produce semicolon files. The page does not list countries, and I will not guess which ones. The point for your app: you will receive both kinds.

Comma fileSemicolon file
Headername,pricename;price
RowZoë,"12,50"Zoë;12,50

A parser that only knows commas reads the second file as one column. The fix is auto-detect. The Papa Parse docs describe the delimiter option: "Leave blank to auto-detect from a list of most common delimiters, or any values passed in through delimitersToGuess." In the docs' default config it is delimiter: "" with the comment "auto-detect", and the default guess list includes the semicolon. If it cannot decide, the docs list an error code, "UndetectableDelimiter". Show that to the user and let them pick the separator from a small dropdown.

3. Weird characters: encoding

When é, ü or ø turn into garbage, the file was saved in one text encoding and read as another. The Papa Parse FAQ answers "Why do non-ASCII characters look weird?" with "It's probably an encoding issue." Its encoding option is "The encoding to use when opening local files. If specified, it must be a value supported by the FileReader API." Let the user switch the encoding in the preview.

The other direction matters too, when your app exports a CSV that people open in Excel. Microsoft's page Opening CSV UTF-8 files correctly in Excel says: "You can open a CSV file encoded with UTF-8 normally if it was saved with BOM (Byte Order Mark)." So write a BOM at the start of your exported file. I found no official page that says how Papa Parse treats a BOM on import, so test that with a real file from Excel before you trust it.

4. Dates and numbers

The text 1.234,56 is one thousand two hundred thirty-four and fifty-six cents in much of Europe. The text 1,234.56 is the same amount in the US. The date 03/04/2026 is 3 April for one person and 4 March for another. Nothing in the file says which one you have.

Do not let the browser guess. MDN's Date.parse page has a section on non-standard date strings that starts with a note: "This section contains implementation-specific behavior that may be inconsistent across browsers or different versions of browsers." So new Date("03/04/2026") is a gamble.

Safer: in the mapping step, ask "Which date style is in this file?" and "Which number style?" with a live preview of the first rows. Then parse with your own code for exactly that style, and reject values that do not fit. For the date traps after import, see wrong-day dates and time zones.

5. The import flow that avoids support mail

  1. Upload. Set a file size limit and a row limit. Microsoft's page above says Excel can import or export "up to 1,048,576 rows and 16,384 columns", so expect big files. See file upload checks.
  2. Preview. Show the first five to ten rows in a table, with the detected separator and encoding.
  3. Map columns. Let the user say which column is the email, which is the name.
  4. Validate every row on the server. The browser check is a convenience. The server check is the real one. See form checks in your AI-built app.
  5. Show row-level errors. "Row 14: email missing." "Row 31: date not valid."
  6. Import all valid rows, or none. Use one database transaction. The PostgreSQL tutorial says a transaction "bundles multiple steps into a single, all-or-nothing operation" (PostgreSQL docs). Decide the rule up front and say it on screen.
  7. Make a re-import safe. Pick a unique key per row, such as the email or an order number, and add a unique constraint. Then use an upsert. The PostgreSQL INSERT page says "ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome". Importing the same file twice then updates rows instead of doubling them. More in duplicate records from double clicks and retries.

6. When your app exports CSV: formula injection

If you let users export back to CSV, a cell that starts with a formula character can run in the spreadsheet. OWASP's CSV Injection page says: "When a spreadsheet program such as Microsoft Excel or LibreOffice Calc is used to open a CSV, any cells starting with = will be interpreted by the software as a formula." Its list of characters to watch is the equals sign, plus, minus and at, and also tab, carriage return and line feed. OWASP warns that the usual quote tricks "are not reliable in Microsoft Excel after saving and re-opening the CSV file" and describes a tab-prefix method with a trade-off.

7. Supabase and Firebase

The Supabase import guide describes a CSV import in the dashboard: "If you're working with small datasets in development, you can experiment with CSV import in the Supabase dashboard," and it notes "the size limit is 100MB". That is a tool for you as the owner, in the Table Editor. It is not a feature your users can use, so your app still needs its own import flow. The Supabase tables guide also covers Postgres's COPY command for bulk loads, with DELIMITER and HEADER options. I did not find an official page on CSV import for Firestore, so I leave Firebase out. Check the current Firebase docs for your case.

Prompts to paste into Lovable, Bolt or Cursor

1. Preview and mapping.

Add a CSV import for the contacts table. Steps: upload, preview, map columns, import. Parse with Papa Parse, leave the delimiter option empty so it auto-detects, and let the user override the separator and encoding. Show the first 10 rows in a table. Show a column mapping step where each file column maps to a database field. If the delimiter cannot be detected, show a clear message and a separator dropdown.

2. Server checks and one transaction.

Validate every imported row on the server, not only in the browser. Return a list of errors with the row number and the reason, for example "Row 14: email missing". Insert the valid rows in one database transaction. If the user chose "all or nothing", roll back everything when any row fails. Set a maximum file size and row count and reject larger files with a clear message.

3. Safe re-import.

Add a unique constraint on contacts.email per account. Import with an upsert (INSERT ... ON CONFLICT DO UPDATE) so importing the same file twice updates rows and creates no duplicates. After the import, show how many rows were added, updated and skipped.

Checklist

  1. The parser is a library, not a split on commas.
  2. The separator is detected, with a manual override.
  3. The user sees a preview before anything is saved.
  4. Accented characters survive. You tested a file saved from Excel.
  5. Date and number style are chosen or confirmed, never guessed silently.
  6. Every row is checked on the server.
  7. Errors show a row number and a reason.
  8. Import is all valid rows or none, inside one transaction.
  9. A unique key and an upsert make a second import harmless.
  10. File size and row count have limits.
  11. Exported files are checked for cells that start with =, +, - or @.

Related: adding Dutch and German to your app covers the language side of serving European users.

Want this fixed for you?

I finish and fix apps built with Lovable, Bolt, Cursor or Replit, including import and export flows that hold up with real spreadsheets. Or send me the details and I'll reply within 24 hours.