AI App With Database

How to import a CSV into an app without breaking it

A step-by-step method for importing a CSV into an app’s database safely: preparing the file, protecting IDs and dates, mapping columns, testing a small batch, handling duplicates and undoing a bad import.

Floot ·

Key Takeaways

  • Back up before you import. Download a copy of the app’s data or confirm you can restore it. A bad import is much easier to undo than to clean up row by row.
  • Never let a spreadsheet “fix” your file. Excel removes leading zeros and keeps only 15 significant digits, so codes and long IDs can change silently when a CSV is opened and saved.
  • Make every column unambiguous. Dates as YYYY-MM-DD, money as plain numbers, yes/no as true/false, encoding as UTF-8.
  • Decide how duplicates are matched. Pick one column, such as email or SKU, that identifies an existing record, and decide whether a match updates it or is skipped.
  • Import 20 rows first. Check them on screen against the file before importing the rest.

Quick-Start Prioritization Framework

Symptom after importLikely causeFix
02139 became 2139File opened and saved in a spreadsheetFormat the column as text; re-export from the source
Long IDs end in 00015-digit number precision in ExcelTreat IDs as text, never numbers
José became JoséFile not saved as UTF-8Export as CSV UTF-8
03/04 read as 4 March instead of 3 AprilAmbiguous date formatConvert dates to YYYY-MM-DD
Columns shifted on some rowsUnquoted commas or line breaks inside a fieldRe-export with proper quoting
Same customer twiceNo matching rule for existing recordsMatch on a unique column, update instead of insert

Importing a CSV looks like the easiest job in software: export from one place, upload to another, done. Then the ZIP codes lose their leading zeros, half the dates flip day and month, accented names turn into strange symbols, and 300 customers appear twice. None of this is bad luck. Each problem has a known cause and a known fix. This guide walks through preparing, testing and running an import so your app’s data comes out as clean as it went in, and so you can recover if it doesn’t.

Before you start: back up and decide the goal

Two questions shape everything else. Is this a one-time load into an empty table, or an update to records that already exist? And can you undo it if it goes wrong?

Make undo possible

Download the app’s current data, or check that its backup can restore to a moment just before the import. If neither is possible, import into a copy or a test version of the app first.

New records, updates or both

A first import of a customer list only creates rows. A weekly price update only changes existing rows. A sync from another system does both. Know which one you’re doing, because the duplicate-handling rules differ.

What a CSV really is

CSV stands for comma-separated values. The common format is documented in RFC 4180: one record per line, fields separated by commas, an optional header line with column names, and any field containing a comma, double quote or line break wrapped in double quotes. A double quote inside a field is written twice.

Why that matters

An address like 12 High St, Apt 4 contains a comma. If the exporter didn’t wrap it in quotes, the importer sees an extra column and every field after it shifts one place. Notes fields with line breaks cause the same problem across rows. RFC 4180 itself notes that implementations vary, which is why you should look at the raw file.

Look at the file as text

Open the CSV in a plain text editor, not a spreadsheet, before importing. You’ll see exactly what the importer will see: real quotes, real dates, real zeros.

Step 1: Protect IDs, codes and long numbers

This is the most common way imports quietly corrupt data, and it usually happens before the file reaches your app.

Leading zeros

Microsoft’s support documentation says Excel “automatically removes leading zeros.” ZIP codes, employee numbers and product codes like 00451 lose their zeros if the CSV is opened in Excel and saved. The fix is to bring the file in through Data, From Text/CSV and set those columns to Text, or to avoid opening the file in Excel at all.

15-digit precision

Excel’s published limits list number precision of 15 digits, and Microsoft explains that digits after the 15th become zeros. A 16-digit tracking or account number is silently changed. Long numbers can also display in scientific notation, such as 1.23E+15.

Rule of thumb

If you would never do arithmetic on it, it’s text. Phone numbers, ZIP codes, SKUs, account numbers and IDs belong in text columns in both the file and the database.

Step 2: Normalize dates, money, booleans and encoding

Every column should have exactly one possible reading. Ambiguity is where imports go wrong.

DataUse thisAvoid
Dates2026-03-0403/04/2026, 4 Mar, March 4th
Date and time2026-03-04 14:30 with a stated time zoneTimes without a zone
Money1234.50 in a column named price_usd$1,234.50 or 1.234,50
Yes/notrue / falseY, yes, x, ticked
Empty valuesTruly emptyN/A, none, -, 0 used to mean unknown
Text encodingUTF-8Legacy encodings like Windows-1252

Empty versus unknown

Databases treat an empty value and the text “N/A” very differently. PostgreSQL’s COPY command, for instance, reads an unquoted empty field in CSV as null by default. If your file uses N/A or a dash for missing values, they’ll be stored as text. Replace them with empty cells before importing.

Encoding

If names with accents or non-Latin characters come out garbled, the file wasn’t read as the encoding it was saved in. Export as CSV UTF-8, which most modern apps expect.

Step 3: Map columns to fields

The importer needs to know which CSV column goes to which field in the app.

Rename headers to match

Changing the header row to the app’s exact field names (email, phone, created_at) avoids most mapping mistakes. Some importers can be told to require an exact match; PostgreSQL’s COPY has a HEADER MATCH option that raises an error if the header names and order don’t match the table.

Split combined columns

If the app stores first and last name separately, or street and city separately, split them in the file first. Splitting after import is much harder.

Linked records use IDs

If you’re importing orders that belong to customers, the orders file should contain the customer’s ID in the app, not their name. Import customers first, export them with their new IDs, then look those IDs up in the orders file.

Step 4: Handle duplicates deliberately

Every import into a table that already has data needs a matching rule. Without one, you get duplicates.

Choose a matching key

Use a column that is unique for each record: email for customers, SKU for products, invoice number for invoices. Clean it first: lowercase emails and trim spaces so Anna@Shop.com and anna@shop.com match.

Choose what a match does

Update the existing record with the file’s values, skip the row, or flag it for review. For price updates, update. For a customer list from a trade show, skip or flag so you don’t overwrite better data you already have.

Let the database help

A unique constraint on the matching column means the database itself refuses a duplicate, instead of relying on the importer to notice.

Step 5: Test a small batch, then run and verify

Take the first 20 rows, plus a few deliberately awkward ones: the longest name, a row with an accent, a row with a comma in the address, a row with blanks. Import those.

Check on screen

Open each test record in the app and compare it field by field with the file. Look closely at dates, money, leading zeros, special characters and anything linked to another record.

All or nothing is a feature

Some importers stop on the first bad row; others skip bad rows and continue. PostgreSQL’s COPY fails the whole command by default, and newer versions offer an option to skip bad rows instead. Stopping is usually safer for small imports, because you fix the file and rerun rather than hunting for which rows were dropped.

Reconcile counts

After the full import, compare the number of rows in the file with the number created or updated, and spot-check a total such as the sum of balances. If 1,000 rows went in and 994 came out, find the six.

Common mistakes

Nearly every broken import traces back to one of these.

Opening and re-saving the CSV in Excel

It looks harmless and silently changes zeros, long numbers and dates. Edit in a text editor or import with columns set to text.

Importing everything on the first try

A 20-row test takes two minutes and catches almost every format problem.

No backup

Without a restore point, undoing 5,000 bad updates means writing a second import to reverse the first.

Linking by name

Two customers called J Smith will merge. Link by ID or another unique value.

Placeholder values

N/A, TBD and 0 meaning unknown end up as real data that skews reports.

If you’d rather not run the import yourself

If you’d rather not set up an importer, Floot lets you upload a CSV and ask its AI to import it into your app’s Postgres database, and paid plans can restore the database to any moment in the last 7 days if something goes wrong. Preparing the file with the steps above still makes the result cleaner.

Frequently Asked Questions

Why does my CSV lose leading zeros?

Usually because it was opened and saved in Excel, which removes leading zeros by default according to Microsoft. Import it with those columns set to Text, or edit the file in a plain text editor instead.

What date format should I use in a CSV?

YYYY-MM-DD, such as 2026-03-04. It can’t be confused between US and European formats, and most databases and apps read it directly.

Why do special characters look wrong after importing?

The file was saved in one text encoding and read as another. Export as CSV UTF-8 and import it as UTF-8.

How do I avoid duplicate records when importing?

Pick a unique matching column such as email or SKU, clean it, and set the import to update or skip existing matches rather than always creating new rows. A unique constraint in the database adds a second safety net.

How do I undo a bad CSV import?

Restore from a backup or point-in-time restore taken before the import. That’s why you should confirm a restore option, or download the data, before you start.

What does RFC 4180 say about commas inside fields?

Fields containing commas, double quotes or line breaks should be enclosed in double quotes, and a double quote inside a field is escaped by writing it twice.

Sources