Loading Data Files in Excel
Excel 2013 (or later) on Windows can load compressed CSV (
csv.gz) data files through Power Query into a Power Pivot (also known as the Excel 'Data Model').
All versions of Excel can load uncompressed CSV data files without using Power Query, but this is not recommended for large data files.
Download and save the data files to a suitable location. In the examples that follow, the data has been saved to
You can use the below steps as a guide on how you can load compressed (Gzip) data files in Excel.
Navigate to the 'Data' tab in the Excel ribbon and click
Get Data > From Other Sources > Blank Queryto create a new blank query.
Use the Advanced Editor to add a Power Query M expression like the following to the blank query. This will allow the compressed (Gzip) data file to be loaded directly.
Source = Binary.Decompress(File.Contents("C:\data\date\date.csv.gz"), Compression.GZip),
#"Imported CSV" = Csv.Document(Source,[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None])
Click 'Done' on the Advanced Editor window and a preview of the data should appear.
Apply the 'Use First Row as Header' transformation and rename the query to something appropriate.
Close & Load > Close & Load to...and ensure that you have 'Only Create Connection' selected and the 'Add this data to the Data Model' checkbox ticked.
Guidance on how analyse data in Excel is beyond the scope of this documentation. You may find the following external links helpful: