MySQL ignoring fields in a csv file after LOAD DATA statement

Question

I am loading a local csv for a table in MySQL. The table has 6 columns (or fields), but, after I load the data, only the message_date column is filled. In other words, the other 5 columns in the table are all NULL, after the LOAD DATA run. Why? I need to SET all of the 5 columns to bring them to my table?

Detail: When I do not SET any variable, just import them all as character fields, all of the columns appears. So it looks like is something after the IGNORE statement that is affecting all of the other 5 columns.

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/DadosBrutosEventTracks.csv'
    INTO TABLE messages
    FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (@var1, @var2, @var3, @var4, @var5, @var6)
    SET 
    message_date = STR_TO_DATE(SUBSTRING(@var3, 1, 22), "%Y-%m-%d %H:%i:%s.%f");

Why? You load into either the column or the variable. not into both. Use (column1, column2, @var3, column4, column5, column6) SET message_date = STR_TO_DATE(SUBSTRING(@var3, 1, 22), "%Y-%m-%d %H:%i:%s.%f"). — Akina
– Akina, Commented Apr 12, 2021 at 16:42

Bill Karwin · Accepted Answer · 2021-04-12 16:39:25Z

2

You only load data into variables. Therefore no data goes into any columns. Loading data into variables happens instead of loading into columns.

(@var1, @var2, @var3, @var4, @var5, @var6)

Then you set one column:

SET 
message_date = STR_TO_DATE(SUBSTRING(@var3, 1, 22), "%Y-%m-%d %H:%i:%s.%f");

The other columns are not mentioned in the SET clause, so they get no data.

answered Apr 12, 2021 at 16:39

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL ignoring fields in a csv file after LOAD DATA statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related