I'm trying to read data from Excel File1 using SQL and paste selected columns from the query result to Excel File2. To make things a little trickier, there might be different column names, so I need to provide mapping (e.g. [Date] -> [DateAdded])
As I understand, I cannot open two Excel files with the same connection, so it seems I need to:
Thanks in advance
Solved! Go to Solution.
Whereas SQL Select is usually my preferable way of pulling data from Excel thanks to the ease of filtering, match etc, writing back I usually address with Write to excel @VJR suggested.
Not sure what logic you are building, so not sure if this will be of any help for you, but what you could do is:
Instead of two separate excel files, operate on 1 with 2 Sheets.
In such case you can open 1 SQL connection and each sheet is treated as a separate table, so you can then execute usually SQL goodies such as table JOINs etc.
But from what you wrote, you just need to pull data from file1, change column names, and paste it to file 2.
So I would use something like this:
1. SQL -> pull data from file 1, changing the column names to desired names at the same time, store it in %ExcelData% variable.
Select [Date] AS [DateAdded], [AnotherColumn] AS [AnotherColumnAddes]
2. Excel action (not excel) -> Paste data to file2 in a way you want to structure data
If you need to paste the column headers, then write %ExcelData%.ColumnHeadersRow to the first row, and the %ExcelData% to row 2
In the targe Excel to paste data from queryresult(datatable) I want to write an entire datatable column in one go. Do you if it is possible? I was trying Write to Excel Worsheet action with %SomeDataTable['ColumnName']% as a value to write, but it did not work