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