Tips and tricks to access and take away Statistics Canada data

There are multiple ways to access and take away Statistics Canada data depending on the software you have at hand. Such as Excel, or Tableau and Power BI.

1.There exists a Web Data API which returns vector (shortcuts to data points) data in JSON format. Typically needing a programmer to set up this kind of access.

User Guides are found here: Web Data Service (WDS) We have some Excel, Python, R, AJAX, SAS examples if requested.

2. Users can also retrieve the Full Table Download in CSV format, compressed in a ZIP including data and metadata.

The guide to the CSV format is found here: Full Table Download (CSV) User Guide

Example:

https://www150.statcan.gc.ca/n1/en/tbl/csv/14100287-eng.zip
https://www150.statcan.gc.ca/n1/fr/tbl/csv/14100287-fra.zip

3. You can retrieve custom CSV directly from the external website using the following methods: Download data as displayed on screen. Taking away a CSV output after customizing the view by hand. (LFS hourly wages example: Hourly wage distributions by occupation, monthly, unadjusted for seasonality)

For your user community, using the new Download selected data format is an easier way for users to filter and download directly into CSV. See the following examples that are not advertised but still possible on our site for expert users who could re-use these shortcuts for easy linking to generated CSV files.

Simply once a table has been customized in the Add/Remove section, when clicking on the Download Options overlay, a user can Right click over the Download Selected Data and using the "Save Link Location…" or in Internet Explorer "Copy Shortcut" you can extract the custom link to generate the CSV on the fly.

A CSV retrieval with a time period parameter included looks like this:

https://www150.statcan.gc.ca/t1/tbl1/en/dtl!downloadDbLoadingData-nonTraduit.action?pid=1010013201&latestN=&startDate=20180101&endDate=20180301&csvLocale=en&selectedMembers=%5B%5B1%5D%2C%5B1%2C2%2C3%2C4%2C5%2C6%2C7%5D%5D

This retrieval shows latest N(5) periods, instead of a time period, it looks like this:

https://www150.statcan.gc.ca/t1/tbl1/en/dtl!downloadDbLoadingData-nonTraduit.action?pid=1010013201&latestN=5&startDate=&endDate=&csvLocale=en&selectedMembers=%5B%5B1%5D%2C%5B5%2C7%2C1%2C6%2C4%2C2%2C3%5D%5D

4. Also, a user can also bookmark the URL using Save my selections so your community could easily return to the same customized table, month after month.

Save my selections looks like this for the same example table, but comes back to the online GUI, to allow edits to the filters.

https://www150.statcan.gc.ca/t1/tbl1/en/cv!recreate.action?pid=1010013201&selectedNodeIds=1D1,2D1,&checkedLevels=&refPeriods=20180101,,20181001&dimensionLayouts=layout2,layout3,layout2&vectorDisplay=false

5. Using the Search by Vector feature is also a quick way to download many data points from a variety of tables at once, here is an example:

https://www150.statcan.gc.ca/t1/tbl1/en/sbv?vectorNumbers=v37151%2C+v1558664%2C+v1576432%2C+v1592178%2C+v2062811%2C+v2062815%2C+v4327078%2C+v4331088%2C+v41552794%2C+v41552796%2C+v41690973%2C+v41690974%2C+v41691233%2C+v42169911%2C+v42169920%2C+v52367097%2C+v53384992%2C+v61913615%2C+v61915304%2C+v61915306%2C+v61915308%2C+v61915313%2C+v61915327%2C+v62305724%2C+v62305731%2C+v62305733%2C+v62305742%2C+v62305745%2C+v62305748%2C+v62305752%2C+v62305783%2C+v62305984%2C+v62425528%2C+v62425550%2C+v62425572%2C+v62425630%2C+v65201226%2C+v65201499%2C+v66496802%2C+v66496805%2C+v66496811%2C+v79310246%2C+v79310643%2C+v87008839%2C+v87008840%2C+v87008851%2C+v87008955%2C+v87008956%2C+v87008967%2C+v108785713%2C+v108785714%2C+v108785715&searchOption=2&latestN=1&vectorList_length=100&request_locale=en

6. Additional ways to take away data:

  • A daily Delta File of all the data points that have changed for the day. This method would be for high volume users that want to recreate our database in their systems. Information on this method is found in our Developers pages: (For example: Delta File)
  • Download Full Table in SDMX (XML) format.
  • Download hundreds of single number Indicators of content through a JSON Indicators API found here at the bottom of the page: Developers
  • Subscribe to tables to be emailed when they are updated through "My Statcan" found here: My StatCan

Additionally, if Option 3 does not work for you, there are options to break apart and use the large Full Table CSV files to load them into Excel as follows:

For the large Full Table Download CSV files, you could consider:

  • Using a CSV splitter similar to this open source one for Windows: Free Huge CSV Splitter
  • Using Power Query in Excel

Opening large CSV files using power query in Excel

Requirements:

Excel 2016 - Power Query comes standard

Previous Excel versions - Power Query needs to downloaded and installed as an add-in before being available for use

Download:

Power Query for Excel can be downloaded from the Microsoft Download Center. Installation instructions are also provided at this location. Download Microsoft Power Query for Excel

Instructions:

Once Power Query has been installed you can open and extract parts of large .csv files by following these steps:

  1. Select "Power Query", "From File", "From CSV".
  2. Select the file that you wish to view or manipulate (a new window will open showing the first few lines of this file with all the column headings).
  3. From the "Query Editor" window, you can filter the information using the dropdown selections in each column.
  4. Once the proper filters have been applied, you can extract that part of the file by using the "Keep Rows", "Keep Top Rows" option.
  5. This option will then ask how many rows you would like to keep for future extraction.
  6. You can then proceed to extract this information by using the "Close & Load", "Close & Load To" option.
  7. Finally, you will be given the option to place these rows in a new Excel worksheet for analysis or manipulation.

More information:

Many more functions and manipulation options that have not been discussed above are available through Power Query, thus allowing the user to open and extract parts of massive .CSV files.

More information on this topic can be found online.

Depending on how familiar you are with Pivot Tables in Excel, you can also using the Data Model source function in Excel to use the CSV file as a Pivot Table Data Model source. Loading CSV/text files with more than a million rows into Excel

Microsoft Power BI DaX:

let

GetCoord = (prodid as text, coord as text, period as text) => let

Json = Json.FromValue({[productId=prodid, coordinate=coord, latestN=period]}),

Getdata = Json.Document(Web.Contents("https://www150.statcan.gc.ca/t1/wds/rest/getDataFromCubePidCoordAndLatestNPeriods", [Headers=[#"Content-Type"="application/json"], Content=Json])),

#"Converted to Table" = Table.FromList(Getdata, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

#"Converted to Table"

in

GetCoord
Report a problem or mistake on this page
Date modified: