.csv Parsing through Scripting Block

[Sam_Cederwall]

I am attempting to map a csv file to one of my bases via scripting due to some pre-processing I would like to do, however, I began running into issues with parsing. Figuring that I had made a mistake I created a new script to test a more simple csv with state names and regions essentially hoping I could troubleshoot on my own.

This is the output table I get with an csv I upload:

And here’s the script I’m using:

// Prompt for a file upload
let csvFileResult = await input.fileAsync(
    'Upload a CSV file',
    {allowedFileTypes: ['.csv'], hasHeaderRow: true}
);

//Display table w/ parsed contents
let csvRows = csvFileResult.parsedContents;
output.table(csvRows)

I’ve been looking at the spreadsheet importer example and the documentation within the block but have had no luck thus far, any help would be much appreciated.

Thank you,
Sam

[Mike_Pennisi]

And the file type:

application/vnd.ms-excel

Well, that’s problematic I assume. Does this help clarify what’s wrong?

Yes, that’s unexpected. This bug report from another project has a little more detail:

I think Windows is messing with MIME types where it shouldn’t. The official MIME type for CSV files is text/csv. It appears that application/vnd.ms-excel is also the MIME type for actual Excel files

@Stephen_Suen @somehats This seems like something the Scripting block may have to address.

In the mean time, two workarounds come to mind (both seem safer than parsedContents.Sheet1). You might try disassociating .csv files with Excel in the “Default Programs” settings in Windows. That was suggested in the above issue, but no one reported whether it worked. It could also interrupt other workflows you have around CSV files.

Alternatively, you could try exporting the file as a .xls or .xlsx file, and then updating your script like so

 // Prompt for a file upload
 let csvFileResult = await input.fileAsync(
     'Upload a CSV file',
-    {allowedFileTypes: ['.csv'], hasHeaderRow: true}
+    {allowedFileTypes: ['.xls', '.xlsx'], hasHeaderRow: true}
 );

 //Display table w/ parsed contents
 let csvRows = csvFileResult.parsedContents;
 output.table(csvRows)

This may work if Windows and Airtable might have more agreement on what it means to be a spreadsheet file than they do on what it means to be a CSV file.

I appreciate the quick responses, it’s incredibly helpful.

Sure thing!

[Billy_Littlefie]

Thanks for the detailed report and debugging efforts @Sam_Cederwall and @Mike_Pennisi. You’ve identified the underlying issue here perfectly. To summarize –

The Sheet1 root property on parsedContents is the result of the scripting block parsing this file as an XLS file, which can contain multiple sheets and therefore needs this extra layer at the root to differentiate between rows in different sheets. “Sheet1” is the default fallback when no sheet name is provided. When a CSV file is parsed, we omit this “Sheet” layer and parsedContents just returns the rows directly (as mentioned above in the thread, there’s no concept of multiple “sheets” within a CSV file)

The unexpected bit here is the mismatch between the mime-type and the file extension. I think this qualifies as a bug - we can update the scripting block to remove this “Sheet” layer for both files ending in “.csv” and files of type “text/csv”.

[Sam_Cederwall]

Thank you guys for all the hard work on this. Appreciate the help, glad we could uncover this bug.

[Sam_Cederwall]

Also @Billy_Littlefield, if you could let me know here when this has been fixed, that would be a tremendous help. Thanks again.

[Billy_Littlefie]

@Sam_Cederwall Sure thing, I’ll post here once the fix is live.

[Billy_Littlefie]

@Sam_Cederwall The patch just went out, so parsedContents should directly return an array of row items for that CSV. Let me know if you’re still experiencing issues!

[Sam_Cederwall]

Thanks for the update @Billy_Littlefield, it’s working great so far!