Facing issue while converting xlsx file placed in blob to csv.

Prateek Gupta 21 Reputation points
2021-07-09T05:58:40.543+00:00

Hi,

I'm facing issue while converting xlsx file placed in blob to csv.
Since I'm unable to upload my .xslx file here , Let me know if u need file for your analysis.

Aim : convert xlsx file to csv ( main sheet : data_solution Areas)
Challenge : Since there are 2 tabs, where as Menu Sheet is causing an issue while conversion.

Error description -
Operation on target ACT_Cmn_ExcelToCSV failed: ErrorCode=ExcelUnsupportedFormat,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Only '.xls' and '.xlsx' format is supported in reading excel file while error is ' at System.IO.Path.CheckInvalidPathChars(String path, Boolean checkAdditional) at System.IO.Path.Combine(String path1, String path2) at NPOI.OpenXml4Net.OPC.PackagingUriHelper.ResolvePartUri(Uri sourcePartUri, Uri targetUri) at NPOI.OpenXml4Net.OPC.PackageRelationship.get_TargetUri() at NPOI.POIXMLDocumentPart.Read(POIXMLFactory factory, Dictionary2 context) at NPOI.POIXMLDocumentPart.Read(POIXMLFactory factory, Dictionary2 context) at NPOI.POIXMLDocumentPart.Read(POIXMLFactory factory, Dictionary`2 context) at NPOI.POIXMLDocument.Load(POIXMLFactory factory) at NPOI.XSSF.UserModel.XSSFWorkbook..ctor(Stream is1) at Microsoft.DataTransfer.ClientLibrary.ExcelUtility.GetExcelWorkbook(String fileExtension, TransferStream stream)'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.ArgumentException,Message=Illegal characters in path.,Source=mscorlib,'

113242-image.png

Request you to please look into it and let me know it's cause and also please provide me a solution for it.

113245-image.png

Regards,
Prateek

Azure Files
Azure Files
An Azure service that offers file shares in the cloud.
1,163 questions
Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
2,428 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,539 questions
Accepted answer
  1. MartinJaffer-MSFT 26,026 Reputation points
    2021-07-15T19:39:35.693+00:00

    From my investigation of the excel file, I found that one of the worksheets had invalid references and other issues. These prevented the workbook from resolving resulting values. This caused ADF to choke, preventing loading anything.

    After removing the offending worksheet, I was able to load data from the remaining sheets.

    The entire workbook must pass validation or else ADF cannot work with it. The error message was describing how it was unable to resolve references #REF!.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ryan Abbey 1,171 Reputation points
    2021-07-12T20:55:55.453+00:00

    Only '.xls' and '.xlsx' format is supported in reading excel file

    What is the name of your Excel file?

    What is the "IF" (prior activity) doing... I'm guessing that feeds in to your copy, does it apply to your Excel file and how?

    0 comments