JSON-stat to Excel Converter
How to Use This Tool
Input Methods
You can provide data to the converter in two ways:
- Local File: Choose a JSON-stat dataset file from your computer. This is the default method.
- API URL: Enter a URL to a JSON-stat dataset.
Basic Usage
- Select your desired input method.
- If using a local file, click the "Choose File" button and select a valid JSON-stat dataset file.
- After selecting a file, the converter will automatically generate a properly formatted Excel spreadsheet.
- If using a URL, enter a valid JSON-stat dataset URL in the input field.
- Click the "Download" button: the converter will automatically generate a properly formatted Excel spreadsheet.
Advanced Features
For POST requests, after entering a URL, paste your JSON query parameters in the Query textarea.
Supported Formats
This tool supports JSON-stat (all versions) formatted datasets. Wrong input formats will result in an error message. The output will be a standard Excel (.xlsx) file with data and the main metadata from the JSON-stat source preserved in several sheets.
How to automatically transform your Excel file
Turn the raw Excel file into a fully joined, labeled dataset using the following VBA and Power Query code:
tables.bas (VBA)
— You need to download this file to import it in VBA Editorjoin.txt (Power Query)
— You only need to access the contents of this file to paste them in Power Query
Step 1: Import tables.bas into the VBA Editor
Instructions:
- Download
tables.bas
- Open the Excel file produced by the converter in Excel
- Press Alt + F11 to open the VBA Editor
- In the VBA Editor, click "File" → "Import File"
- Select the downloaded
tables.bas
file
Step 2: Run the VBA Code
Instructions:
- Press F5 or click the Run icon (▶️)
CreateTablesAutomatically
will be selected because it’s the only subroutine in the Excel file. Click the Run button.- A message appears: "All sheets converted to tables!" (press the button to confirm you have read the message)
- Close the VBA Editor.
Now every sheet is a proper Excel Table and Power Query can now see them.
(Once the VBA code has converted all sheets to tables, you don’t need to keep it in the final Excel file — it’s only needed for the one-time conversion process. The Excel file will maintain all table functionality without requiring the VBA code.)
Alternative Manual Method
Instead of using VBA, you can manually convert sheets to tables: Go to each sheet, select all data, press Ctrl + T (check "My table has headers"), and name (top left ribbon) the table the same as the sheet name. While this works for a few sheets, the VBA method is recommended for files with many sheets.
Step 3: Open Power Query Editor
Instructions:
- Go to the Data tab
- Click "Get Data" → "From Other Sources" → "Blank Query"
- Click "Advanced Editor" and remove any existing code
Step 4: Paste the M Code
Instructions:
- Copy and paste the contents of
join.txt
into the editor - Click "Done" to apply the changes
You can customize this query removing unneeded columns, combining the contents of columns (for example, id + label), etc.
Step 5: Load the Data to Excel
Instructions:
- Click "Close & Load" to load the data into Excel
Your final joined table appears in Excel. You can now create a PivotTable from this data: select any cell in your table, go to "Insert" → "PivotTable", and choose which dimensions to show as rows or columns, and put VALUE in the values field. You can work with either ids or labels for each dimension, and choose which values to aggregate in the cells.