topic Re: Importing/updating DB from CSV in Talend Studio

Importing/updating DB from CSV

_AnonymousUser — Sat, 16 Nov 2024 12:08:51 GMT

Hi,
I'm trying to import/update data from a csv file into MySQL database. The csv file will always contain a few records already stored in the database. How can I use Talend to import new records, but update/or ignore already existing records?
Should I use the tMysqlOutput component or tMap?

Re: Importing/updating DB from CSV

Anonymous — Tue, 20 Nov 2012 00:02:21 GMT

Hi,
You can use the tMysqllBulkExec. You will find a way to update your data in the advanced settings.
This component only requires a delimited file in order to insert/update the data into the database.
Using the tFileInputDelimited --> tMap --> tMysqlOutput is another valid solution, but could be less efficient.
HTH,
Rémy.

Re: Importing/updating DB from CSV

_AnonymousUser — Tue, 20 Nov 2012 03:11:07 GMT

tMysqlBulkExec does not seem to work, because it requires a key. My db table does not have a primary key. I get the error:
Exception in component tMysqlOutputBulkExec_1_tMBE
java.lang.RuntimeException: For bulk update, Schema must have a key at least.
at miljogrep_eos.test_0_1.test.tFileInputDelimited_1Process(test.java:1130)
at miljogrep_eos.test_0_1.test.runJobInTOS(test.java:1425)
at miljogrep_eos.test_0_1.test.main(test.java:1290)
I assume this component wants to do the matching on this key, but I need to do the matching on a date field.
Instead I've tried to use a tMap with a outFilterRejects output like your tutorial:
http://www.talendforge.org/tutorials/tutorial.php?language=english&idTuto=9
This works fine, except I run into trouble when the dates on the inputs are stored different. The dates sometimes are stored as "2012-03-25 02:00:00.000" and sometimes as "2012-03-25 02:00:00.0". I've tried a workaround using StringHandling function, but no luck. How can I make this work? Is there another function I can use? Please see screenshot.

I'm pretty new to Talend, so any help would be greatly appreciated.

Re: Importing/updating DB from CSV

Anonymous — Tue, 20 Nov 2012 07:06:21 GMT

Transform text fields containing date formatted values into fields with the data type Date and match them.
You can do this with the routine TalendDate.parseDate("yyyy-MM-dd HH:mm:ss", yourTextDateField). Change the pattern for every source to fit the incoming format.

Re: Importing/updating DB from CSV

Anonymous — Tue, 20 Nov 2012 23:22:23 GMT

Excellent. Works like a charm. Thanks!