Turning Spreadsheet Data into an Array: with or without scripting
Scripting is often part of our jobs, and the data we’re using in our scripts is regularly provided to us in spreadsheets. There are a couple different ways to get this data into an array, so we can iterate through it:
- First: spreadsheets can be downloaded as CSV files — writing a script to open and parse these only takes a few lines of code. This can be great if you’re already working with CSV parsing libraries in the script you’re writing, or if you’ll need to run the same script against multiple different input files.
- But sometimes even that is too much for the job at hand. There’s a quick alternative to this that just requires a text editor and some hot keys
I’ll cover both of these below, with a quick video of the hot-keys version to show you how quick it is.
Script
The script below is written in PHP (docs), but all programming languages I’ve used have the ability to open and parse CSVs — use whatever you’re most comfortable with. This script simply opens the file, iterates through each line, and appends the first element in each row to the results array. Noting of course, that if you don’t specifically need an array, but just to iterate through each row, you can do so within the while
loop itself and save yourself a loop!