Turning Spreadsheet Data into an Array: with or without scripting

2 min readJun 24, 2021

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!

$filename = ‘./path/to/file.csv’;
$file = fopen($filename, “r”);$results = [];
while (($row = fgetcsv($file)) !== FALSE) {
 $results[]= $row[0];
}

Without a Script

As much as developers love to think automation is the answer to everything…sometimes a script is overkill. The good news is, there’s a quick way to accomplish this without one, by leveraging the ability. of most text editors intended for writing code to apply multiple cursors. Video of this below, followed by instructions and the specific hotkeys:

Select the column in your spreadsheet
Paste it into the text editor
Select the first newline character
⌘ + d (VSCode, Atom, Sublime) or ctrl + ⌘ + d (IntelliJ) creates a new cursor for all other instances of the selected character
ctrl + e moves your cursor (or all your cursors) to the end of whatever line you’re on
Type the closing ”,
ctrl + a moves your cursor (or all your cursors) to the beginning of whatever line you’re on
Type the opening ”, backspace to move everything onto one line, if you like
esc to revert back to one cursor
Type the opening [, ctrl + e takes you to the end of the line for the closing ]