question

RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 asked MartinJaffer-MSFT commented

ADF - Excel compression

I want to set up a copy pipeline to allow dynamic processing of files, some of our files have been compressed and some haven't. With flat files, if there's no compression, then providing a null value suffices, however, in Excel, this generates an error "Object reference not set to an instance of an object."

Anyone managed to get around this (that doesn't involve having two copy activities...)?


PS For MS should you listen... in your "help" files on Excel, for range it states "- A3: reads a table starting from the given cell, dynamically detects all the rows below and all the columns to the right". This is not true, it generates an error 'Index was outside the bounds of the array.'.

azure-data-factory
· 3
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 @RyanAbbey-0701 and welcome to Microsoft Q&A.

I am a little confused.
Is your source Excel, or flat file?
Which is compressed, your source or your sink?

... providing a null value suffices ...

What are you providing a null value to?


I think you are asking how to parameterize compression. (see below pictures)

add a parameter to dataset
108746-image.png
open and close the compression type and reveal the Add dynamic content option
108739-image.png
Use the parameter for compression type
108650-image.png
Pass the compression type in through the copy activity to dataset
108781-image.png

@dataset().compression




0 Votes 0 ·
image.png (48.0 KiB)
image.png (81.1 KiB)
image.png (95.0 KiB)
image.png (115.0 KiB)

How to parameterise is not a problem, the issue is that we have set the Compression up as a parameter but it will only accept a valid compression algorithm as a value. If we try specify "None", "" or null, it fails, this of course prevents us from accepting either compressed or uncompressed Excel files for the copy process

0 Votes 0 ·

Thank you for the clarification @RyanAbbey-0701 .

This sounds like you have already tried everything I would have thought of. I will need to escalate the issue internally for more ideas.

If I do not get a positive solution from the developers, then I will add this as a feature request. I will let you know once I hear back, in either case.

0 Votes 0 ·

0 Answers