How to Import Data into Retool

1-- Test query: verify data access
2SELECT *
3FROM information_schema.tables
4WHERE table_schema = 'public'
5LIMIT 10;
6
7-- Or a direct table query:
8SELECT id, name, email, created_at
9FROM users
10ORDER BY created_at DESC
11LIMIT 50;

1// File Picker component Inspector settings:
2// - File type: text/csv,text/plain
3// - Max file size: 5 (MB) — adjust based on your needs
4// - Multiple files: Off (for single CSV import)
5
6// In a text component, show the selected file name:
7{{ filePicker1.value?.[0]?.name ?? 'No file selected' }}
8
9// File Picker value structure:
10// filePicker1.value = [{
11//   name: 'users.csv',
12//   type: 'text/csv',
13//   base64: '...base64 encoded CSV content...',
14//   size: 12345
15// }]

1// JS Query: parseCSV
2// Triggered by 'Preview CSV' button
3
4const file = filePicker1.value?.[0];
5if (!file) {
6  utils.showNotification({
7    title: 'No file selected',
8    notificationType: 'warning',
9    duration: 3
10  });
11  return [];
12}
13
14// Decode base64 to text
15const base64Content = file.base64;
16const csvText = atob(base64Content);
17
18// Split into lines and parse
19const lines = csvText.split('\n').filter(line => line.trim());
20if (lines.length < 2) {
21  utils.showNotification({
22    title: 'Empty CSV',
23    description: 'File has no data rows.',
24    notificationType: 'warning'
25  });
26  return [];
27}
28
29// Parse header row
30const headers = lines[0].split(',').map(h => h.trim().replace(/"/g, ''));
31
32// Parse data rows
33const rows = lines.slice(1).map(line => {
34  const values = line.split(',').map(v => v.trim().replace(/"/g, ''));
35  const row = {};
36  headers.forEach((header, i) => {
37    row[header] = values[i] ?? '';
38  });
39  return row;
40});
41
42// Save to a temporary state variable for preview
43await parsedCSVData.setValue(rows);
44// Note: setValue() is async — do not read parsedCSVData immediately after
45
46return rows;

1// JS Query: importCSVData
2// Triggered by 'Confirm Import' button
3
4const rows = parsedCSVData.value;
5if (!rows || rows.length === 0) {
6  utils.showNotification({
7    title: 'No data to import',
8    notificationType: 'warning'
9  });
10  return;
11}
12
13// Process in batches of 100 to avoid timeouts
14const BATCH_SIZE = 100;
15let inserted = 0;
16let errors = [];
17
18for (let i = 0; i < rows.length; i += BATCH_SIZE) {
19  const batch = rows.slice(i, i + BATCH_SIZE);
20
21  try {
22    // Trigger the SQL insert query for this batch
23    await insertUsersBatch.trigger({
24      additionalScope: { batchRows: batch }
25    });
26    inserted += batch.length;
27  } catch (err) {
28    errors.push({ batch: i, error: err.message });
29  }
30}
31
32const message = errors.length > 0
33  ? `Imported ${inserted} rows with ${errors.length} batch errors`
34  : `Successfully imported ${inserted} rows`;
35
36utils.showNotification({
37  title: 'Import complete',
38  description: message,
39  notificationType: errors.length > 0 ? 'warning' : 'success'
40});
41
42// Refresh the main data table
43await query1.trigger();
44
45return { inserted, errors };
46
47// ----
48// SQL Query: insertUsersBatch
49// (Called in a loop above via additionalScope)
50// This requires a dynamic INSERT — use Retool's 'Bulk Insert' query type
51// or construct the VALUES dynamically with a JS Query.