<Help needed>How can I read a txt/sql file into one cell?

[cuimaple]

Hi,
how can I read a txt/sql file into one cell, so that I can change it to variable then flow to the DB reader, as I want to manage all SQL in files then when I have changes, all related workflow is changed.

[alpayzeybek]

@cuimaple

Hi,
how can I read a txt/sql file into one cell, so that I can change it to variable then flow to the DB reader, as I want to manage all SQL in files then when I have changes, all related workflow is changed.

Hi,

Yes you’re exactly right. If you’re maintaining a list of column names in an Excel file (as shown in your screenshot), your goal is to dynamically construct a SQL query like:

SELECT SwifferDate, FiscalYear, ShipToPartyID, …
FROM YourTable

Please find attached flow showing my approach. If you have any question, please feel free.

SQL_ColumnListExample.xlsx (13.2 KB)
SQL TXT.knwf (16.1 KB)

Best,

Alpay

[mlauber71]

@cuimaple you could take a look at these two older examples

Can I read in an external sql file and use this in a query

Hi Geoffrey,  This is definitely doable, just write your SQL to a plaintext file, and read it in with a regular filereader.  You can then convert this string to a flow variable, and use that to overwrite your "statement" node setting in the DB nodes.  I've attached a simple example.  Note that the metanode which precedes the file reader and DB reader nodes is just to create the files (DB and Query) needed for the example.  Let us know if this works for you.     externalsql.zip

Execute SQL from GIT or File?

I would suggest using some of the IO nodes from the Vernalis community contribution. If you only have 1 sql file, then following will work: [image] In this case, the edit variable is just to create a flow variable with a knime:// protcol URL to point to the example file embedded in the workflow - you can pass the file paths in as a single ‘;’-delimited flow variable, are select them in the configuration dialog. If you have lots of files, then you can loop through them using the sequence belo…