How to query database using uploaded CSV

Queries and Resources
,
1

I have created a file button where the user uploads a CSV with multiple records in it:
id,name
1,John
2,Carol
3,Mike

Once uploaded, the data is shown in a table so the user can view it.

I would the like to query a Redshift table using the records from the CSV and display the returned records in a table:

SELECT *
FROM redshift_table
WHERE id in (select id from CSV);

How can I return all the records from the database that match the IDs in the CSV to the table in the UI?

2

Hey Chad! Welcome to the forums :slight_smile:

Given that you're using Redshift, looks like we'll need to use syntax like this to search for all ids in a given array of ids:

SELECT * FROM users WHERE id IN ({{ [1, 2, 3].join() }})

In order to grab an array of ids (from a table column), we can do:

{{ formatDataAsObject(table1.data).id }}

So adding that all together...

SELECT *
FROM redshift_table
WHERE id 
IN ({{ formatDataAsObject(table1.data).id.join() }});

Let me know how this works for you :crossed_fingers:

https://docs.retool.com/docs/sql-cheatsheet

3

Hi Victoria, thanks for getting back to me. I haven't managed to get it to work yet - I'm not sure if I'm missing something.

I tried this query with IDs I know exist in the table but nothing is being returned:

SELECT *
FROM redshift_table
WHERE id IN ({{ [260963812937,260968000962].join() }});
4

Hey Chad! Just to double check, are your IDs stored as numbers or strings in your database? Super strange that it’s not working. I’d be happy to step in to test if that would be helpful :slightly_smiling_face: I’d just need the name of your redshift resource!

5

They're stored as strings actually, tried this as well:

SELECT *
FROM bi_silver.stg_churn_ui_test
WHERE msisdn IN ({{ ['260963812937','260968000962'].join() }});

I would greatly appreciate that, thank you. The resource's name is churn-ui-connection

7

Got it! It seems like this works for an array of strings:

SELECT * FROM users WHERE city = ANY ( {{ '{' + ['Walnut','Buffalo'] + '}' }} )

And prepared statements need to be enabled in the resource settings page!

8

Thank you Victoria, much appreciated!

For those interested, here is the final solution to my problem:

SELECT msisdn, account_number
FROM redshift_table
WHERE msisdn = ANY ({{ '{' + formatDataAsObject(table1.data).msisdn.join() + '}' }} )
9

Woohoo! So glad you found a solution that worked for you and thank you for posting it for others to see/use :slight_smile: