Help

Import a CSV file from an attachment field in an Airtable Automation

1843 2
cancel
Showing results for 
Search instead for 
Did you mean: 
nachumi_e
4 - Data Explorer
4 - Data Explorer

 

Hello, So Airtable has a script that allows importing a CSV file to update records, 
I am trying to use this script in an Automation but instead of uploading the CSV to the script, I need it to grab it from an attachment field. 

The Airtable Script: 

// Ask the user to import a CSV file containing a header row
let csvFileResult = await input.fileAsync(
    'Upload a CSV file',
    {allowedFileTypes: ['.csv'], hasHeaderRow: true}
);
// The file importer will automatically parse contents for many file types, including CSV files
let csvRows = csvFileResult.parsedContents;
// Edit this to the name of a table in your base
let table = base.getTable('Purchases');
let shouldContinue = await input.buttonsAsync(
    `Import ${csvRows.length} records from ${csvFileResult.file.name} into ${table.name}?`,
    [{label: 'Yes', variant: 'primary'}, 'No']
)
if (shouldContinue === 'Yes') {
    // Create new records from the CSV.
    // Edit these field and property names to match your table and CSV data
    let newRecords = csvRows.map(csvRow => ({
        fields: {
            'Date': csvRow.Date,
            'Amount': csvRow.Amount
        }
    }));
    // A maximum of 50 record creations are allowed at one time, so do it in batches
    while (newRecords.length > 0) {
        await table.createRecordsAsync(newRecords.slice(0, 50));
        newRecords = newRecords.slice(50);
    }
}

So I have asked my best friend 😁 (GPT) to write me a new script that might be able to do that, Tho it is not working and i wanted to see if anyone can help me modify the script to achieve what i am looking for.

The custom script:

// Replace these values with your own
const apiKey = 'your-api-key';
const baseId = 'your-base-id';
const tableName = 'Table';
const attachmentFieldName = 'Attachment Field';

async function importCSVFromAttachmentField(recordId) {
  // Initialize the Airtable client
  const airtable = new Airtable({ apiKey }).base(baseId);

  try {
    // Retrieve the record with the attachment field
    const record = await airtable(tableName).find(recordId);

    // Check if the attachment field has a value
    if (!record.get(attachmentFieldName)) {
      console.log('Attachment field is empty');
      return;
    }

    // Get the URL of the attachment file
    const attachmentUrl = record.get(attachmentFieldName)[0].url;

    // Download the CSV file
    const response = await fetch(attachmentUrl);
    const csvText = await response.text();

    // Parse the CSV text into an array of objects
    const lines = csvText.split('\n');
    const headers = lines[0].split(',');
    const records = lines.slice(1).map((line) => {
      const values = line.split(',');
      return headers.reduce((record, header, i) => {
        record[header] = values[i];
        return record;
      }, {});
    });

    // Insert the records into the base
    await airtable(tableName).create(records);
    console.log(`Imported ${records.length} records`);
  } catch (error) {
    console.error(error);
  }
}

// Run the function when the button is clicked
input.button.onClick(() => {
  importCSVFromAttachmentField(input.recordId);
});

nachumi_e_0-1673314504666.png

 

2 Replies 2
BradleyGraber
4 - Data Explorer
4 - Data Explorer

The only trouble I had with this script was I was using the wrong URL for the attachment.  There is a viewer URL, and an "Expiring Download" URL  I used the airtable input GUI to select the Expiring Download URL, and it worked.  I'm not sure how to select it using the recordId and scripting.

Gokula_Krishnan
5 - Automation Enthusiast
5 - Automation Enthusiast
I got help from Chat GPT to do this:
My import Bulk Table had 3 simple columns, A CFV File column, a trigger column to trigger script.
New student table is where the CSV goes into. It works, no need for  complicated integration tools
 
// Define the source table (ImportBulk) and target table (Test New Student)
let importTable = base.getTable('ImportBulk');
let targetTable = base.getTable('Test New Student');

 

// Use input config to get the RecordID from the "LineID" field
let lineID = input.config().LineID;

 

// Fetch records from the "ImportBulk" table
let queryResult = await importTable.selectRecordsAsync({
    fields: ['RecordID', 'CSVFile']
});

 

// Find the record matching the provided LineID
let record = queryResult.records.find(record => record.getCellValue('RecordID') === lineID);

 

if (!record) {
    throw new Error('Record with the provided LineID not found.');
}

 

// Fetch the CSV file from the CSVFile field
let csvFileField = record.getCellValue('CSVFile');
if (!csvFileField || csvFileField.length === 0) {
    throw new Error('No CSV file found in the CSVFile field.');
}

 

let csvFileUrl = csvFileField[0].url;

 

// Fetch the CSV file content
let response = await fetch(csvFileUrl);
let csvText = await response.text();

 

// Parse the CSV file content into rows
let csvRows = csvText.trim().split('\n').map(row => row.split(','));
let headers = csvRows[0]; // The first row contains column headers
let dataRows = csvRows.slice(1); // All subsequent rows contain data

 

// Fetch the field metadata from the "Test New Student" table to identify editable fields
let targetFields = targetTable.fields;
let editableFields = targetFields.filter(field => !field.isComputed).map(field => field.name);

 

// Filter headers that match the editable fields in the target table
let matchingFields = headers.filter(header => editableFields.includes(header));

 

if (matchingFields.length === 0) {
    throw new Error('No matching fields found between CSV and "Test New Student" table.');
}

 

// Process each data row from the CSV file
for (let row of dataRows) {
    let recordData = {};

 

    // For each header, if it matches a modifiable field in the "Test New Student" table, add the data
    headers.forEach((header, index) => {
        if (matchingFields.includes(header)) {
            recordData[header] = row[index];
        }
    });

 

    // Create a new record in the "Test New Student" table only if there is valid data to enter
    if (Object.keys(recordData).length > 0) {
        await targetTable.createRecordAsync(recordData);
    }
}

 

output.set('text', 'CSV data has been successfully imported into the "Test New Student" table.');