Importing/updating DB from CSV

One Star

Importing/updating DB from CSV

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?
Employee

Re: Importing/updating DB from CSV

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.
One Star

Re: Importing/updating DB from CSV

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.
Seventeen Stars

Re: Importing/updating DB from CSV

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.
One Star

Re: Importing/updating DB from CSV

Excellent. Works like a charm. Thanks!

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now