converting true false string to boolean while using "load infile data"

Question

I needed to transfer data from MSSQL (Microsoft SQL Server) to MySQL, and the best option for me was to write a python script that exported data as csv from MSSQL and then import this csv into mysql. This process is working well for me so far (and I am not looking for another way to do this).

Datatype conversion from MSSQL to MySQL is done like so:

MSSQL_MySQL_MAP = {
    'nvarchar'  : 'varchar',
    'varchar'   : 'varchar',
    'bit'       : 'boolean',
    'smallint'  : 'smallint',
    'int'       : 'int',
    'datetime'  : 'datetime',
    'timestamp' : 'datetime',
    'ntext'     : 'longtext',
    'real'      : 'double',
    'image'     : 'BLOB',
    'float'     : 'float',
    'money'     : 'decimal',
    }

The exported csv is imported into MySQL using the following command..

"""LOAD DATA INFILE '%s/%s' REPLACE INTO TABLE %s FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'"""

I have a situation where there is a "bit" datatype in MSSQL, and the exported CSV contains a True of False string like this:

 22294,501,q4h,12             ,False,False,None,False,None,None,None,0,None,None

What is the best way to make MySQL understand that the True or False string is a boolean 1 or 0 and import it correctly? Currently, I get a following warning:

Warning: Incorrect integer value: 'False' for column 'system_code' at row 7

Is there a way to pass some parameters along with the load infile data that can accomplish this?

Answer 1

As documented under LOAD DATA INFILE Syntax:

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

The column list can contain either column names or user variables. With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.

User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

In your case you could do something like SET boolean_column := @dummy_variable = 'True'.