Query every table at once / Treat CSV folder as one table

[Mielai]

I have a folder of csv files of which some will be swapped out often and some less often. For an easy user experience, all processing should happen in Base. The number and names of these csv files will change with time, so no direct calls to “file1.csv”, “file2.csv” etc. should happen.

So how do I dynamically refer to all tables and combine them? This should be a simple task, given that all the column names will remain the same. If required the schema could be “date, numid, user, string”. My SQL skills are too rusty for this, I naively tried with “SELECT ALL * FROM *”.

Alternatively, I want the folder with csv files be treated a single dynamic table.

[RobertG]

With csv-tables you won’t get it work. Base only could connect to one table in a query. You have to import the tables into an internal database and add all the content to one table there.

[Mielai]

Do you mean that they would have to be imported manually? I don’t see why there would be such a limitation.

[Wanderer]

@Mielai

I don’t see why there would be such a limitation

The point is: Neither your opinion or ability to see it nor the fact that something could be implemented differently will change existing code. (If my opinion would count all csv-files would have vanished long ago. )
.

@Mielai

naively tried with “SELECT ALL * FROM *”.

A SELECT * FROM file1 can show all rows of a file1.csv, if the folder with your csv-files is a registered datasource for text-tables. SQL provides UNION for the task of combining tables, but i guess the very simple driver wich connects csv to Base does not have this. Also you need a feature to list all tables.
.
So I guess you will need a solution wich uses macros to combine the files.

[RobertG]

@Mielai

I don’t see why there would be such a limitation.

Connect with Base to a csv-folder: Database Wizard → Select Database → Connect to an existing database → Text/CSV
Search for the folder, then save and proceed.
Base document will be opened, click on Queries and start Create Query in Design mode…
Choose a table and you couldn’t choose another table, because the dialog will be closed.
OK, let’s try in direct SQL.
SELECT "Table1"."Name", "Table2.Town" FROM "Table1", "Table2" WHERE "Table1"."TID" = "Table2"."TID"
You will get an error:

SQL Status: HY000
The query cannot be executed. It contains more than one table.

You have to import the content of the tables to a real database, not only a connection to different csv-files to get it working.
You will get the same error for a spreadsheet document you connect to Base.