How to remove the leading "0"(ZERO) in the CSV file data row value in tMap to compare between the INNer join?

[Ashok200792]

We are facing to remove the leading "0"(ZERO) in Upload data file values in tmap function how to handled the Leading "0" to remove 

Ashok

[TRF]

Hi,

Based on your previous case, you can get the 6 right characters:
StringHandling.RIGHT(row1.invoiceno,
6)
or if you want to remove all leading 0:
valueOf(Integer.parseInt(row1.invoiceno))

[Ashok200792]

Yes , I try But the data cannot be transfer to output file

Function we called:

StringHandling.valueOf(Integer.parseInt(row1.invoiceno))

Sample Output Data:

0912033181|20171101|F2|1000|03|0006120211|36|5100416125|3.000|6165.00|        80.400|        83.550|        60.000

I have attached the screen flow of that sample problem ,

can You guide me next further process

Ashok

Screenshot from 2017-11-17 13-05-23.png
Screenshot from 2017-11-17 13-05-14.png

[TRF]

Because you use the filter expression.

To transform the value, use the tMap output side with the following syntax:

String.valueOf(Integer.parseInt(row31.invoiceno))

Here is the picture:

[Ashok200792]

We used this function working fine , but getting error 

String.valueOf(Integer.parseInt(row31.invoiceno))

sample Input data:

0912033181|20171101|F2|1000|03|0006120211|36|5100416125|3.000|6165.00| 80.400| 83.550| 60.000
0919065685|20171101|F2|1000|02|0006190003|22|5100416082|2.000|2564.00| 24.000| 25.340| 18.000

2430605085|20171101|F2|1000|07|0006400007|24|5100416120|30.000|11340.00| 31.800| 36.300| 30.000
2430605086|20171101|F2|1000|07|0006400080|24|5100416115|14.000|5876.00| 47.220| 51.940| 34.600

RunJOB log:

[statistics] connecting to socket on port 3458
[statistics] connected
Exception in component tMap_1 (DB_connection)
java.lang.NumberFormatException: For input string: "2430605085"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at java.lang.Integer.parseInt(Integer.java:583)
at java.lang.Integer.parseInt(Integer.java:615)
at local_project.db_connection_0_1.DB_connection.tFileInputDelimited_1Process(DB_connection.java:1879)
at local_project.db_connection_0_1.DB_connection.tFileList_1Process(DB_connection.java:2348)
at local_project.db_connection_0_1.DB_connection.runJobInTOS(DB_connection.java:3576)
at local_project.db_connection_0_1.DB_connection.main(DB_connection.java:3425)
[statistics] disconnected
Job DB_connection ended at 14:21 17/11/2017. [exit code=1]

Sample Output data:

0912033181|20171101|F2|1000|03|0006120211|36|5100416125|3.000|6165.00| 80.400| 83.550| 60.000
0919065685|20171101|F2|1000|02|0006190003|22|5100416082|2.000|2564.00| 24.000| 25.340| 18.000

Only two Row data came to output file.

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

Yesterday we solved that issue, but we need to remove leading 0 before check in that Input data or Not an account that leading 0(Zero)

So how to handle this function and error, where to we use can you guide me , and then

0919065685|20171101|F2|1000|02|0006190003|22|5100416082|2.000|2564.00| 24.000| 25.340| 18.000

2430605085|20171101|F2|1000|07|0006400007|24|5100416120|30.000|11340.00| 31.800| 36.300| 30.000

Some times Leading 0 is not come to the Input data file , how to match the data to database.

Screenshot from 2017-11-17 14-27-57.png

[Anonymous · Accepted solution]

Hi,

Try this :

String.valueOf(Long.parseLong(row31.invoiceno))

Because Int accepts values between : -2147483648 et +2147483647 (-231 et 231-1)

So the first 2 lines are ok  : 912033181 < 2147483647 and 919065685 < 2147483647 but  2430605085 > 2147483647 so if you cast invoiceno as Long, the result will be ok.

I hope this answer will help you.

TD

[TRF]

The value exceed the int max value.

Replace the expression by the following:

row31.invoiceno.replaceAll("^0*", "")

[Ashok200792]

Thanks a lot , its working .