Bulk Insert into MySql data base

Queries and Resources
1

Im trying to bulk insert data into MySQL data base , basically what they do in this tutorial but instead of posting through and API I'm trying to bulk insert records but I'm getting this error

image
image1500×740 82.8 KB

How can I upload data into MySQL data base by using a csv ?

2

Hi @danielhurtado, welcome to the community and happy to help.

With table1.data, make sure you have the same format as with the table's structure, i.e. the data types of each column are the same with your database table structure.

I am not sure with the error you have here but trying doing an UNNEST({{table1.data}}) and see if that helps.

Those two areas are what I could think of that could help solve this issue.

1 Like
3

Thanks for your response @jocen!! , Tried using UNNEST({{table1.data}}) and went from getting the error in the past post to getting this error:

image
image1517×278 23.3 KB

Im sure the data types on the table I'm inserting and the table in MySQL match, one thing I've noticed is dates are being shown as "Invalid Dates" on both my insert table and MySql table when displaying them on retool, don't know why but maybe lt can be an issue when inserting the data. If you know of anything else that could help I would really appreciate it and once again , Thanks for your response !

4

Hi @danielhurtado, ahh, it seems UNNEST is not the way to go since that seems to use records.map. Remove that and maybe try changing the data type of your date columns. As to how that is:

  1. Click your table and look for the date column you want to change.
  2. Change the Column Type to the same type of that in your database.

If it can't be done, you might want to do a JS transformer to align the data types before you bulk insert them in the database. I'm mostly touching the data layer and don't use insert quite often so apologies I provide limited help here.

5

Hey all, chiming in from support here :wave: @jocen thanks for your suggestions here! My suggestions are basically the same. @danielhurtado you'll want to make sure that the data you're attempting to insert matches the exact structure of your data in MySQL. To transform your data, I would suggest you use a JS transformer or a JS query. If you need to modify the dates uploaded from your CSV file, you can use Moment, which is accessible anywhere in Retool where you can write JavaScript. If you're running into issues please share screenshots of how your data is structured, how your dates are formatted, and how you are transforming them. We'll be happy to help :slightly_smiling_face: