Skip to content

Instantly share code, notes, and snippets.

@benzado
Created September 1, 2011 17:02
Show Gist options
  • Save benzado/1186652 to your computer and use it in GitHub Desktop.
Save benzado/1186652 to your computer and use it in GitHub Desktop.
Convert a Google form response spreadsheet into a readable text file
I first tried to use sqlite's ability to import a file into a table, but ran
into a problem: if any line in the source file didn't have the same number of
columns as the table, the import would fail. For example, if your 10 question
survey ends with a comment field and a respondent left it blank, that line will
have 9 columns instead of 10 and the import will fail.
Next I tried to use a combination of command line tools like `cut` and `paste`,
but couldn't figure out anything that was reasonable. So I gave up and wrote a
perl script, which is fine, because this is what perl is good at.
I recommend working Google's text format (tab-separated values) since the tab
character is unlikely to appear in your data, making it easier to avoid dealing
with escaping issues.
Name Favorite Color
Leonardo Blue
Donatello Purple
Raphael Red
Michaelangelo Orange
Q: Name
A: Leonardo
Q: Favorite Color
A: Blue
Q: Name
A: Donatello
Q: Favorite Color
A: Purple
Q: Name
A: Raphael
Q: Favorite Color
A: Red
Q: Name
A: Michaelangelo
Q: Favorite Color
A: Orange
# Google Docs: select File > Download as > Text to get a tab separated text file
cat survey.tsv | perl -n -e '@x=split(/\t|\n/); if (@h==0) { @h=@x; } else { for($i=0;$i<=$#x;$i++) { printf "Q: %s\nA: %s\n", $h[$i], $x[$i]; } print "\n"; }'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment