LOAD DATA INFILE MySQL(MariaDB)-CodePudding

I am trying to load a .csv file into MariaDB but I am struggling with the query. Here is how the.csv file is formatted:

USER    DATE    TIME    TESTRESULT  ERRORCODE
Esa_Test    16.5.2022   12:36:59    Fail    1(MinMaxError)
Esa_Test    16.5.2022   12:38:02    Fail    1(MinMaxError)
Esa_Test    16.5.2022   12:55:40    Fail    1(MinMaxError)
Esa_Test    17.5.2022   16:15:00    Fail    1(MinMaxError)
DPHYD_Ate   18.5.2022   9:50:11 OK  0(NoError)

When I use this query:

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\xampp\\mysql\\data\\test\\log2.csv' IGNORE INTO TABLE `test`.`testova2` IGNORE 4 LINES (`USER`, `DATE`, `TIME`, `TESTRESULT`, `ERRORCODE`);

The data is loaded successfully but with spaces like this:

USER;DATE;TIME;TESTRESULT;ERRORCODE
 E s a _ T e s t ; 1 6 . 5 . 2 0 2 2 ; 1 2 : 3 6 : 5 9 ; F a i l ; 1 ( M i n M a x E r r o r ) 
 E s a _ T e s t ; 1 6 . 5 . 2 0 2 2 ; 1 2 : 3 8 : 0 2 ; F a i l ; 1 ( M i n M a x E r r o r ) 
 E s a _ T e s t ; 1 6 . 5 . 2 0 2 2 ; 1 2 : 5 5 : 4 0 ; F a i l ; 1 ( M i n M a x E r r o r ) 
 E s a _ T e s t ; 1 7 . 5 . 2 0 2 2 ; 1 6 : 1 5 : 0 0 ; F a i l ; 1 ( M i n M a x E r r o r ) 
 D P H Y D _ A t e ; 1 8 . 5 . 2 0 2 2 ; 9 : 5 0 : 1 1 ; O K ; 0 ( N o E r r o r )

I tried to define some "limits" via FIELDS TERMINATED BY '\t' LINES TERMINATED BY '|' in the query but not working. The original file is with encoding UTF16LE according to notepad Please help me to build the proper query for my case in order to insert the data correctly..

CodePudding user response：

If someone looking at this: I manage to find a solution. The problem was the encoding. The solution was to make a short Python script to change the encoding before the upload in MySQL the code is:

import codecs
with codecs.open("input.csv","r",encoding="utf_16") as fin:
    with codecs.open("output.csv","w",encoding="utf_8") as fout:
        fout.write(fin.read())

I automated this and now everything is working.