LinkedIn
Twitter
Looker will not be updating this content, nor guarantees that everything is up-to-date.
Author: Mark Stephens
The Problem
Although Looker does not connect directly to an Excel spreadsheet, there are ways to upload data via a derived table.
First, there is a third-party CSV to SQL Tool with which you can easily convert a CSV file into a SQL UNION ALL query, and generate a LookML derived table file to copy and paste into the appropriate project. However, if you have security concerns, there is second, more secure, method.
The secondary method utilizes the CONCATENATE function to create a series of SELECT statements to ultimately create a SQL based derived table. This method works only if you have a limited number of columns and rows, such as monthly forecasting data, and is not very scalable for large spreadsheets. This method is also static and will not automatically update if the underlying spreadsheet data changes.
The following example highlights how to implement the second method described above.
The Solution
For example, we have some simple forecast data by month and region:
Using the CONCATENATE function, we can create SQL SELECT statements to generate a table.
The first line would be a simple SELECT clause, and the subsequent lines would UNION each row as a new SELECT clause.
Line 1 would look like:
=concatenate("select ","'",A2,"' as month, '",B2,"' as segment, '",C2,"' as forecasted_value")Then, each subsequent line would look like:
=concatenate("union select ","'",A3,"', '",B3,"', '",C3,"'")The resulting SQL statement becomes:
We can then paste this SQL into SQL Runner to generate a table:
Subsequently, using Looker's Add to Project option, we can create a view file in our project:
Looker creates dimensions from each column in the table. This new view file can then be joined into an Explore as needed.
Note that there are some caveats around this process:
- The new derived table is static; any changes to the underlying spreadsheet will not be automatically reflected in the LookML view file. However, if the underlying data changes but the column names stay the same, you can easily cut and paste the updated SQL directly from the spreadsheet into the LookML view file and skip the intermediary steps.
- This process is great for smaller datasets, but it may not be scaleable for spreadsheets with a large number of columns or rows, or data that will change in structure.
- All columns will come across into the view file as
type: string. Any dates or numbers may need to be converted into their true data types. - Do not forget to assign a primary key.
