Read Excel data as formatted text
Reads Excel data as formatted text instead of raw, unformatted values. Modify the Filter to $.text[*] to retrieve datetime values as formatted text, rather than as numeric serial values.
SELECT * FROM get_excel_worksheet_autodetect
WITH (
-- Drive (SharePoint Document Library)
DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,FileId='root:/myfolder/dump.xlsx:' --Path must end with colon
--,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX' --By Id (preferred)
,SheetId='Sheet1' --Id or Name
--,SheetId='{00000000-0001-0000-0000-000000000000}' --Using ID is good idea if Sheet ever renamed (Id doesnt change)
,Filter='$.text[*]' --this returns date as formatted value (i.e. 1/1/2020 rather than 43831)
--,Filter='$.formulas[*]' --this returns formula cells only (returns actual formula expression or null if no formula)
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.