load data stopped prematurely

Question

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

Answer 1

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.