Insert data from CSV to mySQL DB

Hi,
I created the following flow to pass data from a csv to mySQL. The flow executes successfully.

However the data in the database is being added with missing information.

Below is a comparison of the information in the database when I use python to add the data and when I use n8n:

Can you help me understand what is going on?

Information on my n8n setup

  • n8n version: 0.209.4
  • Running n8n via desktop app

Hi @Lara_Bertini,

the MySQL node has some problems right now, like it won’t insert fields with a field name that contains a dash “-”, I guess there are also problems with accent symbols like è.

You can try to wrap all field names with special characters like you wrapped the data field like this:
grafik

The n8n team is already working on an overhaul of the MySQL node, I think this problem will be fixed with this MySQL node update. OneDrive excel import into Mysql Database - #7 by sirdavidoff

2 Likes

Thank you Felix! What about the field Date, I guess that one was supposed to work right?

Can you give us some example JSON which gets sent to the MySQL node?

Yes, for sure. Here it the JSON code referring to one row to be input in the DB:
{

“Data”:“26/01/2023 15:11:00”,

Email":"[email protected]”,

“Descri��o”:“P�gina Diagn�stico 1”,

“Origem”:“Highticket”,

“Meio”:“Site”,

“Retribui��o”:“Interesse”,

“A��o”:“Inscri��o”,

“Situa��o”:“Ganho”,

“P�blico”:“PF”,

“Nome_Funil”:“Diagn�stico Comercial”,

“Utm_source”:“fb_ads”,

“Utm_id”:“23852738669290007-23852738931060007-23852738931080007”,

“Utm_term”:“Instagram_Feed”,

“Utm_campaign”:“[CONVERS�O][APLICA��O][SESS�O ESTRAT�GICA][DESTRUINDO METAS]”,

“Utm_medium”:“[01][ENGAJAMENTO 180D][IG/FB][V1]”,

“Utm_content”:“[02][V�DEO][B1]”

}

Here is a picture:

Hi @Lara_Bertini,

In my case, I’ve to change the format of the date to the format my MySQL DB uses, I do not know if that format changes with locality, maybe @Jon or someone else knows more about that?

The weird part here is, that the MySQL node just does not save the date into the table, for me, it throws an error when the date is not in the right format.

I’ve made a sample workflow on how I convert the date in my workflows: (I’ve used your date string)

1 Like

Hey @Lara_Bertini,

If you just try and insert only the date field does that work? It could just be that there is some difference in the formatting, Can you share what what the schema is for the table you are using?

Hi Jon, thanks for helping me. Here is how I created my table:
CREATE TABLE eventos_giovanne_saraiva (
Data TIMESTAMP,
Email VARCHAR(255),
Dono VARCHAR(255),
Descrição VARCHAR(255),
Origem VARCHAR(255),
Meio VARCHAR(255),
Retribuição VARCHAR(255),
Ação VARCHAR(255),
Situação VARCHAR(255),
Público VARCHAR(255),
Nome_Funil VARCHAR(255),
Utm_source VARCHAR(255),
Utm_id VARCHAR(255),
Utm_term VARCHAR(255),
Utm_campaign VARCHAR(255),
Utm_medium VARCHAR(255),
Utm_content VARCHAR(255))

Here is the schema:

Hi, Felix! I´ve tried to use the node you indicated to correct Date & Time, and added a Set node to filter only Date to add to BD so I can isolate this problem particulary to be solved. The following workflow is executing properly, but the date is still not bein added to my mySQL DB:

Here is the output in the mySQL DB after running the workflow above:

Data is still null.

Felix, I noticed that even after using this block to transform the Date format, the output is still text, while my DB is set up to receive a timestamp. Isn’t this perhaps the problem?

How can I convert text to Date (timestamp format) in the n8n to send it to my mySQL DB?

Hi @Lara_Bertini,

there is no “timestamp” or “datetime” format, as the data is transferred with an SQL command, which is a string.
That means you have to convert a date object to an string which is in the format MySQL expects.
Usually that format is YYYY-MM-DD HH:mm:SS or as date 2023-01-26 15:11:00

In your workflow Example, you create a new string called “data” from the “Data” string in the “Set1” node.
Then you write the correct date format with the “Date & Time” node to the string called “data”.
But the field in your database is called “Data”, so the “Insert into MySQL” node uses the falsely formatted string from the “Data” string.

tldr: change the output string in the “Date & Time” node to “Data

Also, your schema should be ok.

I’ve just tried it again with your schema and my test workflow, when correctly formatting the date it gets inserted into the table.
The interesting part is, when the date is not correctly formatted, like in your last workflow I get an error:

Screenshot

I can’t replicate a case where it does not insert anything /:

I’ve also just saw that you are using an “old” version of n8n, maybe update that to the newest version.
nvm, there is no newer Version for n8n Desktop

EDIT:
your last workflow example does not forward any items which have fields, which match the fields of your table, because the “Set1” node is set to “Keep only Set”.

In this case, nothing gets saved to the table.

Uhul!! Thanks, Felix!! It worked, the problem was indeed the name of the variable Data.

I had used the set to isolate the date input even, just to correct this problem.

So now, the only thing that is missing in the workflow is to remove the special characters before putting it into the database, so it doesn’t input NULL.

Here what is happening now:

Here is the code:

Data is correctly input, but I´m still missing the text fields.

Is there any way I can remove special caracters in n8n?

I’ve just tested special characters in n8n with the MySQL node and that just does not work right now in n8n.
The n8n Team is already working on an overhaul for the MySQL node which should fix that in the near future.

In the meantime, just remove the special characters from the MySQL table and set the names without special characters in n8n with a set node.

How to fix the content of the data to display the right characters instead of question mark symbols , I’ve no idea.

EDIT: just tested this and you can disable “Read As String” in the “Convert To Spreadsheet” node, with that the special characters get parsed correctly, at least for me :slight_smile:

You still need to change the names in the database to field names without special characters, as that’s another problem.

Screenshot

Yes!! It worked, Felix!! Thank you so much!!
I´ve edited the mySQL table:
image

and also included a autogenerated ID:

I´ve updated all nodes as you guided:

1 Like

Very cool! Glad all works now :slightly_smiling_face:

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.