Errors when uploading csv and inserting into a table

Queries and Resources
1

Hi,

I am trying to build an app whereby a user can upload a csv, the data in this csv is joined on a couple of existing tables in my database, and then that data is inserted into a different table in my database.
Here is the code.
INSERT INTO table_coinvestments (investor_id, company, shares, investment_amount)
SELECT
coinvestors.id,
c.id,
csv.shares,
csv.investment
FROM
{{fileButton1.parsedValue[0]}} csv
JOIN
table_coinvestors coinvestors ON
csv.first_name = coinvestors.first_name AND
csv.last_name = coinvestors.last_name
JOIN
table_companies c ON
csv.company = c.company;

I am getting persistent errors when attempting to run this; the console says that there was a syntax error at or near "$1" but i am not sure what that means. Does anyone have any ideas?

My code seems okay so I would guess it's something to do with how it interprets the parsed value from the csv upload. I just used a file button to upload the csv, and tested it by displaying the parsedValue in a table. It displays perfectly fine.

2

I changed the format of the query to "Query JSON with SQL" as I figured that would allow the program to interpret the json object.
However it is now giving an error stating "Cannot read properties of undefined (reading 'columns')". Any suggestions?

3

When you inspect the parsed CSV, what do you see? The approach works fine for me, for example:

image
image1053×805 69.2 KB

Is the parsed data the same data type as the tables you are trying to join to? Can you get the Query to run without the INSERT INTO?

4

Hello @adam1!

Were you able to inspect the parsed CSV data as @jg80 showed us in their screenshots?

If you are getting a 'cannot read properties of undefined' error you might either be trying to read the data from the wrong place or before it is parsed.

if you could provide some screenshots of the data once the CSV are uploaded and the query as well as a picture similar to jg80s I can help you further trouble shoot on how to access the columns data.

5

Thanks for the reply! I was able to get this working by referring to the csv columns in the order that they are listed in the csv. This seemed to get it working, I didn't realise that was a requirement. I think I had to switch it to Query JSON with SQL mode as well, I didn't realise you couldn't query a table in the db and a json object in the same query.

6

@adam1 Glad to hear you were able to get it working!