Dataloading issue from TabDelimited to MYSQL database
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?
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.
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 🙂
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.
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 🙂
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]
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 🙂
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.
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)).
