Although Looker does not connect directly to an Excel spreadsheet, there is a way to upload data via a derived table. This method works if you have a limited number of columns and rows, such as monthly forecasting data that is not very scalable for large spreadsheets. This data is also static and will not automatically update if the underlying spreadsheet data changes.
In this 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 our SQL Runner to generate a table:
Subsequently, using Looker’s Add to Project option, we can create a view file in our project:
Looker will create 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 automatically be 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 for spreadsheets with a large number of columns or rows, or data that will change in structure, it may not be scaleable.
- 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.