Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
 
If you can't change the CSV file to contain something other than the string 'False', then the approach outlined by eggyal is the only way to get the LOAD DATA command to do a translation. –  spencer7593 Jul 12 '13 at 17:06
 
@spencer7593: I wouldn't say the only way... one could also, for example, load the data into a temporary table with string-type columns, then use INSERT ... SELECT to copy across translated data. –  eggyal Jul 12 '13 at 17:07
 
@eggyal: of course you are right. But as far as getting the LOAD DATA statement to read string values of 'False' and 'True' from a .CSV file and get that inserted as a 0 or 1 into a tinyint column, the only way I know to get that to work is with an expression, such as in your example, though for the benefit of future readers, I would be prone to write SET col = IF(@var1='False',0,1) –  spencer7593 Jul 12 '13 at 17:14
add comment

1 Answer

up vote 2 down vote accepted

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'.

share|improve this answer
 
I was thinking along the same lines - what would a conditional statement look like for the dummy_variable? Essentially, I'd need the dummy_variable to be 0 if False, and 1 if True –  Trewq Jul 12 '13 at 17:12
 
@Trewq: "In your case you could do something like SET boolean_column := @dummy_variable = 'True'." –  eggyal Jul 12 '13 at 17:12
 
wouldnt this force the dummy_variable to be true at all times? This could be false also. –  Trewq Jul 12 '13 at 17:13
 
@Trewq: Ah, no. Rather confusingly, MySQL's = operator is used both for assignment and equality comparison depending on context. In the above context it is equality comparison, returning 1 if the operands are equal and 0 otherwise (which is a shortcut to precisely what you want). –  eggyal Jul 12 '13 at 17:15
 
@Trewq: that's equivalent to IF(@dummy_variable='True',1,0). –  spencer7593 Jul 12 '13 at 17:16
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.