Joining an Uploaded CSV with data from a query that uses postgresql DB data

I need to Left join 2 tables, one is part of our normal DB (postgresql) and the other is an uploaded CSV file. the csv file data structure always appears with a numerical array above the column keys.
eg
CSV_Table
-0 2keys
-name
-user_id
-1 2keys
-name
-user_id
-2 2keys
-name
-user_id
-and so on

the table to left join to is structured normally
query1_SQL_Table
-name
-users_id

How would I go about joining these 2 datasets? is there a ways to format the uploaded CSV to be in the correct format. It displays normally on a table... I have tried uploading it as an xlsx, using google sheets and other things but always get the same result

I can display both tables at once if I select * form {{query1_SQL_Table.data}}, {{upload1.parsedvalue}},

but when you {{upload1.parsedvalue.name}}=undefined
{{upload1.parsedvalue['0'].name}} = gives you the name under the name key in that 0 entry
but i want all names from all entries 0-500

Hey @Mintbricks!

Happy to help here! I'm having a little trouble visualizing the structure of your CSV file, would you mind sharing a screenshot and/or example file?

Thank you for responding!
I actually figured it out I had to formatDataAsArray(query.data) and then it all worked as intended

Oh awesome, that's great to hear! :slight_smile:

I'm having the same issue. It would be greatly appreciated if someone could offer some guidance.

I have a .csv upload with two columns (primary_key, value). This is uploaded by the user in order to update the database with the new value given in the .csv file

Then there's an SQL query that is pulling the same primary_key along with a few other columns. The issue is that the "value" column is pulling whatever value is selected in the .csv. So every row in the table has the same value instead of pulling for the primary_key value

Please let me know if any other information would be helpful

I'm no expert, but I had to do something similar

  1. make sure the switch in the uploader switch is set to parse files.

  2. I created a sql query called upload to "hold" the uploaded data, and executes with input changes

select * from {{(fileButton1.parsedValue['0'])}}

(this may not be necessary but it worked.)

  1. reference the above query in your query that joins instead of the fileButton.
    (
    select
    xx.value,
    ud.value,
    xx.primary_key,
    ud.primary_key
    from database.data as xx
    left join {{upload.data}} as ud on ud.primary_key = xx.primary_key )

There is likely a better way to do this but that is what worked for me

Thanks so much for circling back to share this solution, @Mintbricks! Sounds like you're doing this with the Query JSON with SQL resource type. (Query JSON with SQL | Retool Docs may be a useful doc for anyone else looking at this thread :slight_smile: ) It might even be possible to do what you described with one query, but the above should work great! :bowing_man: Appreciate you sharing so others who find this thread can follow along