How to transfer data from an Access database to a MySQL database
Hello,
I have to use talend to transfer data from an Access database to a MySQL database. I'm trying to use tAccessInput and tMySQLOutput, and I can transfer data from an access table to another, one table at a time. I need a way to iterate through all the tables in the access database and transfer data to the coresponding mysql tables, in an automated process.
Can anyone help me with an advice as to how I can do this. I've tried to read the table names from a file but I can't manage to use the output of the file read component in order to specifiy the table and sql query for the tAccessInput.
I imagine I have to use some global variable in talend, but I dont know how.
Please help,
Cristina
Hello Christina,
this is actual not possible (from my point of view) until you have for all tables the same structure.
You can use a list of tablenames in a file (and of course a SELECT statement and so on). But the problem is the structure of the row between the components. You can use a predefined select statement to create a INSERT, which will be executed in the next component. But I do not thin that this is what you want.
If you have any further question don't hesitate to ask.
Bye
Volker
No good way to iterate through each table without a dose of custom code.
To solve this issue, I would either
1) write a simple perl script-- grab table names and columns, create mysql tables, move data.
or
2) Let Talend grab the schema's of all your input tables and then use a single input/output for each table, allowing Talend to create the tables in MySql.
I believe it is possible; I've done something like this from DB2 or ODBC to SQL Server or Pervasive but it's tricky.
Please see
my post describing the approach.
Your main problem will be getting the field definitions from Access.
This thread discusses how to generate them using VBA. Once you run that code to generate a table in Access defining the fields of all tables, you can then read from that to follow my approach in Talend.
The problem is that Access db is not a jdbc one so we don't have tableList,... I will forgot Talend and doing that in java only (i'm not familiar with perl), it will be really easier to manage with dynamic creations of tables.
