I exported data from a view in my workbench to upload this data into my new table on other database, but when I try to upload this data I am getting this error below:
- Row import failed with error: ("Incorrect integer value: '' for column 'id_genero_musical' at row 1", 1366
What I did change this value '' to NULL, but when I put NULL value on CSV file, it is showing:
- Row import failed with error: ("Incorrect integer value: 'NULL' for column 'id_genero_musical' at row 1", 1366
But my file there is NO quotes, my file is as lines below:
4,"Cultura",NULL,"FM",102.5,0,NULL,NULL,"http://www.cultura.fm.br/","",NULL,16,"PR",2969,"Cultura",5,NULL
6,"Abdallah",NULL,"FM",104.1,55,NULL,NULL,"http://www.abdallahfm.com.br/","http://server3.webradios.com.br:9338/9338","57dbac33ef411",16,"PR",2911,"Abdallah",5,3
7,"Aline",NULL,"FM",93.7,0,NULL,NULL,"http://www.alinefm.com.br","http://controleflash.omegasistemas.net:8356/;stream.mp3","57dbac367dbbc",16,"PR",3147,"Aline",5,3
8,"Amiga",NULL,"FM",101.3,40,NULL,NULL,"http://www.amiga101.com.br","http://stream4.painelvox.com.br:7052","57dbac337965d",16,"PR",2878,"Amiga",5,3
9,"Caiuá",NULL,"FM",103.5,80,NULL,NULL,"http://www.caiuafm.com.br/","http://stream1.painelvox.com.br:6946/","57dbac352b125",16,"PR",3015,"Caiuá",5,1
I am using import button on MySQL Workbench and the database is MariaDB
I spent 2 or 3 days trying to solve that, without success, please help me.
Thank you guys!!
Edit 1:
Workbench is assuming that NULL value is a text, not a NULL value, how can I handle that to import a CSV file assuming that it is a real NULL value?
I don't use mysql workbench import wizard because ,frankly its nasty, and I have never found a way to set OPTIONALLY ENCLOSED BY or LINES TERMINATED BY. You might want to try using
LOCAL INFILE <filepath> INTO TABLE <schema.table>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
(id_emissora, ds_nome, id_tipo_emissora, ds_modulacao, ds_frequencia, nr_alcance,
perc_alcance, qt_habitantes_alcance, ds_url, ds_streaming, ds_id_anatel, id_uf, ds_uf, id_municipio, ds_municipio, id_regiao, id_genero_musical);
And watch out for encoding issues if you are on a nix box LINES TERMINATED BY '\r\n' might need amending to LINES TERMINATED BY '\n'
This load works fine for me on win10, mariadb