topic Re: How to transfer data from an Access database to a MySQL database in Talend Studio

How to transfer data from an Access database to a MySQL database

Anonymous — Sat, 16 Nov 2024 14:15:38 GMT

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

Re: How to transfer data from an Access database to a MySQL database

Anonymous — Wed, 13 Aug 2008 19:56:16 GMT

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

Re: How to transfer data from an Access database to a MySQL database

_AnonymousUser — Thu, 28 Oct 2010 13:23:08 GMT

Hi!
I have exactly the same need today!
Is there a method with the current talend version?
Thanks,

Re: How to transfer data from an Access database to a MySQL database

Anonymous — Thu, 28 Oct 2010 14:23:04 GMT

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.

Re: How to transfer data from an Access database to a MySQL database

alevy — Fri, 29 Oct 2010 01:12:16 GMT

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.

Re: How to transfer data from an Access database to a MySQL database

_AnonymousUser — Fri, 29 Oct 2010 09:58:38 GMT

Hi!

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.

Thanks for the replies

Re: How to transfer data from an Access database to a MySQL database

_AnonymousUser — Mon, 12 Dec 2011 08:46:04 GMT

I have exactly the same need today!!!!!!!!!!!

Re: How to transfer data from an Access database to a MySQL database

Anonymous — Mon, 12 Dec 2011 09:07:27 GMT

Hi Hirecrishecom
For more help, you may create a new topic and describe your issue.
Best regards!
Pedro