mysql - load data stopped prematurely - Stack Overflow

admin管理员组

文章数量:1278789

I uploaded a file with 4 million rows to a table. The table is a database of vehicle license plates along with all the characteristics of that vehicle (color, tire size, engine capacity, and more).

LOAD DATA local INFILE
'C:/Users/yaron/Downloads/TIRE_SIZES.CSV'
INTO TABLE sizetable  
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
mispar_rechev,tozeret_cd,sug_degem,tozeret_nm,degem_cd,degem_nm,ramat_gimur,ramat_eivzur_betihuty,kvutzat_zihum,shnat_yitzur,
degem_manoa,mivchan_acharon_dt,tokef_dt,baalut,misgeret,tzeva_cd,tzeva_rechev,zmig_kidmi,zmig_ahori,sug_delek_nm,horaat_rishum,moed_aliya_lakvish,
kinuy_mishari
);

the problem only half of the rows were uploaded.

From a test I did using notepadd++ for comparison, I discovered that 95% of rows that was not added to the table -one row, above or below ,it has a vehicle plate (AKA first column) number that starts with 0.

table contains non-English words.

upload:

"74592003"|"0413"|"P"|"טויוטה יפן"|"0296"|"ZVG12L-KHXGBW"|"DYNAMIC"|"4"|"4"|"2024"|"2ZR"|"2024-05-28"|"2025-05-27"|"פרטי"|"JTNADACB90J020135"|"11"|"שחור מטלי"|"225/50R18"|"225/50R18"|"בנזין"|"221626"|"2024-5"|"COROLLA CROSS"

one row above not upload:

"04106885"|"0416"|"P"|"קיה סלובקיה"|"0120"|"PG81AB"|"URBAN"|"1"|"15"|"2017"|"G4NA"|"2024-01-25"|"2025-02-04"|"פרטי"|"U5YPG81ABHL203132"|"80"|"שנהב לבן"|"225/60R17"|"225/60R17"|"בנזין"|"160215"|"2017-2"|"SPORTAGE"

And also it happend whenever the number 0 ends the row /the right column (AKA last column)car model for example MAZDA CX-30not uploaded at all.

What could be the solution please?

---

I did it!* Thank you very much, I just used all the advice, finally the solution (of course) was the simplest : I didn't need to add that line:

OPTIONALLY ENCLOSED BY '"'

Thanks again

I uploaded a file with 4 million rows to a table. The table is a database of vehicle license plates along with all the characteristics of that vehicle (color, tire size, engine capacity, and more).

LOAD DATA local INFILE
'C:/Users/yaron/Downloads/TIRE_SIZES.CSV'
INTO TABLE sizetable  
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
mispar_rechev,tozeret_cd,sug_degem,tozeret_nm,degem_cd,degem_nm,ramat_gimur,ramat_eivzur_betihuty,kvutzat_zihum,shnat_yitzur,
degem_manoa,mivchan_acharon_dt,tokef_dt,baalut,misgeret,tzeva_cd,tzeva_rechev,zmig_kidmi,zmig_ahori,sug_delek_nm,horaat_rishum,moed_aliya_lakvish,
kinuy_mishari
);

the problem only half of the rows were uploaded.

From a test I did using notepadd++ for comparison, I discovered that 95% of rows that was not added to the table -one row, above or below ,it has a vehicle plate (AKA first column) number that starts with 0.

table contains non-English words.

upload:

"74592003"|"0413"|"P"|"טויוטה יפן"|"0296"|"ZVG12L-KHXGBW"|"DYNAMIC"|"4"|"4"|"2024"|"2ZR"|"2024-05-28"|"2025-05-27"|"פרטי"|"JTNADACB90J020135"|"11"|"שחור מטלי"|"225/50R18"|"225/50R18"|"בנזין"|"221626"|"2024-5"|"COROLLA CROSS"

one row above not upload:

"04106885"|"0416"|"P"|"קיה סלובקיה"|"0120"|"PG81AB"|"URBAN"|"1"|"15"|"2017"|"G4NA"|"2024-01-25"|"2025-02-04"|"פרטי"|"U5YPG81ABHL203132"|"80"|"שנהב לבן"|"225/60R17"|"225/60R17"|"בנזין"|"160215"|"2017-2"|"SPORTAGE"

And also it happend whenever the number 0 ends the row /the right column (AKA last column)car model for example MAZDA CX-30not uploaded at all.

What could be the solution please?

---

I did it!* Thank you very much, I just used all the advice, finally the solution (of course) was the simplest : I didn't need to add that line:

OPTIONALLY ENCLOSED BY '"'

Thanks again

• mysql
• utf-8
• count
• character-encoding
• mysql-loadfile

Share Improve this question Follow edited Feb 27 at 12:40 Mafic rock asked Feb 24 at 9:44 Mafic rockMafic rock 344 bronze badges 3

• the problem only half of the rows were uploaded When any error occures then the rows which are already processed stays imported. Execute your import via CLI into empty table. Look at error message(s). OPTIONALLY ENCLOSED BY '"' In provided sample rows all columns values are enclosed by dquotes - maybe it's safe to remove optionally? Also provide CREATE TABLE for sizetable. Does mispar_rechev is numeric-datatype column? Try to apply preprocessing for this column. – Akina Commented Feb 24 at 11:33
• @Akina - thanks , mispar_rechev created as a decimal. – Mafic rock Commented Feb 24 at 11:43
• Please check your structure, query and data. I see that 5th column in LOAD DATA is degem_cd, and the values for it in the sample rows are strings - but this column is defined as numeric. Also remember that the column which is defined as NUMERIC will be created as DECIMAL(10,0), which may cause an overflow or data truncation. – Akina Commented Feb 24 at 13:33

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Set the character encoding to CHARACTER SET utf8mb4:

• For the connection
• For the column(s) that can get Hebrew text
• For the LOAD DATA

For further diagnosing:

• SHOW VARIABLES LIKE 'char%';
• SHOW CREATE TABLE sizetable; (Instead of the CREATE you provided.)

Please learn about MySQL datatypes:

• INT, SMALLINT, etc (for counts, ids, etc)
• DECIMAL(m,n) (for money)
• FLOAT and DOUBLE (for measurements)
• TEXT (limited to 64K bytes) versus VARCHAR(nn) (for smaller things), etc.

本文标签： mysqlload data stopped prematurelyStack Overflow