I want to load csv file into MySQL db. This file contains some fields in Postgres fields formatting, eg boolean is 't'. In MySQL db these fields has TINYINT(1) type as its boolean too. So what I want to do is convert Postgres 't' into 1 on the fly, while running LOAD DATA INFILE statement.

Is it a way to do this or I need to preprocess file before I can use it inside sql? Files that I need to process is a bit large so I want to avoid additional file operations on it.

LOAD DATA 
    INFILE '/load-data/photos.txt'
    INTO TABLE photos
    FIELDS TERMINATED BY '\t';

I want to load csv file into MySQL db. This file contains some fields in Postgres fields formatting, eg boolean is 't'. In MySQL db these fields has TINYINT(1) type as its boolean too. So what I want to do is convert Postgres 't' into 1 on the fly, while running LOAD DATA INFILE statement.

Is it a way to do this or I need to preprocess file before I can use it inside sql? Files that I need to process is a bit large so I want to avoid additional file operations on it.

LOAD DATA 
    INFILE '/load-data/photos.txt'
    INTO TABLE photos
    FIELDS TERMINATED BY '\t';

• mysql

• Input Preprocessing – Akina Commented yesterday

1 Answer 1

Load that column into a user variable, then you can process it in the SET clause to get the desired value.

LOAD DATA INFILE '/load-data/photos.txt'
INTO TABLE photos
FIELDS TERMINATED BY '\t'
(col1, col2, @boolean_col3, @date_col4, @ignored_col5, col6)
SET col3 = (@boolean_col3 = 't'),
    col4 = STR_TO_DATE(@date_col4, '%m/%d/%Y')

So you list all the columns that should be written directly from the CSV file, and use user variables like @boolean_col to process other columns to get the actual column value.