Custom Scripting: extract text from a csv attachment (in an airtable form) and upload to table

[dmkirby]

Hello, 

I'm trying to write a custom script (and I'm a JS novice) to extrtact/parse the data from a csv file that an external user would attach to a form submission. I need that data from the csv to then be uploaded into another airtable table within the same base. I tried setting this up with Make and Zapier and webhooks where the form is on Softr instead of Airtable directly but Make/Zapier couldn't read a file attachment on Softr only a urls like a Google Drive link. 

Here is my starter code: I'm currently getting an error on my forEach(function). Any help is madly appreciated!! 

//Set up variables

var sourceTableName = 'Form'; //name of table to extract data from

var targetTableName = 'Claim Assignments'; //name of table to upload data to

var csvAttachmentFieldName = 'attachments'; //name of attachment field containing CSV file

//Get source table

var sourceTable = base.getTable(sourceTableName);

//Get target table

var targetTable = base.getTable(targetTableName);

//Get records from source table

var sourceRecords = sourceTable.selectRecordsAsync();

//Loop through each record in source table

sourceRecords.forEach(function (record) {

//Get attached CSV file field from record

var csvFile = record.getCellValue(csvAttachmentFieldName);

//Extract text from CSV file

var text = extractCSVText(csvFile);

//Map text from csv onto columns in target table

var fields = {};

fields['Assigned Adjuster'] = text[0]; //map text from csv in first column to text field 1

fields['Policy Holders'] = text[1]; //map text from csv in second column to text field 2

//Create new record in target table

var newRecord = targetTable.createRecord(fields);

});

//Function to extract text from csv file

function extractCSVText(file) {

// code to extract text from csv file

}

getTableByName: any;

[Alexey_Gusev]

Hi,

I could give some clues:
first and basic - use 

to post your code.
Unformatted code is hard to read, so any reader who doubt 'read or skip' will skip your post 😀

- commenting code is good, but not so much. like many things in life, excessive amount of a cure might become a poison 
- it's also good to put names at the beginning, so the code can be changed for other tables. But you can also simplify it

Instead of 

//Set up variables
var sourceTableName = 'Form'; //name of table to extract data from
var targetTableName = 'Claim Assignments'; //name of table to upload data to
var csvAttachmentFieldName = 'attachments'; //name of attachment field containing CSV file
//Get source table
var sourceTable = base.getTable(sourceTableName);
//Get target table
var targetTable = base.getTable(targetTableName);

you can write

//Set up tables and fields 
var sourceTable = base.getTable('Form');
var targetTable = base.getTable('Claim Assignments');
var csvField = 'attachments'; 

it's my personal opinion and I might be wrong, but sometimes you should avoid long variable names for fields, because you can use a lot of 'record.getCellValue(SomeVariableWithaLongName) in function expressions, and such code might be harder to read.

- using var everywhere is a typical behavior of JS beginners with a background of other languages (I started the same way 2 years ago)
next level is to use let in functions and separate blocks of code
next level: using const for variables designed to be constant - like table(s) and field names. (Personally I named constants in all CAPITAL letters to make them more visible in code, but soon after stopped doing so)
next level: understanding of arrow-functions. using mostly let, sometimes const, in very rare cases - var.
next(current): always using const, for variables and functions. in rare cases - let, when I sure I need it.
what's next? I don't know.
Of, course, it's all the matter of taste. But I think you will follow different stages and you can't jump on next without understanding previous.

and returning to the topic subject

To read and process file contents from Airtable,
You need to check this

and this example (I also recommend to see them all)

I never created a script for CSV, but had some experience with writing TXT files content to another table.
I think, in your case, your function will be a kind of hybrid between 'read file' and 'process csv'.

[DianaMk]

Thanks so much @Alexey_Gusev . I so appreciate your taking the time to share your learnings and these resources as well. 

I hadn't been able to find the developer documents for Scripts until viewing the screenshots you provided- guided my google search haha. 

This has helped me learn more in general about JS and also about Airtable's scripting capabilities between the Scripting extension vs. Running a Script via Automation. 

Cheers!