Export from MySQL to csv and then import from the same file alters data

[Roman Semenov]

I have a table with a nullable column:

    CREATE TABLE test (id INT, value INT);

    INSERT INTO test VALUES (1, null);

I export it to csv using export button in web console. It generates the following query:

    SELECT * FROM `test`.`test`

    INTO OUTFILE '...' 

    CHARACTER SET 'utf8' 

    FIELDS TERMINATED BY ',' 

    OPTIONALLY ENCLOSED BY '"' 

    ESCAPED BY '"'

The output file looks like this:

    1,"N

Then I truncate table and import it, with import button in web console:

    LOAD DATA LOCAL INFILE '...' 

    INTO TABLE `test`.`test` 

    CHARACTER SET 'utf8' 

    FIELDS TERMINATED BY ',' 

    OPTIONALLY ENCLOSED BY '"' 

    ESCAPED BY '"';

And it changes all null INTs to 0, and null VARCHARs to `"N`. If I try to import this file manually using the same query I get the following warning:

    SHOW WARNINGS;

    Warning 1366: Incorrect integer value: '"N' for column 'value' at row 1

For some reason escaping doesn't work for this case. It does work if fields are escaped and enclosed by different symbols but there is no such option in export dialog. Am I doing something wrong?

[George (Cloud Platform Support)]

Hello Roman, 

If the FIELDS ESCAPED BY character is empty, as in your example, escape-sequence interpretation does not occur. Seen that no escaping occurs, your N is taken as a string, while the column data type is INT, resulting in error. You can gather more detail on escaping from the "13.2.6 LOAD DATA INFILE Syntax" documentation page for MySQL, at the level of the "FIELDS ESCAPED BY controls how to read or write special characters" sub-chapter. 

[Roman Semenov]

If the FIELDS ESCAPED BY character is empty, as in your example

It's not empty in my example! 

[Roman Semenov]

Also it doesn't change the fact that these 2 buttons are incompatible with each other:

Click Export, select CSV, then click Import, select the same file, and boom all NULLs are now silently (!) changed

[George (Cloud Platform Support)]

You are right, Roman. 

Our Developers are aware of this situation, and busy working towards a permanent solution. There is still no estimated date of completion for this task. 

Meanwhile, you may try a temporary solution: to use a field delimiter that is not present in your data, let's say '^': 

   SELECT * INTO OUTFILE '/tmp/foo.csv' CHARACTER SET 'utf8'   

   FIELDS TERMINATED BY '^' OPTIONALLY ENCLOSED BY '' ESCAPED BY "" 

      FROM table; 

     bq load --null_marker="\N" --field_delimiter="^" path/to/file/filenamehat.csv name:string,foo:string,bar:integer 

[Roman Semenov]

The problem is that SELECT ... INTO OUTFILE can export only to a local file on SQL server and this is obviously disabled for Cloud SQL instances - I get "access denied". Currently I have to export via web interface, replace escaped char with sed which is prone to errors and then load with LOAD DATA LOCAL INFILE.