Six Stars

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

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Twelve Stars TRF
Twelve Stars

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

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

Should be enough.


TRF
Twelve Stars TRF
Twelve Stars

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

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


TRF
9 REPLIES
Twelve Stars TRF
Twelve Stars

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

Hi,
What's the question?

TRF
Six Stars

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

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
Twelve Stars TRF
Twelve Stars

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

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

Should be enough.


TRF
Six Stars

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

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

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

Manually compare how invoiceno are represented on both flows.


TRF
Six Stars

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

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

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

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
Twelve Stars TRF
Twelve Stars

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

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


TRF
Six Stars

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

THANKS A LOT ,

Ashok