Skip to main content

input.fileAsync() - CSV, cut rows and re-parse

J

I’m super excited to use the new input.fileAsync() API, but this is my first time working with file blobs in JavaScript, so I’m already scratching my head trying to figure out how to work with my file contents beyond the offerings of the API.

Disclaimer - this question has little to do with Airtable and the Scripting Block API itself; it’s basically just a plain old JavaScript question.

I already use the CSV import block to import bank statements to my budgeting base. However, my bank outputs 4 lines of identifier data before outputting the table of transactions – so I always have to manually open this CSV, remove those top 4 lines, save it, and then import it. I’ve been meaning to write a script to do this for me (was going to use ruby in MacOS), but Airtable’s new Scripting Block API is just in time to allow me to (hopefully) incorporate this into the import process!

So, I’m bringing in my file with

let fileObject = await input.fileAsync('Import a CSV', { allowedFileTypes: ['.csv'] });

The fileObject.parsedContents has 4 rows of content I don’t want before the table of transactions with a header row at the start of it. I’m really struggling to figure out how I can cut those 4 rows.

The only Intellisense option made available on fileObject.file that looks like it might allow me to do what I need to do is the Blob.slice() method – but according to documentation, this operates on bytes, and not on text content or file rows.

I’m continuing to research and asking elsewhere on the internet, but I figured I’d ask here too if anyone would be kind enough to help me tackle this and understand better how to work with blobs and bytes in JavaScript :slightly_smiling_face:

7 replies

K
  • Kasra
  • Inspiring
  • 192 replies
  • March 28, 2020

Since it’s a CSV file, it’ll probably be easiest to operate on the parsedContents rather than the raw Blob. E.g.

let csvFileResult = await input.fileAsync(
    'Upload a CSV file',
    {allowedFileTypes: ['.csv']}
);
let csvRows = csvFileResult.parsedContents;
// csvRows is an Array, so we can use `slice` to cut off the first 4 rows:
let slicedRows = csvRows.slice(4);
output.table(slicedRows);
J
1 person likes this
  • J
    Jeremy_Oglesby
J
Kasra wrote:

Since it’s a CSV file, it’ll probably be easiest to operate on the parsedContents rather than the raw Blob. E.g.

let csvFileResult = await input.fileAsync(
    'Upload a CSV file',
    {allowedFileTypes: ['.csv']}
);
let csvRows = csvFileResult.parsedContents;
// csvRows is an Array, so we can use `slice` to cut off the first 4 rows:
let slicedRows = csvRows.slice(4);
output.table(slicedRows);

:man_facepalming:t2:

Yes, I suppose that is the obvious, simple answer isn’t it…

Thank you, @Kasra!

My next question is, do I lose anything by not having the opportunity to use the hasHeaderRow option in the import? Or will it be easy enough to treat the first row as a header on my own in the JavaScript?

K
  • Kasra
  • Inspiring
  • 192 replies
  • March 28, 2020

Haha, chalk it up to coding on Friday evening :grinning_face_with_smiling_eyes:

You don’t lose much, the only difference is that when you use hasHeaderRow, each row in the array will be an object with keys coming from the header row, e.g:

[
  {Name: 'Alice', Age: 42},
  {Name: 'Bob', Age: 36}
]

When you don’t use hasHeaderRow, each row in the array with be an array, e.g.:

[
  ['Alice', 42],
  ['Bob', 36]
]
J
1 person likes this
  • J
    Jeremy_Oglesby
J
Kasra wrote:

Haha, chalk it up to coding on Friday evening :grinning_face_with_smiling_eyes:

You don’t lose much, the only difference is that when you use hasHeaderRow, each row in the array will be an object with keys coming from the header row, e.g:

[
  {Name: 'Alice', Age: 42},
  {Name: 'Bob', Age: 36}
]

When you don’t use hasHeaderRow, each row in the array with be an array, e.g.:

[
  ['Alice', 42],
  ['Bob', 36]
]

I see - perfect, thanks again :pray:t2:

J

Why is it that I don’t have the ability to mark a reply as a “Solution” on my threads? (I think this might only be the case in in the #developers section of the forums…)

no “Mark as solution” option…

K
  • Kasra
  • Inspiring
  • 192 replies
  • March 28, 2020

Hm, not sure! @Jason do you know?

J
  • Jason11
  • Inspiring
  • 375 replies
  • April 2, 2020

Hey @Jeremy_Oglesby, marking solutions isn’t a feature we have enabled for every category in the community just yet. Do you think it’d be helpful for most topics posted in the scripting block category?

Reply


Most helpful members this week

Terms & Conditions