XLSX to CSV Converter in Retool

Community Show & Tell
, ,
1

Hey everyone! :tada:

I wanted to share a project I tackled over the weekend for a hackathon. The goal was to build an app based solely on a high-level description of its function, Build a Better File Converter for End Users. I decided to experiment by converting XLSX files to CSV format, since this was a new approach for me in Retool. I leaned on SQL procedures for the heavy lifting, which I know can get complex, but it allowed me to dynamically build insert statements based on validated table schemas. It all comes together after ensuring that the data is a perfect match for a target table in the database.

While a lot of this could be done with JS Queries in Retool (and there’s probably a simpler way to streamline it), I wanted to test what I could do with what I knew at the time. I’d love your feedback on ways to improve this process!

XLSX to CSV Conversion

This snippet converts a base64 XLSX file to CSV. You can probably skip some of this by using the parse feature of the drop file component.

if (!{{dropzone.value}} || {{dropzone.value.length}} === 0) {
  return "Please upload a file.";
}

const file = {{dropzone.value[0].base64Data}};

function base64ToArrayBuffer(base64) {
  const binaryString = window.atob(base64);
  const len = binaryString.length;
  const bytes = new Uint8Array(len);
  
  for (let i = 0; i < len; i++) {
    bytes[i] = binaryString.charCodeAt(i);
  }
  return bytes.buffer;
}

const binaryData = base64ToArrayBuffer(file);

const workbook = XLSX.read(binaryData, { type: 'array' });

const sheetName = workbook.SheetNames[0];

const csvData = XLSX.utils.sheet_to_csv(workbook.Sheets[sheetName]);

function cleanValue(value) {
  return value.replace(/^"(.*)"$/, '$1').trim(); 
}

const rows = csvData.split('\n');

const headers = rows[0].split(',').map(header => cleanValue(header));

const parsedData = rows.slice(1).map(row => {
  const values = row.split(',').map(value => cleanValue(value)); 

  if (values.length === headers.length) {
    const rowData = headers.reduce((acc, header, index) => {
      acc[header] = values[index] ? values[index] : '';
      return acc;
    }, {});
    return rowData;
  } else {
    return null;
  }
}).filter(row => row !== null); 

console.log(parsedData);

return parsedData;

Schema Validation

This part compares the CSV to my existing schema tables to ensure the data is valid for import.

const csvData = xlsxToCSV.value;  

if (csvData.length > 0) {
  const csvHeaders = Object.keys(csvData[0]);
  return csvHeaders;
} else {
  return "No data found.";
}

SQL Procedure Call

I designed a procedure to handle data inserts. You could handle this part differently, but I prefer using SQL for flexibility and comfort. Here’s how I call the procedure (feel free to replace formattedData with your parsed CSV data):

CALL DARK_MODE.INSERT_DATA_INTO_TABLE({{ JSON.stringify({
      TABLE_NAME: findMatchingTableSchema.data.matchingTable,
      DATAS: formattedData.value
}) }});

SQL Snowflake Procedure

This Snowflake stored procedure, insert_data_into_table, accepts a JSON string containing a table name and an array of data entries. The procedure dynamically constructs and executes INSERT statements to add each entry into the specified table. It performs basic validation on the input data, handles null values and string formatting, and ensures the transaction is properly committed. In case of any error, the transaction is rolled back, and an error message is returned. If this doesn't make sense then thats totally okay. I use these for work often and find it easier to use but I would recommend looking into these procedures as it can be a helpful tool in development.

CREATE OR REPLACE PROCEDURE insert_data_into_table(data VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
    const payload = JSON.parse(DATA);

    if (!payload.TABLE_NAME || !payload.DATAS || !Array.isArray(payload.DATAS)) {
        return 'Invalid input: missing table name or data';
    }

    snowflake.execute({sqlText: "BEGIN WORK;"});

    try {
        for (const entry of payload.DATAS) {
            const columns = [];
            const values = [];

            for (const [key, value] of Object.entries(entry)) {
                columns.push(key);

                if (value === null) {
                    values.push('NULL');
                } else if (typeof value === 'string') {
                    values.push(`'${value.replace(/'/g, "''")}'`);
                } else if (typeof value === 'number') {
                    values.push(value);
                } else {
                    throw new Error(`Unsupported data type for key '${key}': ${typeof value}`);
                }
            }

            const insertStmt = `
                INSERT INTO ${payload.TABLE_NAME} (${columns.join(", ")})
                VALUES (${values.join(", ")});
            `;

            snowflake.execute({sqlText: insertStmt});
        }

        snowflake.execute({sqlText: "COMMIT WORK;"});
    } catch (err) {
        snowflake.execute({sqlText: "ROLLBACK WORK;"});
        return `Error: ${err.message}`;
    }

    return 'Data inserted successfully!';
$$;
4 Likes