Loading complex CSV files into BigQuery using Google Sheets

Building an ELT pipeline using Google Sheets as an intermediaryBigQuery offers the ability to quickly import a CSV file, both from the web user interface and from the command line:Limitations of autodetect and importThis works for your plain-vanilla CSV files, but can fail on complex CSV files. As an example of a file it fails on, let’s take a dataset of New York City Airbnb rentals data from Kaggle. This dataset has 16 columns, but one of the columns consists of pretty much free-form text. This means that it can contain emojis, new line characters, …Indeed, try to open this file up with BigQuery:and we get the errors like:This is because a row is spread across multiple lines, and so the starting quote on one line is never closed. This is not an easy problem to solve — lots of toolsstruggle with CSV files that have new lines inside cells.Sheets to the rescueGoogle Sheets, on the other hand, has a much better CSV import mechanism. Open up a Google Sheet, import the CSV file and voila …The cool thing is that by using a Google Sheet, you can do interactive data preparation in the Sheet before loading it into BigQuery.First, delete the first row (the header) from the sheet. We don’t want that in our data.ELT from a Google SheetOnce it is in Google Sheets, we can use a handy little trick — BigQuery can directly query Google Sheets! To do that, we define the Google Sheet as a table in BigQuery:Steps from the BigQuery UISelect a dataset and click on Create TableSelect Drive as the source, specify the Drive URL to the Google SheetSet Google Sheet as the file formatGive the table a name. I named it airbnb_raw_googlesheetSpecify the schema:This table does not copy the data from the sheet — it queries the sheet live.So, let’s copy the data as-is into BigQuery (of course, we could do some transformation here as well):How to automateYou can automate these steps:Here’s an article on how toread a CSV file into Sheets using PythonFrom then on, usedataform.co or BigQuery scripts to define the BigQuery table and do the ELT.To import complex CSV files into BigQuery, build an ELT pipeline using Google Sheets as an intermediary. This allows you to handle CSV files with new lines and other special characters in the columns. Enjoy!Related Article[New blog series] BigQuery explained: An overviewOur new blog series provides an overview of what’s possible with BigQuery.Read Article
Quelle: Google Cloud Platform