SQL Excel data from datatables

[MichaelBoruta]

Hi,

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:

1. Open connection to File1 
2. Execute SELECT query to get data into datatable
3. Close connection to File1
4. Open connection to File2
5. Do some magic SQL statement to insert query results from datatable
6. Close connection to File2

Thanks in advance

[VJR]

Hi @MichaelBoruta 

I could not fully understand your requirements.

Also, any specific reason you are not using Read from Excel1 and Write to Excel2 actions ?

[momlo]

Hi @MichaelBoruta 

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]

FROM [SheetName$]

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

[MichaelBoruta]

@VJR @momlo thanks for your suggestions. I wanted to use SQL because it is super fast.

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

[VJR · Accepted solution]

If you want to get only one column then you can use Retrieve data table column into list and write that list into Excel.