phpMyAdmin rejects a CSV import with Invalid column count on line 1
Invalid column count in CSV input on line 1.Short answer
phpMyAdmin counts the fields in each CSV line and compares them with the columns it expects. By default it expects every column of the table. The error means the counts differ: the separator or line ending in the form does not match the file, or the table has more columns than the file.
Fix it fast
1Find out which cause you have
Compare the number of columns in the table with the number of fields on the first line of the file.
SHOW COLUMNS FROM my_table; -- and on the command line: head -n 2 data.csv2Match what you see, then jump to its fix
- 1
Line 1 fails and the file uses semicolons or tabs, or has unusual line endingsThe separator, enclosure or line ending in the import form does not match the fileFix - 2
Every line fails; the file has fewer fields than the table has columnsThe table has more columns than the file (for example an id column)Fix - 3
The table has an id with AUTO_INCREMENT and you do not use a column listAn auto-increment column must be filled for each recordFix - 4
You want to see which field count MySQL itself expectsThe same file through LOAD DATA, where columns are named explicitlyFix
- 1
What does the error look like?
Invalid column count in CSV input on line 1.
Cause 1 of 4
The separator, enclosure or line ending in the import form does not match the file
1Check
The importer reads the separator, enclosure, escape and line-terminator settings from the import form. If the separator is wrong the fields merge or split, and if the line terminator is wrong a whole file can be read as one record.
head -n 1 data.csv | cat -A | head -c 3002Fix
Set the form values to what the file really uses: the separator character, the quote character and the line terminator. Leave the line terminator on its automatic value when you are unsure.
separator: ;
enclosed by: "
line terminator: auto3Verify
Import again with the corrected values.
-- check row count after the import
SELECT COUNT(*) FROM my_table;Confirmed by raw.githubusercontent.com
Cause 2 of 4
The table has more columns than the file (for example an id column)
1Check
When the Column names field is empty, phpMyAdmin expects one value for every column of the table. A table with an auto-increment id and a CSV without ids does not match.
SHOW COLUMNS FROM my_table;2Fix
List only the columns the file contains, separated by commas and without quotes, in the Column names field of the import form.
Column names: name,email,created_at3Verify
Import again; the error should no longer appear.
SELECT * FROM my_table ORDER BY id DESC LIMIT 5;Confirmed by raw.githubusercontent.com
Cause 3 of 4
An auto-increment column must be filled for each record
1Check
The phpMyAdmin documentation says that for a table with an auto_increment field, the value in each CSV record should be 0 so that the field fills in correctly.
SHOW CREATE TABLE my_table;2Fix
Either use the Column names field as above, or add a first column of 0 values to the file.
0,Ana,ana@example.com
0,Ben,ben@example.com3Verify
After the import, the id column should have new numbers.
SELECT id, name FROM my_table ORDER BY id DESC LIMIT 5;Confirmed by docs.phpmyadmin.net
Cause 4 of 4
The same file through LOAD DATA, where columns are named explicitly
1Check
MySQL's LOAD DATA expects one field per table column unless you give a column list, and gives an error for too few or too many fields in strict mode. Defaults are tab-separated fields and newline-terminated lines unless you set them.
SHOW COLUMNS FROM my_table;2Fix
Name the columns and the separators explicitly, and skip a header line.
LOAD DATA INFILE '/path/to/data.csv' INTO TABLE my_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, email, created_at);3Verify
The statement reports rows affected and warnings.
SHOW WARNINGS;Confirmed by dev.mysql.com
How do I stop it coming back?
- Export the CSV with the same separator and quote settings every time.
- Keep the Column names list for each table next to the CSV.
- Test the import on a copy of the table first.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix