{"id":45864,"date":"2026-11-10T06:09:19","date_gmt":"2026-11-10T06:09:19","guid":{"rendered":"https:\/\/www.withdata.com\/blog\/?p=45864"},"modified":"2026-11-10T06:25:38","modified_gmt":"2026-11-10T06:25:38","slug":"csv-import-four-failures","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/filetodb\/csv-import-four-failures.html","title":{"rendered":"CSV Import: The Zip Code That Becomes an Integer, and the Text That Becomes Question Marks"},"content":{"rendered":"

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 \u2014 a file that imports “successfully” and a column that quietly lost its leading zeros, a name that arrived as Caf??<\/code> Z??rich<\/code>, a record that turned into two rows.<\/p>\n

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 \u2014 most of them do not raise one.<\/p>\n\n\n\n\n\n\n\n\n
What you see<\/th>\nWhat actually happened<\/th>\nHow to confirm it<\/th>\n<\/tr>\n<\/thead>\n
01234<\/code> became 1234<\/code>, 0001234567<\/code> became 1234567<\/code><\/td>\nthe column was created as a number<\/td>\ncompare the first rows with the file<\/td>\n<\/tr>\n
the run finished, row count is too high<\/td>\na line break inside a quoted value split the record<\/td>\nopen the file in a text editor that shows control characters<\/td>\n<\/tr>\n
Caf?? Z??rich<\/code>, ?and? S.A.<\/code><\/td>\nthe text was decoded with the wrong codepage<\/td>\nlook for a non-ASCII character before and after<\/td>\n<\/tr>\n
“value too long for type character varying(n)”<\/td>\nthe target column is narrower than the data<\/td>\ncheck the DDL of the created table<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

1. When a zip code becomes a number<\/h2>\n

The classic: a column of five-digit codes. 01234<\/code> is a valid number<\/em> in every database on earth, so if the target column is an integer the import succeeds and the zero is gone \u2014 no error, no warning, and the data is wrong.<\/p>\n

Whether that happens is decided in one place: the types of the table you create. In FileToDB<\/a> that table is generated from the file’s own content, and the types are visible and editable before anything moves:<\/p>\n

\"Create<\/p>\n

zip<\/code> and part_no<\/code> come out as varchar(255)<\/code>, amount<\/code> as numeric<\/code>, shipped<\/code> as timestamp<\/code>. Leading zeros survive because nobody decided the column was a number.<\/p>\n

The failure is what happens when a human “cleans that up” \u2014 the same dialog lets you type any type, and integer<\/code> looks tidier:<\/p>\n

\"The<\/p>\n

Measured on the same four-row file, after that change:<\/p>\n

order_id |  zip  |  part_no\n     1001 |  1234 | 0001234567\n     1002 |   501 | 0009988776\n     1004 |    12 | 0000000001<\/code><\/pre>\n
The import reported 4 records<\/code>, and not one row errored. Part numbers survived only because the type was left alone (varchar(255)<\/code>); had they been “cleaned up” too, the same thing would have happened to them. Barcodes, employee numbers and account numbers are all this failure.<\/p>\n
The rule: look at the type column before the run, and give text-looking identifiers varchar<\/code>. A value that keeps its leading zeros is text, whatever it is made of.<\/p>\n
2. A comma, a quote, and a line break<\/h2>\n
A CSV is only simple until a field contains the delimiter. The standard answer is quoting \u2014 and it works, as long as the reader is told what the quote character is. The quoting settings in FileToDB<\/a> default to \"<\/code>:<\/p>\n
\"Open<\/p>\n
The second row of that preview contains has, comma and \"quotes\" inside<\/code> and arrives as one<\/strong> column, not four.<\/p>\n
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:<\/p>\n
id,note\n1,\"line one\nline two\"\n2,plain<\/code><\/pre>\n
Two records. Import that without the multi-line option and you get three<\/strong>, the second one being line two<\/code> in the id<\/code> column and nothing in note<\/code>. The switch is Has Multi-Line Record<\/code> on the Open Source page (off by default, because most files do not need it), and the same setting exists for the command line:<\/p>\n
FileToDB.exe import --from=export.csv --to=\"PostgreSQL:user:pass@127.0.0.1:5432\/db\" --table=notes ^\n                    --set=Load.SourceMultiLine=True<\/code><\/pre>\n
3. Text that arrives as question marks<\/h2>\n
A .csv<\/code> has no declared encoding. The bytes are one character each, and which character depends on the codepage the producing application used \u2014 cp1252 for a lot of Windows exports, UTF-8 for anything modern, cp936\/cp1251 for machines set up in another region.<\/p>\n
Get it wrong and the run still succeeds; you find out by reading the data:<\/p>\n
name,city\nCaf?? Z??rich,Z??rich\nFran?ois Fr?res,Besan?on<\/code><\/pre>\n
Measured 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 \u2014 the log now starts with:<\/p>\n
WARNING: source encoding - no BOM in \"cp1252.csv\" and the first 131072 bytes are not valid UTF-8\n(first bad byte at offset 16); assuming ANSI, the system codepage. If the text looks wrong, set the\nencoding explicitly.<\/code><\/pre>\n
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<\/code> field on the Open Source page \u2014 a list of named codepages (UTF-8<\/code>, UTF-8BOM<\/code>, ANSI<\/code>, CP1252 (Western European)<\/code>, CP1251 (Cyrillic)<\/code>, CP936 (Chinese Simplified)<\/code>, ISO-8859-15<\/code>, \u2026). 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.<\/p>\n
4. Two smaller decisions worth making on purpose<\/h2>\n
Empty is not always NULL.<\/strong> An empty field in a text column arrives as an empty string; an empty field in a numeric column arrives as NULL<\/code>. Both are reasonable, and they are not the same thing in a query (where note = ''<\/code> versus where note is null<\/code>).<\/p>\n
Money is not a float.<\/strong> On PostgreSQL the amount column above is numeric<\/code>, which is exact. On targets without a decimal type the same column becomes a floating-point type, where 0.10<\/code> may come back as 0.1<\/code> and sums stop matching to the cent. If the target has a fixed-precision type, set it.<\/p>\n
5. The whole import, in three steps<\/h2>\n
FileToDB<\/a> runs the whole import as one session of three screens:<\/p>\n
    \n
  1. Open Source<\/strong> \u2014 pick the file, check the encoding, tick Has Multi-Line Record<\/code> if the file has<\/li>\n<\/ol>\n
    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.<\/p>\n
      \n
    1. Set Target<\/strong> \u2014 schema and table, Load Type<\/code> (Append<\/code> for a new table), and the Columns Map<\/code>:<\/li>\n<\/ol>\n
      \"Set<\/p>\n
        \n
      1. Create Table<\/strong> \u2014 the generated types (screenshot in \u00a71), edited where the data needs it; then<\/li>\n<\/ol>\n
        Convert Data<\/code>, which reports the row count and the elapsed time.<\/p>\n
        The command line runs the same session:<\/p>\n
        FileToDB.exe import --from=orders.csv --to=\"PostgreSQL:postgres@127.0.0.1:5432\/shop\" --table=orders<\/code><\/pre>\n
        FAQ<\/h2>\n
        Why did my leading zeros disappear?<\/h3>\n
        The target column was created as a number. 01234<\/code> is a valid integer, so nothing fails \u2014 the zero is simply not part of the value. Create the column as varchar<\/code> (or text<\/code>) and re-import.<\/p>\n
        The import said 4 records but my file has 3. Why?<\/h3>\n
        A line break inside a quoted value: the reader treated the second line as a new record. Turn on Has Multi-Line Record<\/code> (or --set=Load.SourceMultiLine=True<\/code>) and re-import.<\/p>\n
        Why do accented letters come out as question marks?<\/h3>\n
        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.<\/p>\n
        Can I import a file whose header row is missing?<\/h3>\n
        Yes \u2014 leave Field Name In File Header<\/code> unticked and the columns are numbered; rename them in Create Table<\/code>, or map them on the Set Target page.<\/p>\n
        Is an empty CSV field NULL or an empty string?<\/h3>\n
        In a text column, empty; in a numeric column, NULL<\/code>. Decide before you write queries that compare against either one.<\/p>\n
        Can the import be scheduled?<\/h3>\n
        Yes: save the task once in the GUI, then run the same session from the command line or the scheduler.<\/p>\n
        Trying it on your own file<\/h2>\n
        \n
        Import a CSV, see the types and the text before the rows land, and fix them in the dialog instead of in SQL.<\/strong> Try FileToDB<\/a> \u2014 Windows \/ macOS \/ Linux, one-time price. Moving a whole database rather than a file is DBCopier<\/a>‘s job.<\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
        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 \u2014 a file that imports “successfully” and a column that quietly lost its leading zeros, a name that arrived as Caf?? Z??rich, a […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2772],"tags":[13864,13863,219,2475,13774],"class_list":["post-45864","post","type-post","status-publish","format-standard","hentry","category-filetodb","tag-csv-encoding","tag-csv-import","tag-csv-to-mysql","tag-csv-to-postgresql","tag-filetodb"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45864","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=45864"}],"version-history":[{"count":5,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45864\/revisions"}],"predecessor-version":[{"id":45873,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45864\/revisions\/45873"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=45864"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=45864"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=45864"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}