Insert data into MariaDB table in bulk via csv

[Katunga]

Hello!
I am currently struggling to insert data into my DB.
I have an empty table which automatically adds the ID for each new entry that I make.
I now want to add data to said Table Keyword, which only has two columns, them being keyword_id and name (Im rather new to sql and databases but I am aware that these names might not be proper etiquette). I prepared the data I want to insert into the table in MS Excel, converted the file into a csv. format, uploaded it into a table and am now trying to insert said data into my DB.
I already tried to use the gui bulk function of retool but I cannot seem to get the array format right as the table.data returns an additional entry which I initially didnt enter:

I also tried to enter the data without the ID but the issue, being the "" entries, kept persisting.

I also used a the map function to get just the keywords but I still received an error. Upon doing my research it semmed that the array entries were objects and not strings but I don't know how I could vonvert them to strings.

My next attempt was to enter the data one by one by iterating through the table. This not only took a long time but also yielded no results as I dont know how to set up the query and trigger it via additional scopes to enter the data:

const keywords = table1.data;

// Loop through each row and trigger the insert query for each keyword
for (let i = 0; i < keywords.length; i++) {
   await keyword.setValue(keywords[i].Keyword); // Assuming each row has a "Keyword" column

  console.log(await keyword.value)
   //Trigger the insert query for each keyword
  await bulkinsert.trigger({
    additionalScope: {
      keyword: await keyword.value // Pass the keyword into the query
   }
  });
}

I get the error:

My query looks as follows:

INSERT INTO Keyword
VALUES ({{ keyword.value }})

I am at a loss.
On why I only want to enter the keyword: As the id automatically increments I figured just entering the name would suffice.

Thank you for our help in advance!

[ali-sajjad-rizavi]

@Katunga

table1.data

The extra column you're seeing : "", you might have accidentally added an extra custom column in the table's inspector settings.

But you can choose only the columns that you want to pick using;

table1.data.map(x => _.pick(x, ['Keyword', 'keyword_id']))

And use this in your bulk upload.

[Katunga]

Hello!
Thank you that worked swimmingly!

I would like to update in bulk as well by a similiar method.
I have an Expert table with expert_id as its primary key. This table also contains a column department where I made a mistake and would like to correct that.
I now have the correct associations in a table with just the expert_id as well as the department_id (department is another table). I get the following error:

My table looks as follows:

and the code in "Array of records to update" is:

{{  table1.data.map(x => _.pick(x, ['expert_id', "department_id"]))}}

Could you maybe help me there as well?

Additionally, should I mark your answer as solution, as it answers the inital inquiry or can you then not respond anymore?
Thanks in advance!

[ali-sajjad-rizavi]

Hi, sure. Let me try if I understand your problem correctly.

For the insert, according to what you said, it works.

table1.data.map(x => _.pick(x, ['Keyword', 'keyword_id']))

Now for updating data in bulk, you need to make the columns of your table editable (for only the columns that you would allow updating).

One you do that, you will be able to see the list of updates made to the table using table1.changesetArray. It will give you something like shown below, which always contains the primary key of the table, and the columns that were edited in each row.

[
  {
    "name": null,
    "id": 5
  },
  {
    "name": null,
    "id": 9
  },
  {
    "createdAt": "2022-05-10",
    "name": null,
    "id": 11
  }
]

Then, in the GUI mode of your SQL resource query, you should select "Bulk update via a primary key".