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

Question

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,'

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

Regards,
Prateek

Answer 1

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!.

Answer 2

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?