How to Fix Power Query Cannot Extract Website Data or Load JSON in Excel
Question details
The user is unable to extract data from a website or import a JSON file using Power Query in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing web data or JSON files into Excel via Power Query.
- Observed behavior
- Power Query fails to connect or load the data, often due to missing database drivers, connection issues, or invalid JSON endpoints.
Ensure that you have a stable internet connection and that the URL or local path to your JSON file is correct and accessible.
Update Database Drivers and Verify JSON Endpoint
Ensure required ODBC/OLE DB drivers are up to date and the JSON data source is valid and accessible.
Power Query relies on specific system drivers to interpret web protocols and database connections. If these drivers are outdated or missing, the connection will fail. Additionally, the web endpoint must return properly formatted JSON data.
Close Microsoft Excel completely and reopen it to clear any temporary background connection errors or stalled queries.
Copy the website URL or JSON endpoint and paste it into a standard web browser to confirm it is live and returns valid JSON code without requiring complex authentication.
Download and install the latest versions of the required ODBC or OLE DB drivers from your database provider's official website.
Go to the Data tab in Excel, select 'Get Data', and choose 'From Web' or 'From File' > 'From JSON' to attempt the connection again.
Transform Data Using Power BI Desktop
Use Power BI Desktop's advanced Power Query editor to import the data and then export it to Excel.
Try WPS Office for Seamless Data Handling
If you frequently encounter complex driver issues, slow loading times, or import errors in Excel, consider switching to WPS Office. It provides a lightweight, highly compatible, and user-friendly spreadsheet application to manage your daily data and files without heavy resource demands.
Frequently Asked Questions
Why does Power Query say the JSON input is invalid?
This error occurs when the website's response is not formatted as strict JSON. It may contain HTML tags, trailing commas, or authentication error messages that disrupt the required JSON structure.
How do I check if my ODBC driver is installed in Windows?
Open the Windows Start menu and search for 'ODBC Data Sources'. Open the application and navigate to the 'Drivers' tab to see a complete list of all currently installed database drivers on your system.
Can I import a JSON file stored on my local hard drive?
Yes. In Excel, go to the Data tab, click 'Get Data', select 'From File', and then click 'From JSON'. Browse to the file location on your local drive, select the file, and click Import.
Does WPS Office support importing external data?
Yes, WPS Spreadsheets allows you to import external data from local files like CSV or text formats. You can access these import features via the Data tab by selecting 'Import Data'.
