topic Dataloading issue from TabDelimited to MYSQL database in Talend Studio

Dataloading issue from TabDelimited to MYSQL database

Anonymous — Tue, 02 Apr 2019 20:07:08 GMT

Hello Friends,

I am trying to load the tab delimited file generated from multiple steps(joins/aggregation/filter). The input has 6719 records, but 6662 records are getting loaded to database. 57 records are getting rejected with the error "Out of range value for column"

The product description column data has some trailing spaces on some rows, I added expression to remove space and \t (if any), still data is getting shifted to next columns, getting rejected with above error.

Is they anything can be done to load those 57 records?

StringHandling.EREPLACE(StringHandling.TRIM(row10.PRODUCT_DESCRIPTION),"\t","")

Re: Dataloading issue from TabDelimited to MYSQL database

akumar2301 — Tue, 02 Apr 2019 20:33:29 GMT

Try once

StringHandling.EREPLACE(StringHandling.TRIM(row10.PRODUCT_DESCRIPTION),"\\t","")

Re: Dataloading issue from TabDelimited to MYSQL database

Anonymous — Tue, 02 Apr 2019 21:21:55 GMT

I tried both \t, \\t.. It is not working.

Re: Dataloading issue from TabDelimited to MYSQL database

Anonymous — Wed, 03 Apr 2019 06:12:34 GMT

Hi,

It may not be necessarily tabs or spaces. It could be due to actual values of the column especially for any integer column. Please refer the MySQL developer guide for the details.

https://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html

I would suggest you to check all the numeric columns whether they are in right range and check the length of all character columns whether they are within the DB column length. The rejected records will surely have some clue on these aspects.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Dataloading issue from TabDelimited to MYSQL database

Anonymous — Wed, 03 Apr 2019 12:56:24 GMT

Thanks for the hint Nikhil.

I double checked schema, it has enough space(length) to store it.

By seeing the error, it seems to be issue with tab,

=====================================

Starting job Copy_of_MTechProj_MSFT_Feed_Int at 01:46 03/04/2019.

[statistics] connecting to socket on port 3952
[statistics] connected
Couldn't parse value for column 'Price' in 'row1', value is '43 cm Compatibile con Surface Pro'. Details: java.lang.NumberFormatException: For input string: "43 cm Compatibile con Surface Pro"
Couldn't parse value for column 'Price' in 'row1', value is '1. Dimensioni (L x A x P): 41'. Details: java.lang.NumberFormatException: For input string: "1. Dimensioni (L x A x P): 41"
Couldn't parse value for column 'Price' in 'row1', value is 'https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE1Meso?ver=56c5'. Details: java.lang.NumberFormatException: For input string: "https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE1Meso?ver=56c5"
Couldn't parse value for column 'Discount__Price' in 'row1', value is 'https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE1MM7t?ver=10c9'. Details: java.lang.NumberFormatException: For input string: "https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE1MM7t?ver=10c9"
Couldn't parse value for column 'Price' in 'row1', value is ' Surface Book e Surface Laptop."'. Details: java.lang.NumberFormatException: For input string: "Surface Book e Surface Laptop.""
Couldn't parse value for column 'Price' in 'row1', value is ' Surface Book e Surface Laptop."'. Details: java.lang.NumberFormatException: For input string: "Surface Book e Surface Laptop.""
Couldn't parse value for column 'Price' in 'row1', value is 'https://www.microsoft.com/it-it/p/organizer-knomad-knomo-mayfair-da-105/8TPWQ7XRPQPS/8BL5'. Details: java.lang.NumberFormatException: For input string: "https://www.microsoft.com/it-it/p/organizer-knomad-knomo-mayfair-da-105/8TPWQ7XRPQPS/8BL5"
Couldn't parse value for column 'Price' in 'row1', value is 'https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE2Gn9i?ver=6c20'. Details: java.lang.NumberFormatException: For input string: "https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE2Gn9i?ver=6c20"
Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Data truncation: Out of range value for column 'PRICE' at row 1Couldn't parse value for column 'Price' in 'row1', value is '00 cm (wys. x szer. x gł.) Pasuje do modeli Surface Pro i Surface Pro 4."'. Details: java.lang.NumberFormatException: For input string: "00 cm (wys. x szer. x gł.) Pasuje do modeli Surface Pro i Surface Pro 4.""
Couldn't parse value for column 'Price' in 'row1', value is 'Thule'. Details: java.lang.NumberFormatException: For input string: "Thule"
Couldn't parse value for column 'Price' in 'row1', value is ' Surface Book i Surface Laptop."'. Details: java.lang.NumberFormatException: For input string: "Surface Book i Surface Laptop.""
Couldn't parse value for column 'Price' in 'row1', value is ' Surface Pro 4'. Details: java.lang.NumberFormatException: For input string: "Surface Pro 4"
Couldn't parse value for column 'Price' in 'row1', value is '90 x 29'. Details: java.lang.NumberFormatException: For input string: "90 x 29"
Couldn't parse value for column 'Discount__Price' in 'row1', value is 'https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE2Gn9i?ver=6c20'. Details: java.lang.NumberFormatException: For input string: "https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE2Gn9i?ver=6c20"
[statistics] disconnected

Job Copy_of_MTechProj_MSFT_Feed_Int ended at 01:47 03/04/2019. [exit code=0]

====================================

Re: Dataloading issue from TabDelimited to MYSQL database

Anonymous — Wed, 03 Apr 2019 13:01:15 GMT

Hi,

what is the data type of Price column in database?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Dataloading issue from TabDelimited to MYSQL database

Anonymous — Wed, 03 Apr 2019 13:14:29 GMT

It actually worked. All the prices were with less than 7 digits. DB has 7,2(length,precision). I changed it to 9,2. It has loaded all the data to table.

But still seeing error,

Couldn't parse value for column 'Price' in 'row1', value is '43 cm Compatibile con Surface Pro'. Details: java.lang.NumberFormatException: For input string: "43 cm Compatibile con Surface Pro"
Couldn't parse value for column 'Price' in 'row1', value is '1. Dimensioni (L x A x P): 41'. Details: java.lang.NumberFormatException: For input string: "1. Dimensioni (L x A x P): 41"
Couldn't parse value for column 'Price' in 'row1', value is 'https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE1Meso?ver=56c5'. Details: java.lang.NumberFormatException: For input string: "https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE1Meso?ver=56c5"
Couldn't parse value for column 'Discount__Price' in 'row1', value is 'https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE1MM7t?ver=10c9'. Details: java.lang.NumberFormatException: For input string: "https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE1MM7t?ver=10c9"
Couldn't parse value for column 'Price' in 'row1', value is ' Surface Book e Surface Laptop."'. Details: java.lang.NumberFormatException: For input string: "Surface Book e Surface Laptop.""
Couldn't parse value for column 'Price' in 'row1', value is ' Surface Book e Surface Laptop."'. Details: java.lang.NumberFormatException: For input string: "Surface Book e Surface Laptop.""
Couldn't parse value for column 'Price' in 'row1', value is 'https://www.microsoft.com/it-it/p/organizer-knomad-knomo-mayfair-da-105/8TPWQ7XRPQPS/8BL5'. Details: java.lang.NumberFormatException: For input string: "https://www.microsoft.com/it-it/p/organizer-knomad-knomo-mayfair-da-105/8TPWQ7XRPQPS/8BL5"
Couldn't parse value for column 'Price' in 'row1', value is 'https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE2Gn9i?ver=6c20'. Details: java.lang.NumberFormatException: For input string: "https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE2Gn9i?ver=6c20"
Couldn't parse value for column 'Price' in 'row1', value is '00 cm (wys. x szer. x gł.) Pasuje do modeli Surface Pro i Surface Pro 4."'. Details: java.lang.NumberFormatException: For input string: "00 cm (wys. x szer. x gł.) Pasuje do modeli Surface Pro i Surface Pro 4.""
Couldn't parse value for column 'Price' in 'row1', value is 'Thule'. Details: java.lang.NumberFormatException: For input string: "Thule"
Couldn't parse value for column 'Price' in 'row1', value is ' Surface Book i Surface Laptop."'. Details: java.lang.NumberFormatException: For input string: "Surface Book i Surface Laptop.""
Couldn't parse value for column 'Price' in 'row1', value is ' Surface Pro 4'. Details: java.lang.NumberFormatException: For input string: "Surface Pro 4"
Couldn't parse value for column 'Price' in 'row1', value is '90 x 29'. Details: java.lang.NumberFormatException: For input string: "90 x 29"
Couldn't parse value for column 'Discount__Price' in 'row1', value is 'https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE2Gn9i?ver=6c20'. Details: java.lang.NumberFormatException: For input string: "https://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE2Gn9i?ver=6c20"

Input(Delimited file) and table(mysql) rows are matching. Trying to find rows which has this error.

Re: Dataloading issue from TabDelimited to MYSQL database

Anonymous — Wed, 03 Apr 2019 13:15:55 GMT

It is Float(9,2)

Re: Dataloading issue from TabDelimited to MYSQL database

akumar2301 — Wed, 03 Apr 2019 13:26:09 GMT

when I tried to parse the file with separator \t , it works perfectly.

Attaching my output file.

out.xls

Re: Dataloading issue from TabDelimited to MYSQL database

Anonymous — Wed, 03 Apr 2019 13:38:45 GMT

Thank Abhishek.

I was getting 2 different errors.

Column data being moved to next column due to incorrect column enclosure.

Price column length specified was not enough.

#1 The input is Comma separated, double quotes enclosed file. There is no proper enclosure for few records which has comma within column data and comma as column separator. Due to this some records are getting rejected on initial process.

#2 has been fixed after changing the price column length(Float(9,2)).