mysqlutf-8countcharacter-encodingmysql-loadfile

load data stopped prematurely

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?

UPDATE

CREATE table sizetable ( 
    mispar_rechev numeric, 
    tozeret_cd numeric , 
    sug_degem text , 
    tozeret_nm text , 
    degem_cd numeric , 
    degem_nm text , 
    ramat_gimur text , 
    ramat_eivzur_betihuty numeric , 
    kvutzat_zihum numeric , 
    shnat_yitzur numeric , 
    degem_manoa text , 
    mivchan_acharon_dt text , 
    tokef_dt text , 
    baalut text , 
    misgeret text , 
    tzeva_cd numeric , 
    tzeva_rechev text , 
    zmig_kidmi text , 
    zmig_ahori text , 
    sug_delek_nm text , 
    horaat_rishum numeric , 
    moed_aliya_lakvish text , 
    kinuy_mishari text 
    );

Another try after the feedback

SHOW VARIABLES LIKE 'char%';

character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  utf8mb4
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    utf8mb4
character_set_system    utf8mb3
character_sets_dir  C:\Program Files\MySQL\MySQL Server 9.2\share\charsets\

i made a smaller version of the file (1000 rows). create new table call "sizetable2"

Columns:
mispar_rechev text 
tozeret_cd text 
sug_degem text 
tozeret_nm text 
degem_cd text 
degem_nm text 
ramat_gimur text 
ramat_eivzur_betihuty text 
kvutzat_zihum text 
shnat_yitzur text 
degem_manoa text 
mivchan_acharon_dt text 
tokef_dt text 
baalut text 
misgeret text 
tzeva_cd text 
tzeva_rechev text 
zmig_kidmi text 
zmig_ahori text 
sug_delek_nm text 
horaat_rishum text 
moed_aliya_lakvish text 
kinuy_mishari text

result- only 500 rows uploaded.same issue the plate no upload "07921274" and the rest isn't. any ideas?

Solution

  • Set the character encoding to CHARACTER SET utf8mb4:

    For further diagnosing:

    Please learn about MySQL datatypes: