Create screen to import Excel/Csv spreadsheet

[thiagow]

Is it possible to create a screen to upload an excel or CSV file and later read its data? In short, I need to create a screen so that the user can import an Excel/CSV spreadsheet and read its data.

[melohagan]

Hi @thiagow

Short answer - no, there's currently no component for reading and displaying the contents of a CSV file within a client app.
With the future release of custom components, this will be possible to be built by dev users in the months ahead!

In the meantime, if you have access to AWS I tried out this solution with Lambdas that works ok.

Step 1- Add S3 Bucket Upload Component

Create a new S3 datasource:

The credentials can be obtained in a similar way to DynamoDB: https://docs.budibase.com/docs/dynamodb#setup

Add a form with an S3 File Upload component
Select the S3 datasource you created, and enter in the target bucket name in AWS as well as a file name for the upload.
Add an upload button with the Upload File to S3 action.

Step 2 - AWS Lambda

Create a new node project on your computer, and install the csvtojson library. There's a guide in this StackOverflow answer: https://stackoverflow.com/questions/59590490/aws-lamda-cannot-find-module-csvtojson

Add a new handler.js file to the root of the project with the following JavaScript:

const csv = require('csvtojson')
const aws = require('aws-sdk');

const s3 = new aws.S3({ apiVersion: '2006-03-01' });


exports.handler = async (event) => {
    //console.log('Received event:', JSON.stringify(event, null, 2));

    // Get the object from the event and show its content type
    let bucket = "";
    let key = "";
    if (event.queryStringParameters && event.queryStringParameters.bucket) {
        bucket = event.queryStringParameters.bucket;
    }
    if (event.queryStringParameters && event.queryStringParameters.key) {
        key = event.queryStringParameters.key;
    }
    const params = {
        Bucket: bucket,
        Key: key,
    };
    try {
        const stream = await s3.getObject(params).createReadStream();
        return await csv().fromStream(stream);
    } catch (err) {
        console.log(err);
        const message = `Error getting object ${key} from bucket ${bucket}. Make sure they exist and your bucket is in the same region as this function.`;
        console.log(message);
        throw new Error(message);
    }
};

Zip the contents of your project.
Create a new Lambda function in AWS and upload the zip file. It should look roughly like so:

You'll notice in the top right the Function URL. Copy that. We'll use this in Budibase later.
Also have a look at the Configuration tab. You will probably need to enable CORS:

Make sure the runtime settings are correct! So in this case it should be handler.handler

Step 3 - Add the REST Query and Table

Create a new REST Query with the following params. You can put in your own bucket and key.

Note the schema will need to be static for this to work. E.g. the users must be uploading CSVs that share column names, but the row contents can obviously be different.

Add a Data Provider for the REST Query source with a table underneath.
I've also added a button with the Refresh Data provider action so we can get the new data from a recently uploaded CSV.

Result

[melohagan]

Hey @sarper57 If you hit your lambda from Postman are you getting the same error response, or is it just an error within Budibase?

[sarper57]

Hi Melohagan, postman returned internal server error and 502Bad Gateway so I don't know what I am doing wrong?

[melohagan]

If there's an issue getting a response from your AWS lambda then it'd be beyond the scope of my help. There are a number of troubleshooting guides from Amazon around this. You'll need to make sure your config is right.

[sarper57]

Hi Melohagan
I hope you will be able to help me out, I have sorted the converting the CSV file to json with lambda python function and receiving the data via REST API. how do I insert these records to mySQL table?
thanks

[melohagan]

In this example, we're using the JSON from the REST API as the datasource. If you wanted to instead write rows to a MySQL then that could be done in your lambda as well, although this is getting out of the scope of Budibase.

[thiagow]

Obrigado pela resposta!
Vocês irão avisar os usuários quando liberarem o recurso que irá permite criar e incorporar novos componentes no Budibase?

[shogunpurple]

Olá @thiagow,

Sim, vamos avisar todos quando liberarmos - vai ser por volta do final de setembro.

Vamos publicar uma discussão comunitária quando estiver pronta.

Se possível, peço por favor que use inglês nas discussões para que seja mais fácil para a equipa responder. Obrigado!