Six Stars

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Five Stars tdz
Five Stars

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

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

Twelve Stars TRF
Twelve Stars

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

The value exceed the int max value.

Replace the expression by the following:

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

TRF
7 REPLIES
Twelve Stars TRF
Twelve Stars

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

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))

TRF
Six Stars

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

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

Twelve Stars TRF
Twelve Stars

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

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:

Capture.PNG

 


TRF
Six Stars

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

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.

 

Five Stars tdz
Five Stars

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

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

Twelve Stars TRF
Twelve Stars

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

The value exceed the int max value.

Replace the expression by the following:

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

TRF
Six Stars

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

Thanks a lot , its working .