Error while importing CSV file in database

Question

I try to import a CSV file in my database, I don't have any error but no rows are insert

I checked my field name

My table structure is:

CREATE TABLE IF NOT EXISTS `tmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and my CSV looks like:

id;test
1;11
2;22

The command I use to import the file is LOAD DATA LOCAL INFILE '.../test.csv' INTO TABLE tmp FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 ROWS

It should import data into the tmp table, but just return No error, 0 row inserted

Answer 1

It could be because you did not specify the column to add the data in or the command "enclosed by". You could try adding those 2 lines you your code as shown below:

LOAD DATA INFILE '/path/to/test.csv'
INTO TABLE tmp
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id,test);

I used this article's step as reference for importing csv, you could refer to it for additional detail and explanation: https://blog.terresquall.com/2021/11/importing-a-csv-file-into-an-sql-table/

If your CSV is in a notepad like mine during testing, a "data truncated" warning will appear because there is a newline character at the end of each line. It can be ignored as it does not affect the results.