question

PrateekGupta-0172 avatar image
0 Votes"
PrateekGupta-0172 asked MartinJaffer-MSFT commented

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

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, Dictionary`2 context) at NPOI.POIXMLDocumentPart.Read(POIXMLFactory factory, Dictionary`2 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-data-factoryazure-blob-storageazure-files
image.png (67.3 KiB)
image.png (31.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @PrateekGupta-0172 and welcome to Microsoft Q&A.

Out of the first error message, I think the important part is Message=Illegal characters in path . In the Error details below, it also says it does not support encrypted excel files.

There are four things I can think of to check:
1. Is the file encrypted? Remove encryption.
2. If the file is compressed, have you selected the correct compression settings in the dataset?
3. Are the sheet / tab names valid?
4. Is the file path valid?

Beyond that, I would need to take a look at the file. Details will follow on how to send to me. Don't forget to redact any sensitive information!




1 Vote 1 ·

@PrateekGupta-0172 I haven't heard back from you after my investigation. If we can consider this resolved, please mark as accepted answer. If not please let me know how I may better assist.

0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.