topic Re: How do I create an excel file as an output in Dataflux? in SAS Data Management

How do I create an excel file as an output in Dataflux?

beamer108 — Tue, 09 Mar 2021 15:29:45 GMT

I have a datajob in which I would like the output to be an excel file vs .csv.

Im using a data target insert node but having trouble understanding what I need to set up in the adv properties to get it working correctly. I do understand that if there is an existing excel file out there already you have to delete that before creating a new. I just can't seem to set up the dsn correctly (or other adv properties).

Thank you in advance.

Re: How do I create an excel file as an output in Dataflux?

TonyL_ — Wed, 10 Mar 2021 19:17:03 GMT

Here's what I usually do.

Create a new XLSX file or use an existing one.
Go into the 32-bit ODBC admin or click on New ODBC Connection from the Data Connections area in DataFlux.
Add a new User or System DSN, selecting the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
Point it to the spreadsheet from above and make sure you have "Read Only" UNchecked.

In your DataFlux job, use the Data Target (Insert) node:

Navigate to select the Data connection.
Select the new connection you created above.
Click on it and click the green plus + to create a new output tab name.
Make sure "Delete Existing Rows" is UNchecked in your Insert node.

Running your job should automatically create a new Tab and Named Range in your spreadsheet.

Also, I don't think Excel allows the job to automatically delete or alter the data or range (hence my final bullet point above), so I always clear our my data before each run, either programmatically in DF or manually.

Re: How do I create an excel file as an output in Dataflux?

beamer108 — Thu, 11 Mar 2021 13:12:05 GMT

Thank you!