Bug #57176 Export to CSV should have option to output NULL as \N or NULL values
Submitted: 1 Oct 2010 16:05 Modified: 1 Oct 2010 17:49
Reporter: Thomas Johnson Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S4 (Feature request)
Version:5.2.28 OS:Any
Assigned to: CPU Architecture:Any

[1 Oct 2010 16:05] Thomas Johnson
Description:
It would be nice if the Export of a resultset from SQL Editor could ourput NULL if different formats.  Today it outputs it as null string.

Example:
select id, fldnull, fldString from test.test1;
Would produce:
1,,"MyString"

Where fldNULL was a null value.

My Suggestion is taht a preference be added so the user can declare how a NULL value is written on export.  Options include: empty string (current behavior), NULL, \N.
Then on the export popup that asks for a file name and path, have a dropdown whcih defaults to the prefered value but will allow the user to override it before exporting to one of the other values.

The reson for this request is I usally use the output for two reasons, the first to load into Excel.  The other to be used in a LOAD DATA LOCAL INFILE statement.  The current behavior works great for Excel, but not with Load Data.

Also if may be nice to add in teh preferences section whether to use a LF or CRLF combination for am export.  The old MySQL Browser would do a CR/LF, but SQL Editor does only LF.

Also a button to reimport data to a given table from a file would be VERY NICE (bascially performs step 5 shown in the "how to repeat")!

How to repeat:
use test;

CREATE TABLE `test`.`TEST1` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `fldnull` VARCHAR(50),
  `fldstring` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

insert into test.test1 values (1, null, "MyString");

now in SQL Editor:
1) execute: select * from test.test1;
2) choose to export file to a file (d:\mytest.csv).
3) execute: delete from test.test1;
4) exectue: select * from test1;
   validate there are no rows.
5) exectue: 
  LOAD DATA LOCAL INFILE 'd:/test.csv'
  INTO TABLE test.test1
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES;
6) exectue: select * from test1;
   validate the row was loaded, but the valud of fldnull is an empty string not a null value.

Suggested fix:
Add to preferences an option for the user to choose how to export NULL's.  Options include: empty string, NULL or \N

Add to preferences an option for the user to choose how lines are terminated: Options include: LF, CR/LF

Add a dropdown to teh export popup to allow the user to override the preference for NULL handling.  It should default to teh value in the preferences.

Modify export to use the chosen options (above).
[1 Oct 2010 17:49] Valeriy Kravchuk
Thank you for the feature request.
[1 Oct 2010 17:55] Peter Laursen
I'd like to add a comment here.

SELECT INTO OUTFILE and LOAD DATA are consistent in using "\N" representation for NULL. So it is consistent that WB does the same as SELECT INTO OUTFILE.

However is the file shall be used with another program than MySQL (a spreadsheet for instance) then both NULL and '' (empty string) could be considered.  Both '\N* and 'NULL' in a numerical column will cause trouble if you use spreadsheet-formulas!