Menu
I am loading data from text file to mysql table using following query:
LOAD DATA INFILE "myFile.csv"
INTO TABLE some_table
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n' (admin,consumer);
But when my file contains some error lines it is not able skip that line and the whole process stops at that place. I mean if my file format like :
---------- sankr : kumar ---------- ramesh:rao ---------- new users add here ---------- sri : vennla ---------- anu : bhavya ----------
I have to load by skipping the line "new users add here". How can do this?
349
The IGNORE number LINES clause can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip an initial header line containing column names: LOAD DATA INFILE '/tmp/test.
mysql> LOAD DATA LOCAL INFILE '/path/pet. txt' INTO TABLE pet; If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this statement instead: mysql> LOAD DATA LOCAL INFILE '/path/pet.
The following are steps that you want to import data into a table: Open table to which the data is loaded. Review the data, click Apply button. MySQL workbench will display a dialog “Apply SQL Script to Database”, click Apply button to insert data into the table.
LOAD DATA (all forms) is more efficient than INSERT because it loads rows in bulk.
The keyword you're looking for is IGNORE.
As in:
LOAD DATA INFILE "myFile.csv" IGNORE
INTO TABLE some_table
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n' (admin,consumer);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
