Posted Thu, 10 Jun 2021 05:17:51 GMT by

I have a MS Excel Spreadsheet that contains 2 columns.  Column 1 contains state name and Column 2 contains assets.  I need to be able to read the MS Excel file and only grab the rows that contain a certain state.  OR  Pull in all records into a Dataset and add criteria in the "Write to File" step.  Currently I can only pull in all records from the MS Excel spreadsheet and output all of them into a new file.  I can not figure out how to add a "where" statement or filter anywhere.  The easy way out is to have the provider of the MS Excel Spreadsheet to only send the records I want, but this is not possible.   

Posted Fri, 11 Jun 2021 10:00:34 GMT by

Hi Chris,

It's possible to query Excel files.  Excel and Automate will need to be the same bit-version and you'll need to install the MS Access Database Engine.  You can then use the "SQL Query" action in Automate and select the "Connection String" type option.

You can find connection strings here (look under the "Microsoft ACE OLEDB" section for Excel).

 

Here's an example step i was able to use to query a XLSX file:

 

Posted Fri, 16 Jul 2021 04:07:26 GMT by

Devin, this worked to the point of where do I add the criteria.  I need to add a "where" clause to only pull in certain states.  I tried to add this without luck.    RESULTDATASET="ds" SQLQUERY="select * from [Sheet1$] where A = 'MISSOURI'".  Meaning I want to scan the entire column A and only return the values of 'MISSOURI'.  I really appreciate all your help, I am at a loss with the criteria/where statement syntax while using MS Excel as the data source.

 

Posted Sat, 17 Jul 2021 07:03:01 GMT by

Hi Chris,

The article helped me a lot:

https://querysurge.zendesk.com/hc/en-us/articles/205766136-Writing-SQL-Queries-against-Excel-files-using-ODBC-connection-Deprecated-Excel-SQL-

Just looks like column names need to be in brackets.

Here I got the data from the "NAME" column in my Excel file:

 

 

and here I got the records where NAME = 'Devin':


select * from [Sheet1$]
where [NAME] = 'Devin'

Posted Tue, 27 Jul 2021 22:59:28 GMT by

This worked great.  thanks so much.

You must be signed in to post in this forum.