Importing a CSV looks like it should be the easy half of data work: no server, no schema, just text and commas. It is the half that produces the strangest support threads — a file that imports “successfully” and a column that quietly lost its leading zeros, a name that arrived as Caf?? Z??rich, a record that turned into two rows.
There are four distinct failures behind almost all of those threads, and they are told apart by what you see afterwards, not by the error message — most of them do not raise one.
| What you see | What actually happened | How to confirm it |
|---|---|---|
01234 became 1234, 0001234567 became 1234567 |
the column was created as a number | compare the first rows with the file |
| the run finished, row count is too high | a line break inside a quoted value split the record | open the file in a text editor that shows control characters |
Caf?? Z??rich, ?and? S.A. |
the text was decoded with the wrong codepage | look for a non-ASCII character before and after |
| “value too long for type character varying(n)” | the target column is narrower than the data | check the DDL of the created table |
1. When a zip code becomes a number
The classic: a column of five-digit codes. 01234 is a valid number in every database on earth, so if the target column is an integer the import succeeds and the zero is gone — no error, no warning, and the data is wrong.
Whether that happens is decided in one place: the types of the table you create. In FileToDB that table is generated from the file’s own content, and the types are visible and editable before anything moves:
zip and part_no come out as varchar(255), amount as numeric, shipped as timestamp. Leading zeros survive because nobody decided the column was a number.
The failure is what happens when a human “cleans that up” — the same dialog lets you type any type, and integer looks tidier:
Measured on the same four-row file, after that change:
order_id | zip | part_no
1001 | 1234 | 0001234567
1002 | 501 | 0009988776
1004 | 12 | 0000000001The import reported 4 records, and not one row errored. Part numbers survived only because the type was left alone (varchar(255)); had they been “cleaned up” too, the same thing would have happened to them. Barcodes, employee numbers and account numbers are all this failure.
The rule: look at the type column before the run, and give text-looking identifiers varchar. A value that keeps its leading zeros is text, whatever it is made of.
2. A comma, a quote, and a line break
A CSV is only simple until a field contains the delimiter. The standard answer is quoting — and it works, as long as the reader is told what the quote character is. The quoting settings in FileToDB default to ":
The second row of that preview contains has, comma and "quotes" inside and arrives as one column, not four.
The line break is the same problem with a worse default. A value with a newline inside is legal CSV; a reader that does not know it is possible treats the end of the line as the end of the record:
id,note
1,"line one
line two"
2,plainTwo records. Import that without the multi-line option and you get three, the second one being line two in the id column and nothing in note. The switch is Has Multi-Line Record on the Open Source page (off by default, because most files do not need it), and the same setting exists for the command line:
FileToDB.exe import --from=export.csv --to="PostgreSQL:user:pass@127.0.0.1:5432/db" --table=notes ^
--set=Load.SourceMultiLine=True3. Text that arrives as question marks
A .csv has no declared encoding. The bytes are one character each, and which character depends on the codepage the producing application used — cp1252 for a lot of Windows exports, UTF-8 for anything modern, cp936/cp1251 for machines set up in another region.
Get it wrong and the run still succeeds; you find out by reading the data:
name,city
Caf?? Z??rich,Z??rich
Fran?ois Fr?res,Besan?onMeasured on a cp1252 file imported on a machine whose ANSI codepage is 936: two records, no error, and the accented characters replaced. The import does not stay silent about it any more — the log now starts with:
WARNING: source encoding - no BOM in "cp1252.csv" and the first 131072 bytes are not valid UTF-8
(first bad byte at offset 16); assuming ANSI, the system codepage. If the text looks wrong, set the
encoding explicitly.That warning is the point: the last two failures cannot be seen in a row count, so the tool says which assumption it made. The fix is the Encoding field on the Open Source page — a list of named codepages (UTF-8, UTF-8BOM, ANSI, CP1252 (Western European), CP1251 (Cyrillic), CP936 (Chinese Simplified), ISO-8859-15, …). Pick the one the file was written in and the same bytes decode correctly; a file with a byte-order mark is read correctly without being asked.
4. Two smaller decisions worth making on purpose
Empty is not always NULL. An empty field in a text column arrives as an empty string; an empty field in a numeric column arrives as NULL. Both are reasonable, and they are not the same thing in a query (where note = '' versus where note is null).
Money is not a float. On PostgreSQL the amount column above is numeric, which is exact. On targets without a decimal type the same column becomes a floating-point type, where 0.10 may come back as 0.1 and sums stop matching to the cent. If the target has a fixed-precision type, set it.
5. The whole import, in three steps
FileToDB runs the whole import as one session of three screens:
- Open Source — pick the file, check the encoding, tick
Has Multi-Line Recordif the file has
line breaks inside values, and read the preview: it shows the first 100 rows and the total, so the zip codes and the quoted commas are visible before anything is written.
- Set Target — schema and table,
Load Type(Appendfor a new table), and theColumns Map:
- Create Table — the generated types (screenshot in §1), edited where the data needs it; then
Convert Data, which reports the row count and the elapsed time.
The command line runs the same session:
FileToDB.exe import --from=orders.csv --to="PostgreSQL:postgres@127.0.0.1:5432/shop" --table=ordersFAQ
Why did my leading zeros disappear?
The target column was created as a number. 01234 is a valid integer, so nothing fails — the zero is simply not part of the value. Create the column as varchar (or text) and re-import.
The import said 4 records but my file has 3. Why?
A line break inside a quoted value: the reader treated the second line as a new record. Turn on Has Multi-Line Record (or --set=Load.SourceMultiLine=True) and re-import.
Why do accented letters come out as question marks?
The file’s bytes were decoded with a different codepage than the one it was written in. The import warns when it has to assume; pick the matching encoding on the Open Source page. Files that declare their encoding with a byte-order mark are read correctly without a setting.
Can I import a file whose header row is missing?
Yes — leave Field Name In File Header unticked and the columns are numbered; rename them in Create Table, or map them on the Set Target page.
Is an empty CSV field NULL or an empty string?
In a text column, empty; in a numeric column, NULL. Decide before you write queries that compare against either one.
Can the import be scheduled?
Yes: save the task once in the GUI, then run the same session from the command line or the scheduler.
Trying it on your own file
Import a CSV, see the types and the text before the rows land, and fix them in the dialog instead of in SQL. Try FileToDB — Windows / macOS / Linux, one-time price. Moving a whole database rather than a file is DBCopier‘s job.