JSON, SQL I/O

[denisfi]

Hi guys,

I’ve some projects that the IT team create the script as Json and SQL and I dind’t see anything into I/O to read these files and inject or transform as string to be used at another nodes. It can simplify some steps as create tables, use json templates/data directly without open a node and manipulate it inside.

Can exists any file I/O to read these type of files and used it easily?

Tks,

Denis

[ArjenEX]

For JSON, you can use the dedicated import node as part of the JSON Processing extension.

JSON Reader

For SQL’s I would use a line reader, concat all rows together in a groupBy node and then pass it as flow variable to a query reader.

[denisfi]

Hi guys, thanks for the answer, but i’d like for example, use I/O as csv, text file for sql files and xml. The node for json is gre4at, but not exist for SQL and XML as well.

The IT team creat sql querys for different needs, and reply it for the team as a file (dump) with all information and commands that could be used into a DB Query node to simplify the process.

Some of them need to truncade tables, delete and create tables… so, as a solution passed before to read line by line to create a concatenated string will be terrible.

If is possible to create a node to read a file and bring it as a unic string information, I could put it as variable and apply into other nodes without modifications… If the IT team make some changes, will stay on backgrounds.

Sorry if my english wasn’t clear.

BR,

Denis Iongbloed

[mlauber71]

@denisfi from what I understand you want to upload a file with something like SQL commands that might be inside a JSON file (for structuring) and then execute them via KNIME towards a SQL database.

There is a node that can inject queries. But they may not be suitable to create or change tables.

DB Query Injector

Another option indeed would be to use the

DB SQL Executor

And maybe have the SQL ready to go or add some wrappers around.

Maybe you can confirm what you want. And if you are not comfortable in english you could just write in your language and someone might just use Deepl or ChatGPT to translate (which you can also do at any time).