Question Re: Export Data to Excel Target in Technology Q&A

Export Data to Excel Target

dunncrew — Thu, 27 Jan 2022 20:48:59 GMT

Hi, I would like to query data and export it to an Excel XLSX target file, preferably into 4 different tabs.

I see how to export to CSV as a target, but not Excel.

Is there a way to run 4 queries, and get the results into 1 XLSX file in 4 tabs ?

Running SAPDS ver 14.2.13.2467

Re: Export Data to Excel Target

RichS — Thu, 27 Jan 2022 21:04:19 GMT

As far as I know, this is not supported by Data Services out-of-the-box.

However, it is possible using external resources. We do it in our environment by storing results in temporary, delimited text files and then calling a custom function which executes a free utility which converts each temporary file into a worksheet in an Excel .xlsx target file. It is not for the faint of heart.

Re: Export Data to Excel Target

dunncrew — Thu, 27 Jan 2022 21:30:22 GMT

What is the "free utility" ?

I see SQL Server can easily export to Excel. I hoped SAPDS had the same function.

Not encouraging !! "It is not for the faint of heart."

Re: Export Data to Excel Target

dunncrew — Wed, 09 Feb 2022 17:18:25 GMT

I was not able to find a solution in SAPDS.

I used SQL Server Integrations Services ( SSIS ) to accomplish this.

Re: Export Data to Excel Target

former_member645387 — Mon, 14 Feb 2022 06:41:59 GMT

Hi there.

DS does not support ".xslx" but you can export to an excel file in ".xls"format.

All you would need to do is specify that it is a Tab Delimited file and add extension .xls at the end on the name, as per screenshot below.

This opens in excel fine and i havent had any issues setting my export files up in this way.

Hope this helps.

Re: Export Data to Excel Target

dunncrew — Mon, 14 Feb 2022 15:02:40 GMT

Thanks for that. Can different tabs within the XLS be specified to be created & loaded with data ?