Carefull with Gogole Sheet CSV export

2020-06-26 14:18  ggggrgviz        [permalink]

Ready for another story from the trenches? So image a Google Sheet made by someone else, with all kinds of dat in about 30 columns, of about a few thousand rows. Yes, it's a stretch to keep using Sheets for that, but this data will serve for the analysis for a decent application to manage this with... That probably won't be my team handling that project, but I had to do a quick cross reference of this data with the data in the database of one our current projects. The best way to do cross-checks is get the sheet into a table in the database to run queries. I guess you should be able to import a CSV pretty easily, right? I searched around and found this:

https://docs.google.com/spreadsheets/d/{key}/gviz/tq?tqx=out:csv&sheet={sheet_name}

Which I thought would provide the data in just the way ready for me to import. Wrong. The second column just happened to have codes for all of the items that are numeric for the first few hundreds of items, and then alphanumeric codes. At first I thought the CSV importer was fouling up, but I hadn't looked at the CSV data itself. Turns out this CSV exporter checks the first few lines (or perhaps even only the first one!), guesses the column is numeric, and then just exports an empty value for all non-numeric values in that column!

The code in that column was only in a number of cases needed to uniquely identify the items, so I first was looking for a reason why my cross-match was throwing duplicates in all of the wrong places. Ofcourse. Weep one tear for the time lost, then move on. Take solace in the wisdom gained.

I solved it by using the CSV from the Export menu. I only needed it once so I didn't get a URL for that. 

→