I have a table with the following fields:
CREATE TABLE text (
drawing INT NOT NULL,
blockID INT NOT NULL,
entityID INT NOT NULL,
style INT,
txt VARCHAR(255) NOT NULL,
attrib INT);
My csv file contains the data:
19 1CB2 E49 2 CLIENT MODULAR 1C2A
19 1CB3 E4B 2 CLIENT UG - 2 MODULAR PILOT PLANT 1C2C
19 1CB4 E4C 2 100 - 500 MICRON 1C2D
19 -1 E50 2 USERNAME 1C31
19 1CBA E51 2 15.8.2020 1C32
19 1C16 E58 2 PLANT 1C39
I'm using the following SQL to import the CSV file:
LOAD DATA INFILE '/tmp/_P2.8Q9nJ4_/text' INTO TABLE text (drawing,@blockID,@entityID,style,txt,@attrib) SET blockID=UNHEX(@blockID),entityID=UNHEX(@entityID),attrib=UNHEX(@attrib);
But this is the result:
+---------+---------+----------+-------+-------------------------------------------+--------+
| drawing | blockID | entityID | style | txt | attrib |
+---------+---------+----------+-------+-------------------------------------------+--------+
| 19 | 0 | 0 | 0 | CLIENT MODULAR | 0 |
| 19 | 0 | 0 | 0 | CLIENT UG - 2 MODULAR PILOT PLANT | 0 |
| 19 | 0 | 0 | 2 | 100 - 500 MICRON | 0 |
| 19 | 0 | 0 | 2 | USERNAME | 0 |
| 19 | 0 | 0 | 2 | PLANT | 0 |
| 19 | 0 | 0 | 2 | 15.8.2020 | 0 |
What is the correct way to import a CSV file into my table?