The CSV Import Preflight Checklist: What to Check Before Uploading to Any App

Before you upload a CSV to QuickBooks, Shopify, HubSpot, Mailchimp, or any other app, run it through the same preflight checks a pilot runs before takeoff. Most import failures come from a short list of predictable problems: wrong encoding, mixed delimiters, more than one header row, missing required fields, inconsistent value types in a column, and duplicate keys. Checking these at the row level before you hit upload turns a useless 'your file failed' message into a specific fix you can make in two minutes.

This is the general checklist that sits underneath every destination-specific guide. The exact field names and limits change from app to app, but the categories of failure do not. Work through the twelve checks below in order, then use the condensed master checklist near the end as your repeatable preflight routine.

1. Confirm the file is UTF-8 encoded (and know where the BOM is)

Encoding is the single most common silent killer. If a file is saved as Windows-1252, Latin-1, or ANSI instead of UTF-8, accented and non-Latin characters either corrupt or block the whole import. Shopify states plainly that a UTF-8 encoded CSV is the only format it accepts for product import. Mailchimp requires UTF-8 too, and a mis-encoded file either fails outright or silently turns 'Muller' into 'M?ller' and 'Garcia' into 'Garc?a' across your entire list.

The wrinkle is the byte order mark (BOM), a hidden three-byte prefix (EF BB BF) that Excel adds when you 'Save As CSV UTF-8'. Some importers read the BOM as part of the first header, so a column named 'Handle' arrives as 'Handle' with three invisible bytes glued to the front and the destination reports a missing required column it is actually looking at.

Failure example (Shopify): the BOM attaches to the first header,
so 'Handle' no longer matches the case-sensitive column name and
the importer rejects every row as missing a handle.

Fix: re-save as UTF-8, and if the destination is strict about the BOM, save as UTF-8 without BOM. When you open a file in a plain text editor and see characters like A with a tilde or a black diamond question mark at the very start of line one, that is a BOM or an encoding mismatch talking.

2. Check delimiter consistency across the whole file

A CSV is comma-separated by definition, but files exported from European systems and some banking tools use semicolons instead. Mailchimp calls this out directly: some European spreadsheet programs separate fields with semicolons, and you have to replace them with commas before the import will work. Worse than a wrong delimiter is an inconsistent one, where most rows use commas but a handful use semicolons or tabs because they were pasted in from another source.

The symptom of a delimiter mismatch is your entire file loading into a single column, or column counts that jump around row to row. If the importer thinks row 1 has 8 fields and row 400 has 3, one of those rows used a different separator or contains an unescaped delimiter inside a value.

3. Make sure there is exactly one header row

Almost every importer expects the first line to be column headers and every line after it to be data. Bank and accounting exports routinely break this rule by stacking a report title, an account number, a date range, and a blank line above the real header. HubSpot requires a single header row with one record per row after it; a report banner sitting on line 1 means the importer treats your real headers as data and your first real record as a header.

Before (bank export with report preamble):
Account Statement - Checking ****4471
Period: 01/01/2026 - 01/31/2026

Date,Description,Amount
01/03/2026,COFFEE SHOP,-4.50

After (one clean header row on line 1):
Date,Description,Amount
01/03/2026,COFFEE SHOP,-4.50

Also confirm you do not have a duplicated header row halfway down the file, which happens when two exports get concatenated. That middle header becomes a garbage data row that fails type validation on every numeric column.

4. Verify required fields are present and actually filled

Every destination has a set of columns it cannot proceed without, and it checks two things: that the column exists, and that each row has a value in it. QuickBooks Online is a clean example. Its bank import needs either a 3-column layout (Date, Description, Amount) or a 4-column layout (Date, Description, Credit, Debit). Miss one and you get the exact message: 'Something's not quite right. Your file does not contain the three required columns: transaction date, memo and amount.'

Present is not the same as filled. A required column that exists but has blanks in some rows will pass the header check and then fail row-by-row. Scan each required column for empty cells before you upload, not after the importer stops on row 212.

5. Keep field types consistent within each column

An importer decides how to parse a column based on the expectation that every cell in it is the same kind of thing. Dates are where this breaks most often. QuickBooks reads a bank file row by row and fails on the first date it cannot parse, so a file that is mostly MM/DD/YYYY with a few DD/MM/YYYY rows (or a stray '20/11/2018 TUE' where the bank appended the weekday) stops the whole import.

Before (mixed date formats in one column - QuickBooks stops here):
01/03/2026
03/01/2026
2026-01-05
20/11/2026 TUE

After (single consistent format):
01/03/2026
03/01/2026
01/05/2026
11/20/2026

6. Enforce unique keys with no duplicates

Most destinations use one column as the identity of a record and reject or overwrite rows that collide on it. HubSpot deduplicates contacts by email: if two rows in your file share an email, you risk errors or unintended overwrites, and if a duplicate already exists in the CRM the import throws an error for that row and skips it. Mailchimp similarly trips on duplicate contacts inside a single file. On product catalogs, a repeated SKU or handle causes the same class of collision.

Deduplicate on the key column before uploading, and decide deliberately whether the winning row should be the first or last occurrence. Silent overwrites are worse than a hard rejection because you may not notice which record's data survived.

7. Remove blank rows and blank columns

A fully blank row in the middle of a file is a row with zero values in every required column, so it fails validation just like a real missing-data row would. Trailing blank rows at the bottom of an export are the usual culprit and are easy to miss because they look empty in a spreadsheet. Empty columns, often a stray comma at the end of each line, create a phantom unnamed column that some importers refuse to map.

8. Validate quoting and escaping

Any value that contains a comma, a line break, or a double quote must be wrapped in double quotes, and literal double quotes inside a value must be doubled. A product description with an unescaped comma splits into two fields and throws off the column count for that row only, which is why these failures look random. This is also why Shopify product bodies (Body HTML), full of commas and quotes, are a frequent source of 'wrong number of columns' errors.

Wrong (comma inside value, no quotes - row splits into 4 fields):
SKU-19,Bracket, steel,12.00

Right (value quoted, so it stays one field):
SKU-19,"Bracket, steel",12.00

Right (embedded quote doubled):
SKU-20,"3"" steel pipe",8.50

9. Run a row count sanity check against the source

Before uploading, compare the row count of your cleaned file against what you expected from the source. If your bank statement had 148 transactions and the file has 150 data rows, two extra rows crept in (often the report footer or a subtotal line). If it has 146, your cleaning step dropped two real records. This 30-second check catches the errors that are invisible at the field level because every individual value looks fine.

Remember the count is data rows, which is total lines minus one for the header. A file that reports one more record than you expect almost always has a stray footer or a duplicated header hiding in it.

10. Preview a sample before you commit the upload

Open the first 10 and last 10 rows and read them as a human. The bottom rows are where footers, totals, and encoding artifacts hide; the top rows are where preamble and header problems live. Confirm the columns line up with their headers, dates look uniform, numbers have no stray symbols, and no value has bled into the wrong column. A 60-second read of the edges of the file catches more than any single automated rule.

This is where a before/after preview with detected column types earns its keep. Seeing that a column you expect to be a date is being detected as text tells you a formatting problem exists before the destination ever sees the file. PipeSheets shows this preview with inferred types on both the original and cleaned versions, so you catch a column that will not parse while you can still fix it.

11. Check file size and encoding limits for the destination

Destinations impose hard caps that reject a file before any row is even read. These are worth knowing because the fix is usually 'split the file', not 'clean the data'.

12. Keep an error export of rejected rows

When an importer does give you a report of skipped or failed rows, save it. That rejected-rows export is the fastest map to what is wrong: it tells you which specific records and which specific columns tripped the validation, so you fix those rows and re-import only them instead of re-processing the whole file. Row-level detail is the entire point of this exercise, because 'file failed to import' tells you nothing and 'row 212: invalid date, row 288: duplicate email' tells you exactly what to do.

The condensed master checklist

Automating the boring half of the checklist

Checks 1 through 8 are mechanical and repetitive, which makes them a good fit for a saved cleaning step you run every time instead of eyeballing by hand. The Quick Clean preset in PipeSheets handles a chunk of the list in one click: it trims whitespace from every column, standardizes assorted null spellings (N/A, null, NULL, None) into true blanks, removes fully empty rows and columns, and normalizes headers. From there you can build a saved pipeline for the destination-specific parts, such as find-and-replace to convert a date column to one format or map inconsistent status values to a single spelling, then preview the before and after with detected types before exporting a clean UTF-8 CSV or XLSX.

The checklist stays the same no matter where the file is headed. What changes is the required column names, the accepted date format, and the size cap, so keep the destination's official import documentation open for those three details and let the preflight routine catch everything else. Fix files at the row level before upload and you stop trading vague failures for specific, one-time fixes.