Toad World® Forums

Query for Excel table information

Hello all, I have been working to create automation scripts for a project and I have been basing a few of the actions on our Excel requirements documents.

I had setup the actions to select columns in the Excel document with a specific value thinking that all of our requirements documents were in the same format. Unfortunately, each document is different, so the automated action won’t always get my desired columns.

I was wondering if there was a way to be able to setup a query that would return a column name from the Excel document based on the value I am looking for.

A similar query would be this one that works in SQL Server, which can return table names, column names, data types, etc.:

select * from information_schema.columns

Trying this while querying on the Excel document connection was unsuccessful, but is there another query that might work instead?

When TDA connects to Excel it automatically makes named ranges. The first one would be named ‘AutoRange_Sheet1’. I would try the following:

Select * from AutoRange_Sheet1

Debbie

Hi Debbie,

What you have said is indeed the case, but I am only looking for 3 columns out of a larger amount generated.

The problem is that each of the documents I am looking at have these 3 columns in a different spot.

So while I could reformat all of our requirements documents to be the same (I think 60 or so documents), being able to query for the column name where the value I am looking for is would be really helpful.

I could see how grabbing all of the results with your query could work as well; though, I am also filtering out values on the columns so I would still need the column names.

Do you know what the column names are? Are they always the same?
And what do you mean they are in a different spot? Since I can’t see the file it is hard for me to visualize the problem. Can you post screenshots of an example?

Debbie

Thank you very much for your help Debbie.

I was actually able to find a work around so that the different Excel files are always imported with the data I want in the same place.

That makes it a lot easier.

Debbie