You are not logged in.
Here is what I'd like to do:
We have about 50 Access mdb databases from customers, each with approx 150 tables. Each database has the same tables/columns, just variable rows due to usage. I'd like to be able to put all the mdb files in a folder, and use talend to cycle through all of them, outputting the total records for each table in each database to an output file for analysis. The goal is to have stats about how many total records each client has in certain tables, to see if certain areas are heavily used (for feature enhancement purposes).
Is there an easy way to iterate through each database and table? I have a bit of Talend experience, but not for this sort of project. I am thinking the tFileList and tForEach could be used for looping through the databases, but haven't seen anything that would run through the tables automatically.
Or is there some sort of SQL query/other tool I could easily run on each file to output this?
Any ideas would be great.
With tFileList, you can iterate each database file, but now there is not a tAccessTableList as tMysqlTableList to iterate each table, so you can create a tAccessTableList by yourself and share it with us. In the mean time, please report a bug on our bugtracker.
If you want only to compute statistics (and no data manipulation) like count,
try to use Talend to:
- iterate on each mdb file with tfilelist
- retrieve list of tables with "select Name from msysobjects" etc. on an sql query (but in you case the tables are the same on all files so you can also hardwire this list somewhere)
- use tflowtoiterate to iterate a count(*) on each table and output the results to where you want
anyway if you need specific generic data processing and manipulation, talend, as far as I know, doesn't have a dynamic schema so even if you are able to iterate on MDB files and get table names, you need to define 150 schema, as many as the tables present in a file.
hope it helps
Thank you for the suggestions. I had some troubles getting Talend to read the msysobjects table due to DB permissions (our DB issue, not a Talend problem), so decided to just do it manually, since it's a one time thing and a sample of 20 DBs will provide an ok sample.
Below is the SQL I ran to get the total counts from each table in the DB, in case anyone else is trying this.
SELECT MSysObjects.Name, DCount("*",[MSysObjects].[Name]) AS Expr1
WHERE (((Left([Name],1))<>"~") AND ((Left([Name],4))<>"MSys") AND ((MSysObjects.Type) In (1,4,6)))
ORDER BY MSysObjects.Name;