Question
I am trying to load the data from CSV file to MYSql database through bulk insert option. Here are the below create table syntax and CSV file
CREATE TABLE discounts (
id INT NOT NULL ,
title VARCHAR(10) NOT NULL,
expired_date DATE NOT NULL,
amount VARCHAR(255 ) NOT NULL
);
CSV file format:
"475","Back","20140401","FFFF"
"476","bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb","20140901","DDD"
SQL Query :
LOAD DATA INFILE 'C:\Users\karthick\Desktop\data.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
In above create table syntax i have specified the column "title" data length to "10". But the value in data file for second row exceeds the length 10.
When i executed the SQL query the data are loaded successfully to MySQL database and here are the below output & My values in second row is getting truncated for the field "title". Could you please suggest how to stop loading the row without truncating it. Also it should load the next consecutive row without terminating if the data are appropriate. Please suggest
Database Output :
'475', 'Back', '2014-04-01', 'FFFF'
'476', 'bbbbbbbbbb', '2014-09-01', 'DDD'
Popular Answer
As per my understanding, below are few points that you want to achieve:
1) Data should not get truncated if title length is more than specified field length as per table structure.
2) If title length is more, then that record should get skipped while doing an importing of records & rest of the process should continue ahead.
Answer as per mysql database taken into consideration:
You can make use of sql_mode as TRADITIONAL (Make MySQL behave like a “traditional†SQL database system. A simple description of this mode is “give an error instead of a warning†when inserting an incorrect value into a column. Reference: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html)
Now after setting this mode, while doing records import, error will occur if any incorrect data or value out of range is getting to insert into table.
Next part, for out of range values their is no way to skip the error rows. You can check existing discussion link: Skip error lines while loading data to mysql table from delimited file
For skipping rows which are breaking unique constraints or possibly creating duplicate records, can be skipped using IGNORE keyword along with LOAD DATA INFILE.
Refer: https://dev.mysql.com/doc/refman/5.5/en/load-data.html