Adding CSV upload functionality and mapping columns to BigQuery table

Hi,

I'm trying to create an app in Retool that can read data from and write data to BigQuery.

Currently, I have a table called "allLeads" in my Retool app that contains data from BigQuery.

Now, I need to add functionality to upload CSV files and add the information from those files to the "allLeads" table, as well as to a separate table in BigQuery called "uploaded_on_retool".

The CSV files may have different formats, and the only required column is the VAT number, but all other columns are optional. I need to be able to map the columns from the CSV files to the columns in the BigQuery table and allLeads table.

Additionally, if a VAT number already exists in the "allLeads" table, I need to receive a notification asking if I want to replace the existing entry or leave it as is.

I've already added a fileButton and tried to use the "Bulk insert records" feature, but it is not adding new records to my BigQuery table.

Could you help me understand what steps I need to take to achieve my goal?

Thank you.

Hey @mondob!

Using fileInput.parsedValue[0] you should be able to get a JSON representation of your CSV as long as Parse files is turned on:

With that, you might try using a Query JSON with SQL query to map the columns with something like:

SELECT col1 AS alias1, col2 AS alias2 FROM {{ fileInput.parsedValue[0] }}

Does that sound like it could work?

If you prefer to use JavaScript something like this may also do the trick :thinking:

Thanks for your replay @Kabirdas

I've created fileInput button and turned on Parse files. I've also created query uploadLeads. Please see screenshots below:


With this I can upload files which has the exact same structure as my BigQuery table.

The problem is that I need to have option uploading files with different structure (different names of columns, even the number of columns might be different in each file).
I need that when someone try to upload file they get pop up or something where you see all columns that are in the file that you are uploading, and then you can match each column from that file to column in BigQuery table. And if there are columns that exist in BigQuery, but aren't in uploaded file, than all those columns get value "-".

Retool is new tool for me, so I'm not sure if I understand your replay right, but I don't know if the solution that you provided might help me for this :thinking:

Thank you for the support :pray:

Having people assign the column names themselves is an interesting use case! I'm not sure what the simplest way to do that would be but if you can grab a list of column names from the uploaded data using Object.keys or something similar:

Then you can use that in a listview or table where people can potentially enter new names:

Doing the mapping itself from dynamic values is a bit tricky, but working off of the JS example linked above you can do something like the following:

const tableData = {{ fileInput1.parsedValue[0] }};
const columnNames = {{columnNames.value}};

//get mapped column names from textinput component
const columnMappings = {{columnMapping}};

//create an object where the keys are old column names and the values are new column names
const columnMappingObject = Object.fromEntries(columnNames.map((col, i) => [col, columnMappings[i].value]));

//maps column names in each row to new name
const keyMapper = (row) => _.mapKeys(row, (_, key) => columnMappingObject[key] || key);

return tableData.map(keyMapper);

Putting that all together gets something like this:

Let me know if that works! I've attached a sample app to import and play around with :slightly_smiling_face:
column_name_mapper (1).json

1 Like

Thanks @Kabirdas, this is exactly what I need.