Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

phpMyAdmin rejects a CSV import with Invalid column count on line 1

ErrorInvalid 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.

  • Applies to phpMyAdmin 5.2, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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.csv
  2. 2Match what you see, then jump to its fix

    1. 1Line 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. 2Every 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. 3The 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. 4You want to see which field count MySQL itself expectsThe same file through LOAD DATA, where columns are named explicitlyFix

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 300

2Fix

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: auto

3Verify

Import again with the corrected values.

-- check row count after the import
SELECT COUNT(*) FROM my_table;

Confirmed by raw.githubusercontent.com

Did this fix it?

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_at

3Verify

Import again; the error should no longer appear.

SELECT * FROM my_table ORDER BY id DESC LIMIT 5;

Confirmed by raw.githubusercontent.com

Did this fix it?

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.com

3Verify

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.