How to Fix Unexpected Token E in JSON When Importing Excel to Microsoft Lists
Question details
The user needs to fix an error preventing the successful import or export of an Excel workbook data into Microsoft Lists or SharePoint.

- Product
- Microsoft Excel, Microsoft Lists, SharePoint
- Device & OS
- not provided
- Scenario
- Importing an Excel table into Microsoft Lists or exporting directly from Excel to a SharePoint site.
- Observed behavior
- The import fails with an 'Unexpected token E in JSON at position 0' error, or a direct export from Excel fails with a 'Cannot connect to the server at this time' message.
Ensure your network connection is stable, especially if using a corporate VPN, as network restrictions can sometimes block the connection between Excel and SharePoint servers.
Verify and Clean Excel Table Formatting
Ensure your Excel data strictly meets the requirements for a successful Microsoft Lists import.
Microsoft Lists requires Excel data to be strictly formatted as a table without missing critical values. Malformed data is the most common cause of JSON parsing errors during upload.
Open your workbook, select the entire data range, and press Ctrl+T to ensure the data is officially formatted as an Excel Table.
Scroll through your table and delete any completely blank rows. Ensure the column designated as the 'Title' column contains no empty cells.
Check your total row count. Microsoft Lists only accepts imports of up to 20,000 rows at a time. Split the data if it exceeds this limit.

Transfer Data to a Clean Workbook
If the current workbook is corrupted or contains hidden malformed data, migrating to a fresh file can resolve the error.
Import Directly via SharePoint Site Contents
Bypass direct Excel exports by initiating the list creation directly from your SharePoint environment.
Edit and Clean Your Spreadsheets Easily with WPS Office
If you are tired of complex integration errors and heavy software in Microsoft Office, try WPS Office. It provides a lightweight, highly compatible alternative to Microsoft Excel for formatting, managing, and exporting your spreadsheet data effortlessly.
- 1. Download WPS Office: Visit the official WPS website and download the free office suite to your device.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx file. Your formatting and table structures will remain perfectly intact.
- 3. Manage and Export Data: Use the intuitive tools to clean up rows, adjust date formats, and prepare your data without heavy processing lag.

Frequently Asked Questions
Why does the 'Unexpected token E in JSON' error occur when importing Excel?
This typically happens when the Microsoft Lists server receives malformed data. In Excel imports, it is often triggered by invalid date formats, empty rows within a defined table, corrupted hidden characters, or exceeding the 20,000-row limit.
Can network settings or VPNs affect Excel exports to SharePoint?
Yes. If you are using a VPN to access a company intranet, network interruptions or firewall restrictions can block the connection, resulting in a 'Cannot connect to the server at this time' error when exporting directly from Excel.
How do I remove hidden formatting that might be causing import errors?
You can create a new, blank workbook, copy your original data, and use the 'Paste as Values' option. This strips away background formatting and hidden corrupted characters, leaving only the raw data to be safely formatted as a new table.
What is the maximum number of rows I can import into Microsoft Lists from Excel?
Microsoft Lists supports importing a maximum of 20,000 rows from an Excel table in a single operation. If your table is larger, you must split it into multiple files or batches.




