Dynamically run sql queries based on tables found in .csv files

I have 12 folders which each contain a .csv file. Within each .csv file there may be one or more database table names (column labeled FullTableName). I would like to read the 12 .csv files, dynamically read the database table names within each file and use them to populate a sql query which selects all records from each database table. In addition, for each record that it extracts, i would like to add and populate a new column, FullTableName, with the FullTableName from which it originated from.

File attached is a sample .csv that contains 6 sql database/table names.

Load_CRTRAD_032022_08162022.csv

Input

Dynamic Processing

Database Connection

Accepted answers

mschmeiser

I actually found another solution to this problem. The Dynamic Input tool allows me to use the Modify SQL Query radio button to replace a specific string AND pass a field to the output. I used the latter option to add the ServerName, DatabaseName & TableName (which i had derived earlier in the process) to the output tool which allowed me to associate the output records to the applicable table in which they originated.

All comments

dougperez

You can do that with dynamic input:

mschmeiser

How do you get the FullTableName from within the .csv file added to the results/output?

dougperez

Read the .csv first, then put a dynamic input with these configurations

table.yxmd

mschmeiser

I changed the input location of the .csv, updated the odbc connection and ran the workflow. I would've expected the dbo.table name data to be added at the end of the results, but the results did not contain the dbo.table name column. I am using v2019.4. Is that why it's not working?

dougperez

Change the query to:
SELECT 'dbo.table' as tablename, * FROM dbo.table

mschmeiser

I originally used the "select * from dbo.tablename" where dbo.tablename was replaced with the 'fulltablename' from the .csv file. The workflow ran fine, but did not contain the tablename applicable to the record. When i added 'dbo.tablename' as tablename, an ran the workflow, i am receiving an error (The file "" has a different schema than the 1st file in the set) on some of the files within the .csv. I have verified that the columns within each of the files are the same, so i don't understand why i only receive the error when i add the tablename to my output.

dougperez

In this case you should use a batch import macro to read all csv files:
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

apathetichell

I would use dynamic input in-db as seen in the attached workflow & macro.

DB PULL OUTER WORKFLOW.yxzp

mschmeiser

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703