Not able to read the Excel file using the download url of the share point file into the Azre ADF http Excel dataset

Question

Hi, I am using the Share point download url in the Azure ADF http Excel dataset, where i have put the download url then also this does not read the sheet name .
When i put the sheet name manually then this not show the preview.

In that similar way i have work with the csv files, working correctly, but not with xlsx file?
getting the error in the preview when using the sheet name manually -

Only '.xls' and '.xlsx' format is supported in reading excel file while error is '   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at Microsoft.DataTransfer.ClientLibrary.MultipartSequentialReadSource.<ReadBuffersInSequential>d__5.MoveNext()
   at Microsoft.DataTransfer.ClientLibrary.TransferStream.ReadInternal(Byte[] buffer, Int32 offset, Int32 count)
   at Microsoft.DataTransfer.ClientLibrary.TransferStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at Microsoft.DataTransfer.ClientLibrary.SizeBoundedTransferStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at Microsoft.DataTransfer.ClientLibrary.TransferStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.Fill()
   at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.ReadLeByte()
   at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.ReadLeInt()
   at ICSharpCode.SharpZipLib.Zip.ZipInputStream.GetNextEntry()
   at NPOI.OpenXml4Net.Util.ZipInputStreamZipEntrySource..ctor(ZipInputStream inp)
   at NPOI.OpenXml4Net.OPC.ZipPackage..ctor(Stream filestream, PackageAccess access)
   at NPOI.OpenXml4Net.OPC.OPCPackage.Open(Stream in1)
   at NPOI.Util.PackageHelper.Open(Stream is1)
   at NPOI.XSSF.UserModel.XSSFWorkbook..ctor(Stream is1)
   at Microsoft.DataTransfer.ClientLibrary.ExcelUtility.GetExcelWorkbook(String fileExtension, TransferStream stream)'.
One or more errors occurred.
Failed to read data from http server. Check the error from http server：The request was aborted: The connection was closed unexpectedly.
The request was aborted: The connection was closed unexpectedly.

Answer 1

Hi nishant garg,

it sounds like you’re running into an NPOI zip-parsing error when ADF’s HTTP-Excel dataset tries to fetch your SharePoint download URL instead of a clean .xlsx stream. Here are a few things you can try:

Make sure the file isn’t protected • Open it in Excel, click Enable Editing and then Save. This clears any workbook/sheet protection that the NPOI library (which ADF uses under the covers) can’t handle.

Validate your download URL • In Postman or your browser, hit the URL and confirm you get back a raw .xlsx file (content-type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet) and not an HTML page or redirect. If you see HTML/“Access Denied,” ADF will download invalid content and NPOI will choke.

Explicitly set sheetName or sheetIndex • In your Excel dataset’s properties, specify either sheetName (e.g. "Sheet1") or sheetIndex (0‐based). Without one of those, ADF can’t determine which worksheet to read.

Watch out for timeouts on large files • HTTP datasets have a ~100 second timeout to load the entire workbook into memory. If your file is large (>100 MB) you might hit a timeout or partial download. Workarounds: – Use a Data Flow source instead of Copy/Preview (it supports streaming). – Spin up a Self-Hosted IR and do a Copy Activity from HTTP → Blob Storage, then point your Excel dataset at the blob. – Break the workbook into smaller files or convert to CSV if feasible.

Stage via Blob Storage as a fallback • If HTTP still refuses to deliver a clean stream, create a Copy Activity that downloads the file from HTTP into Azure Blob. Then your Excel dataset can reliably read from Blob Storage without HTTP timeouts or redirects.

Give those a try and let me know which one works for you!

Reference list

1. Disable security protection on Excel file https://support.microsoft.com/azure-data-factory-enable-editing-remove-protection
2. Excel file format in Azure Data Factory & Synapse (HTTP connector notes) https://learn.microsoft.com/azure/data-factory/format-excel#dataset-properties

Answer 2

Azure Data Factory can read Excel files over HTTP, but the file must be a real .xls or .xlsx binary and the HTTP endpoint must return the file stream reliably. The error indicates that ADF is trying to open the stream as an Excel ZIP package and either:

• The content returned by the SharePoint download URL is not a valid Excel binary (for example, HTML, JSON, or a redirect page), or
• The HTTP connection is being closed before the full file is read.

For CSV, ADF is more tolerant because it just reads text; for Excel it must parse the full ZIP/OOXML structure.

To make this work:

1. Verify the download URL actually returns the raw .xlsx file
   • Paste the same URL used in the HTTP dataset into a browser or a tool like Postman.
   • Confirm that it downloads the file directly and that the downloaded file opens in Excel without any repair prompts.
   • If the response is an HTML page (for example, a SharePoint UI page, auth/consent page, or redirect), ADF cannot treat it as Excel.
2. Ensure the URL is the true file-download endpoint
   • Use the SharePoint “direct download” link (typically ends with ?download=1 or similar) rather than a view or sharing page URL.
   • Remove any UI-only parameters that cause SharePoint to return HTML instead of the file stream.
3. Confirm the file format
   • Make sure the file is saved as .xlsx or .xls (not CSV, XML, or “Strict Open XML Spreadsheet” or other formats) because ADF’s Excel dataset supports only .xls and .xlsx.
4. Check HTTP connectivity and timeouts
   • The message The request was aborted: The connection was closed unexpectedly indicates the HTTP server (SharePoint) or an intermediary is closing the connection.
   • Ensure the file size is reasonable and that network/firewall/proxy settings allow long-lived downloads from that URL.
5. Configure the Excel dataset correctly
   • type must be Excel.
   • Use the HTTP linked service and set location to the HTTP path.
   • Set either sheetName or sheetIndex. If sheetName is used, ensure it exactly matches the worksheet name in the workbook.
   • Optionally set range and firstRowAsHeader if needed.

If the URL returns a valid .xlsx stream and the connection is stable, ADF will be able to infer or use the specified sheet name and show a preview. If it still fails, download the file locally from the same URL and compare its content/size to what is expected; any mismatch indicates the URL is not returning the correct file content for ADF.

---

References:

• Excel file format in Azure Data Factory and Azure Synapse Analytics
• Troubleshoot connector and format issues in mapping data flows in Azure Data Factory
• SharePoint and OneDrive for work or school files import