ben tedder : code things

Exporting from Google Sheets to MySQL via CSV or TSV

Getting data from Google Sheets into MySQL can require a bit of work. Here are some things I did, and some things that worked.

CSV vs TSV

The first battle I fought with Google Sheets was deciding how to export the file. I started off with a .csv (comma-separated values) export. All was going fine until I noticed some inconsistencies in my data. Items were in the wrong columns, it was a mess. I had to start over.

Turns out that some of my columns had some weirdness: double quotes, double-double quotes, single quotes, all in one field. I don't think Google could handle that. I'm not sure what the .csv 'spec' is, but I guess I was kind of expecting it to escape all those characters for me. Guess not.

My next approach was to use a .tsv (tab-separated values) file. That worked much better. Things were where they should be. But I wasn't done yet.

How do you put a .tsv into mysql?

TSV to MySQL

As it turns out, TSV to MySQL is actually quite straightforward. Here's the simplest form (but keep reading):

LOAD DATA LOCAL INFILE '/Users/me/my-data.tsv' 
INTO TABLE mytable;

TSV to MySQL without making yourself go crazy

Unless you have the most boring table in the world, and the most predictable data in the world, you'll have some fun here. Here's the fun I had.

  • Some of the values in Google Sheets were null, and they were going into a DECIMAL{5,3} column.
  • I did not want to import some columns from Google Sheets to MySQL
  • I still wanted my created_at and updated_at to be set so Active Record would have good data

Select columns

There is a great way to hide, select, ignore columns when using LOAD DATA LOCAL INFILE. Name your columns. Also, sluff off columns you don't want into @variables, like so:

LOAD DATA LOCAL INFILE '/Users/me/my-data.tsv' 
INTO TABLE mytable (part_number, name, @ignored_column, price);

This is great if you have an auto-incremented ID column or something similar, takes care of it. It's also great if you want to keep the structure of your Google spreadsheet but need to import (especially if this is a regular occurence) to a MySQL table of a slightly different structure.

Important Note: I learned that any imported item using LOAD DATA LOCAL INFILE is seen as a string. Tuck that away for later.

Dealing with NULL values when importing to an integer, decimal, etc field

This one was lame. But I took advantage of the @variables mentioned above, so it was ok. I basically had to create secondary columns in my spreadsheet with the following formula:

=IF(ISBLANK(F3),0,F3)

In this scenario I had my original price in the F column, and my not_null_price in the G column with this formula. That meant that when I imported, it looked like this:

LOAD DATA LOCAL INFILE '/Users/me/my-data.tsv' 
INTO TABLE mytable (part_number, name, @null_price, price);

I wish there was a better way (I'm sure there is) to just script out a one-time thing that replaces all non-decimal or null values to 0 in Google Sheets, but I didn't find anything in my quick searching.

Setting values that your spreadsheet doesn't have

There is one more item I used with the LOAD DATA LOCAL INFILE scripting. That's the SET method. You can set things like created_at, updated_at, or really anything you want. Here's what I did:

LOAD DATA LOCAL INFILE '/Users/me/my-data.tsv' 
INTO TABLE mytable (part_number, name, @null_price, price)
SET created_at = NOW(), updated_at = NOW(), featured = 0;

I wanted to start all products out in and 'un-featured' state, and I wanted to just have a standard date time "2016-10-07 22:44:36" in the created_at and updated_at fields.

Tip: Tweaking the .tsv file

  1. Open the .tsv file
  2. Remove the first row (if it is just column names)
  3. Add a new line at the end of the file (often MySQL tripped up on last row since it wasn't terminated)

Overview

Hopefully those little tips got you where you needed to be. After a good amount of work I got all my data moved from Google Sheets to a .tsv, and from a .tsv to a MySQL table. Woohoo!