self-hosted n8n (0.222.3)
Trying to use the mysql node to execute some sql using load local to load in csv file.
In MySql workbench, I have to add the parameter OPT_LOCAL_INFILE=1 to the advanced connection settings. see screenshot
This allows the load local sql statement to use a local .csv file on my (n8n) machine.
How do I add this to the connection string that the mysql node uses ?
This is the sql:
load data local
infile '/files/fred_customers.csv'
into table boospa.prestashop_customers
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY ';\n' STARTING BY ''
ignore 1 lines
(id_customer, customer_group, customer_language, @customer_company, customer_firstname, customer_lastname, customer_email, @customer_birthday, customer_newsletter, customer_optin, customer_creation_date)
set
customer_birthday = str_to_date(if(@customer_birthday='',null,@customer_birthday),'%Y-%m-%d')
,customer_company = if(@customer_company='NULL',null,@customer_company)
Current workaround is to copy the csv file to the mysql server and not use load local.
It’s would just be easier to use the local n8n machine though.
sample csv (i couldn’t attach the file to this topic ?)
id_customer;customer_group;customer_language;customer_company;customer_firstname;customer_lastname;customer_email;customer_birthday;customer_newsletter;customer_optin;customer_creation_date;
"10";"Client Boospa";"Français";"NULL";"Thierry";"PATERAC";"[email protected]";"2020-01-01";"1";"1";"2020-09-30 16:55:36";
"11";"Client Boospa";"Français";"NULL";"severine";"mion";"[email protected]";"";"0";"0";"2020-09-30 16:55:36";
"12";"Client Boospa";"Français";"NULL";"Arnaud";"GUYON";"[email protected]";"";"0";"0";"2020-09-30 16:55:36";
And this is the (mysql) error in n8n node as I am not using the OPT_LOCAL_INFILE=1 setting:
I would appreciate any guidance you can give.
Many thanks. Mark.