Importing a CSV file with hex data.

[davecoventry]

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?

[rproffitt]

My first thought is that's not the hexidecimal format MySQL uses.
Reference: https://dev.mysql.com/doc/refman/5.7/en/hexadecimal-literals.html

Try it using the format noted in the link.

Also, line 4 in the CSV has a -1 so you may want to think about that one.

[Dani]

The MySQL function UNHEX() "interprets each pair of hexadecimal digits as a number and converts it to a character". However, you're trying to store those characters in MySQL integer columns. Store them as varchars instead.

[davecoventry]

Thanks: That's helpful.

I was using this suggestion in my solution.

It looks like the simplest would be to trash my database and redesign my tables.