Runnig SQL queries from Excel spreadsheet

[sancheh]

I have an Excel document containing 200-500 different SQL queries, with each row containing a query. I would like to create a workflow that reads and executes all of these queries against a Microsoft Access database table, then saves the query results in a separate Excel spreadsheet. Is this possible, and can someone help me with it?

[shancmiralles]

if there are repetitive queries i suggest a macro to lessen the burden.. from how i see it.. breakdown data according to how they are going to be queried (?) then do a macro..  
try this link if it helps.. 
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Execute-a-large-list-of-querie...

hope i somehow shed some light on your inquiry!
@sancheh 

[apathetichell]

look up batch macros on this community and learn how to use them. you can basically batch your queries and send them in via dyanmic input in-db or by changing your query statement in input data - and cutitng off this question - I have zero interest in providing you a workflow which does this because my databases are not your databases.

[sancheh]

Hey, thanks for the link! I managed to get it partially working. In my SQL Queries, I have a column called Units. When I include "Units = 'ug/mL'" in the syntax, I get no results even if there are records to return. But when I remove "Units = 'ug/mL'", I get results. Any idea what could be causing this?

work, 

[apathetichell]

I would recommend rechecking that that specifc value exists in that specific database in that specific field. My hunch is that something is off on your case. If you can find the value in your db - that would point to either an encoding issue by the driver of the special character or that your table is actually a view and the odbc user does not have permission to see the underlying values in that table. 99 times out of 100 the issue is user error (ie the value or column name is different in the DB). If that is not the case - turn on logs at your odbc driver level. re-run the query - see what is being sent to the DB - and what is being returned.

[sancheh]

so, if i run this query:

SELECT '2888ABS' AS DeID, * FROM raw_data WHERE Units = 'ug/mL' 

I get no results

But if i modify the query by replacing = with LIKE and use a while card % instead of / I get all results.

SELECT '2888ABS' AS DeID, * FROM raw_data WHERE Units LIKE 'ug%mL'

Do you have any ideas on what could be causing this? i have more than 4,000 queries like this.

thanks in advance.

[apathetichell]

LIKE is a fuzzy match = is an exact match. your values are not exact matches. look at the results and see what the values in your DB are. Maybe the / character you think you see is a different character which looks like /? if you see the fix is to use % - just bulk change your queries in formula to use LIKE instead of = if you are happy with the results. use a regex_replace to replace the / with % if you need to.