topic Before Insert csv data need to check the mysql database that column value is exit are not? in Talend Studio

Before Insert csv data need to check the mysql database that column value is exit are not?

Ashok200792 — Thu, 16 Nov 2017 10:45:37 GMT

Before Insert the CSV data need to check the Mysql data base and the column value is exist in that table, if that value is exist in that table,particular column of row data cannot insert into the database.

We not allowed the duplicated records in that database, before load the file we need to restrict this data

Thanks Regards,

Ashok M

Re: Before Insert csv data need to check the mysql database that column value is exit are not?

TRF — Thu, 16 Nov 2017 10:48:31 GMT

Hi,
What's the question?

Re: Before Insert csv data need to check the mysql database that column value is exit are not?

Ashok200792 — Thu, 16 Nov 2017 10:51:41 GMT

Before we upload the CSV data Need to check the Mysql database table, any existing data is in that CSV data file column they ignore that row data, and non- existing data only need to store

Re: Before Insert csv data need to check the mysql database that column value is exit are not?

TRF — Thu, 16 Nov 2017 11:09:47 GMT

As you have a inner join in tMap with lookup rejected catched, just remove "row1.invoiceno != row2.invoiceno"

Should be enough.

Re: Before Insert csv data need to check the mysql database that column value is exit are not?

Ashok200792 — Thu, 16 Nov 2017 11:13:24 GMT

YES , I removed That "row1.invoiceno != row2.invoiceno" but still allowed that data to next tlogRow_1

Re: Before Insert csv data need to check the mysql database that column value is exit are not?

TRF — Thu, 16 Nov 2017 11:15:46 GMT

Manually compare how invoiceno are represented on both flows.

Re: Before Insert csv data need to check the mysql database that column value is exit are not?

Ashok200792 — Thu, 16 Nov 2017 11:37:34 GMT

This Is sample data (CSV FILE FORMAT) :

0921076505|20171021|F2|1000|02|0006180001|22|5100408998|30.000|11880.00| 29.250| 42.900| 29.250

This Is sample data (DATA BASE TABLE) :
| 1 | 921076505 | 2017-10-21 | NULL | NULL | F2 | 1000 | 2 | 0006180001 | 22 | 5100408998 | 30 | 11880.00 | 29.250 | 42.900 | 29.250 | 2017-11-15 14:17:54 |

Program executed output:

Starting job DB_connection at 16:41 16/11/2017.
[statistics] connecting to socket on port 3439
[statistics] connected
0921076505|20171021|F2|1000|02|0006180001|22|5100408998|30.000|11880.00| 29.250| 42.900| 29.250
0921076505|20171021|F2|1000|02|0006180001|22|5100408998|30.000|11880.00| 29.250| 42.900| 29.250
[statistics] disconnected
Job DB_connection ended at 16:41 16/11/2017. [exit code=0]

BUt we dont don't to allowed the Duplicate record in that database .

Re: Before Insert csv data need to check the mysql database that column value is exit are not?

Ashok200792 — Thu, 16 Nov 2017 11:43:59 GMT

Any alternative method to validate the exits records to check the table and allowed non- exist record only after inserted into to corresponding table.

Ashok

Re: Before Insert csv data need to check the mysql database that column value is exit are not?

TRF — Thu, 16 Nov 2017 12:31:17 GMT

As you can see, invoiceno have a leading 0 (0921076505) but not the db invoiceno (921076505) so records don't match

Re: Before Insert csv data need to check the mysql database that column value is exit are not?

Ashok200792 — Thu, 16 Nov 2017 13:12:27 GMT

THANKS A LOT ,

Ashok