Problem
This post will cover one hidden feature of SharePoint Connector / OneDrive Connector. When you read Excel data using Graph API from SharePoint Document Library or OneDrive, the date is returned as a Number (e.g., 43831 rather than 1/3/2020). To fix this issue, perform the steps listed below. You will need the latest connector file.
Without Read Filter change you will below data
Cause
Excel internally stores dates as numbers, so when we call the API, it returns them that way only. There is no easy way to know which field is a date because Excel tags that value as a double datatype. The good news is that we have another workaround for this issue.
Solution
Workaround#1 (for Legacy connector)
You can try the three-step process (incase you are using old connector file which doesn’t have Read Filter property):
- Download Excel from OneDrive/SharePoint
- Read using Excel Source (it detects the correct date type)
- Once done, delete the local file using the Advanced File System task.
Workaround#2 (Recommended)
Try to use the latest connector which has a new Read Filter property for Read Excel Worksheet endpoint.
Go to the API source (for SSIS) or API Driver (for ODBC) and change your settings to use Read Filter to $.text[*] as below. This will read all values as text with formatting applied, e.g., $123.50 for currency or 12/31/2025 for date. You must clean or convert to the correct types later on.
After this fix you can see values are with correct formatting
