Loading XSLB [Macros based excel file but binary extension] into Azure Synapse using ADF

Question

Hello Guys, I am working on integrating XSLB file into Synapse. Just to give you all a background on the XSLB file. A complex application is being developed using XSLB and used by more than 600 users on a daily basis across 200 files ( same format). My aim is to load them into Synapse as it is for business to use then for their operational reporting at the end of the day. The XSLB files will be available in the blob and was planning to load them into Synapse. When I was trying to create the pipeline I got error that this binary file is not supported.

Hence I had to adopt a different approach to load them into the Synapse.

However, i was looking for some readymade solution through ADF to load them. Is there anybody in this forum who encountered this type of problem and how you resolved them?

Answer 1

Hi @Sanjay Dinda ,

Thank you for posting query in Microsoft Q&A Platform.

Could you please clarify what you mean when you say wants to load in to Synapse?

You mean to load file in to Synapse attached ADLS gen2 account? If yes, you can consider creating a synapse pipeline with copy activity. Use binary format type dataset in source and sink tabs of Copy activity to load file as it to storage account.

If your requirement is connecting to XLSB file and reading data inside it (not loading directly as it is) by selecting sheets then it's not possible as XLSB formatter is unsupported directly in ADF or Synapse pipelines at this moment.

You can consider converting .xlsb file to excel file first and then creating a dataset for that excel file in ADF or Synapse Pipeline to read it.

Hope this helps. Please let me know if any further queries.

------------------

Please consider hitting Accept Answer button. Accepted answers help community as well.