How to save to excel sheet thousands of matrices

[zizoo]

I have 10000 matrices created in a loop in my workflow that I would like to export in one single file like an excel sheet.
My goal is to make the import and export of these N matrices easy.
If not possible in Excel, is it possible to save in a different format?
Thanks

[izaychik63]

It would be helpful if you explain how you plan to use these matrices. Excel for me sounds odd. For data collection database like SQLite or MS Access will be more appropriate.

[zizoo]

I am open to suggestions.
To make it simple, I have thousand of tables that I would like to save in one file and to make their calling easy.
For example, I would like to search for the maximum in table 234 or find the mean in table 278.
I would like to make the access to this data easy for non databases expert.
Maybe SQLite is a must.

[mlauber71]

One idea could be to use an H2 database (which KNIME supports). They come in two flavours. One is operated in-memory which should make access very fast. The downside would be you would have to load all your files into memory every time you fire up your computer. Also, you can store the tables in a single file like SQLite - from what I read H2 databases should be very effective in dealing with a lot of tables. Not sure what will happen when you feed them 10.000.

Also, H2 can have different schemes (Like PUBLIC in the example) that might help you organize your tables into categories. INFORMATION_SCHEMA helps you see and access which datasets you already have so you could use that to organize the access.

Of course, you would have to see what happens when you stress that concept. Under ‘Advanced’ you will find additional settings that might help you tweak the performance.

kn_example_h2_database.knar (50.2 KB)

[zizoo]

Hi @mlauber71

I created an example using the H2 DB reader that you mentioned.
Is it possible that you help me to complete it.
My goal is to append a table to an initially empty database.
Then I would like to call it and apply a specific manipulation to each table of the database.
I used a generic table creator.DBtest.knwf (28.5 KB)

Thanks,
Zied

[mlauber71]

I think you should come up with a working example that represents what you want to do or explain it in a consistent way. The workflow is a non-working mixture of in-memory H2 database and a local database that does not exist.

What is not clear from your description do you want to have a database with the same structure and append data to it *1) or would it be several tables with different structures?

I would suggest you prepare some sample data and try to come up with some sort of plan what you want to do.

*1) if you want to append data to an existing table you have to uncheck this item

[zizoo]

Hi @mlauber71,
Actually, in each iteration the database should be updated by appending a table that has different sizes.
So in iteration zero, the database is empty and a table will be added, for example of dimensions 5 rows and 10 columns. in the next iteration, the database has the previous table and a new table of dimension 65 rows and 43 columns are added to the databse , and so on.
Once I have the full database, I would like to target a specific table in the database and find its maximum value or its average value.
If it is possible, I would like to also to do the previous step for all the tables in the database.

[zizoo]

Hi again @mlauber71,
I attach here a new workflow where I managed to write a database locally ( I see to files with different sizes and have the extension .trace.db and mv.db). I am not sure which one is the actual database.
Then I added H2 connector and DB query reader to import back the database to the workflow. In this step, I could not see the original tables that I created and I am not sure how I can call a specific table that I added initially in an iterative way.
DBtest2.knwf (24.4 KB)

Thanks,

[mlauber71]

You put all your data into one existing table and appended it. If you want tables with different names you would have to specify their names (and schemes if necessary).

If you use the same name and location for the connection to the H2 DB you can find them by filtering by the TABLE_SCHEMA column.

You might want to put some more planning into what you want to achieve or try to explain it to us again.

DBtest2.knwf (59.6 KB)