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

[nachumi_e]

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);
});

[BradleyGraber]

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]

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.');