Answered

Execute SQL against Excel without headers - reference columns by index

(0) Share

Report

Posted on by Beaner

I'm executing SQL against an Excel spreadsheet that does not have the headers in the first row.

I want to use SQL due to the speed and in order to group this timesheet data to total hours for each contractor for the week.

This work:
Select *
From [hours$] hr
where [# Generated On: 08/25/2023 22:28:13 UTC] Not Like '#%%'

But obviously, the first row will have a different date/time each run. Plus, it puts the actual header in the resulting dataset.

Is there a way to reference the columns via index in PAD, something like the below? This syntax does not work.

Select *
From [hours$] hr
Where hr{0} Not Like '#%%'

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Agnius Bartninkas Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
You should modify your SQL connection string to not include the first row as a header. I assume you use something like this:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

If so, you should switch to "HDR=NO", which will tell PAD that the first row is not a header row.

You will then get column names assigned automatically. I believe they will be called F1, F2, etc. Try running a query like that once without any filters to see the column names that it will return. Then you can use those generic names in your filters.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No
Verified answer

Beaner 18 on at

Like (0)

Report
Copy link

Link copied!

Thank You. Works Great.

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

If you feel like I answered your question, please accept my reply as a solution to the topic.

Thank you.

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You have accepted your own answer as a solution, while the solution was provided by me. I would appreciate it if you marked my own answer as the solution, too. I get some karma points to being a super user for providing solutions. Thank you.

Was this reply helpful? Yes No