cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichaelBoruta
Resolver II
Resolver II

SQL Excel data from datatables

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

4 REPLIES 4
VJR
Super User
Super User

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
Impactful Individual
Impactful Individual

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
Resolver II
Resolver II

@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

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

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,840)