Runnig SQL queries from Excel spreadsheet

Question

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?

apathetichell · Answer

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.

sancheh · Answer

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 · Answer

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.