Import SQL statement results in existing excel file

[HaBiMuc]

Hi all,

I created a flow with PAD. Beginning with open a sql connection, follows sql-statement, opening a new excel-file and export the result of the query into it. This flow ist scheduled, so that every week a new excel file is created.

Question 1: It is possible to format this new excel file after dataimport like a table with header, row-colors etc?

Question 2: It is possible to import the query result in a existing excel file an save it under new filename?

Thanks für your help.

Regards, Habimuc

[thbeatty1]

1. I use VBScript to format Excel documents. For example, this will format the columns A:P to have a grey cell color, bold text, autofit, and add a currency format to columns K:P.

You'll likely be able to find whatever formatting you're looking for online, for example the cell colors are listed at https://docs.microsoft.com/en-us/office/vba/api/excel.colorindex

2. You could use the Rename file(s) action to rename the file immediately after saving it. 

Alternatively, you could 'save as' while closing, then delete the original file right after saving the new one (if you don't care about maintaining original document for versioning).

[VJR]

Hi @HaBiMuc 

You have multiple options one of which you can follow as suggested by @thbeatty1 to use vbscript.

Another option is to have an already formatted Excel (with table and row colored, headers, etc). You can also keep the spacing of columns depending on how your data is going to be. Lets call it your Excel template.

Then after fetching the SQL results, make a copy of the Excel template into the new destination file you want using "Copy Files" action. Give it a name and write your QueryResult datatable to this new file which is already colored.

With vbscript you have greater flexibility of coloring/formatting cells and also doing an Autofit of columns after the data is written to Excel.

Do write back if you need more assistance.

[HaBiMuc]

Thanks @thbeatty1 , this is my next step development 😉 vbscript is not my home base, but I'll try this.

Next problem is, that I need the header-column of the table bit the query only contains the data, so I'll try the way of @VJR with an excel template.

HaBiMuc

[HaBiMuc]

Hi @VJR , thanks for this option. I'll try this with an excel template, hoping to select cell A2 for the first input, 'cause I have an template with table-header and filtering-option. My I come back to your help. Regards, HaBiMuc

[HaBiMuc]

Hi @VJR ,

finally I could test your tip. Had problems with the connection to the sql database for a long time.

It has now worked great. However, I do not really know why 🙂

I created a template file and copied it via PA as suggested. However, I then have problems to write into this copy. Because PA requires a file at the target for the SQL result. I guess it doesn't work to define the copy as ExcelInstance and then use the ExcelInstance as target. PA requires a file name here. But since I write the copy into a new directory, there is no file to select.

I tried it like this: PA should open my template file, write the result starting from column/row A2 and then write it into a new directory and send it via email. That worked quite well.

But I would still be interested in your way with the copy. Since I use the German version of PA, I am not yet sure whether I use the correct templates / steps from PA. Could you possibly make screenshots. Thanks for the help.

Greetings
HaBiMuc

[VJR]

Hi @HaBiMuc 

- Open SQL connection

- Execute SQL statement

• This will give a Datatable called as QueryResult

- Close SQL Connection

- Copy file(s) action to make a copy of ExcelTemplate

• This gives an output variable called CopiedFiles
• Rename CopiedFiles as your new file - lets say FinaReport.xlsx

- Launch Excel for FinaReport.xlsx

• This will give ExcelInstance

-  Use Write to Excel Worksheet to write the QueryResult datatable to this new file FinalReport.xlsx using ExcelInstance

Let me know if this is not what you are expecting.

Regarding your Question1, I have added a cookbook post for a wide range of Excel formatting features which also includes giving colors to headers and rows. It uses vbscript. You simply need to call it by passing True/False values and not write any script, but if as you said vbscript is not your forte then you can ignore it :).

[Ahammad_Riyaz]

Hi @thbeatty1 ,

You can refer this, in the template excel you can store the headers and all.

After you can use write into same excel and use save option to store in diff location.

Regards

Ahammad Riyaz

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.