topic Re: "Data truncation: Data too long for column" causes in Talend Studio

"Data truncation: Data too long for column" causes

Anonymous — Tue, 20 Mar 2018 08:44:07 GMT

I created a MySql table using talend. And with tfileInputDelimited palette, i tried to copy the data present in csv filoe to the table in data base.

While runnung the job, am getting Data truncation: Data too long for column 'First' at row 1

Please help me in resolving the issue. I am still at learning phase.

Please find the screenshots attached for more details.

Thank u.

Re: "Data truncation: Data too long for column" causes

TRF — Tue, 20 Mar 2018 08:52:25 GMT

Hi,

Is your CSV file aligned on the tFileInputDelimited configuration (fields included between "" and separated by ?

Else 1 rows is considered as 1 column and the length may be greater than the DB column definition.

Re: "Data truncation: Data too long for column" causes

Anonymous — Tue, 20 Mar 2018 09:02:10 GMT

path of the csv file in my pc: "D:/Talend/TOS_DI-20170623_1246-V6.4.1/workspace/Custs.csv"

I am not getting what you said. Can you please explain it clearly?

Screenshot (44).png

Re: "Data truncation: Data too long for column" causes

TRF — Tue, 20 Mar 2018 09:04:34 GMT

Share a capture of the file content from notepad or notepad++ (not from Excel)

Re: "Data truncation: Data too long for column" causes

Anonymous — Tue, 20 Mar 2018 09:13:07 GMT

please find the attachment in notepad.

Thank u

Screenshot (45).png

Re: "Data truncation: Data too long for column" causes

TRF — Tue, 20 Mar 2018 09:21:41 GMT

Based on your tFileInputDelimited, fields are expected to be enclosed between "" and separated by ;

In your file, there is no "" around fields and separator seems to be a Tab (not a .

When your file is read by your job, it's like there is only 1 field per record.

I suggest you to tick the option "Check each row structure against schema" for tFileInputDelimited Advanced Settings.

Re: "Data truncation: Data too long for column" causes

Anonymous — Tue, 20 Mar 2018 09:28:35 GMT

Its csv file only. Not a tab separated file. For your understanding, i separated with tab.

Actually there is no error while running the job. But data is not copied from csv file to DataBase.

I also tried with field separator ";". But its not working.

Re: "Data truncation: Data too long for column" causes

TRF — Tue, 20 Mar 2018 09:39:50 GMT

The file MUST look like this (once again, based on your tFileInputDelimited configuration):

First;Last;Number;City;State
"vams1";"krishna";"11";"cittoor";"ap"
"amit";"sharea";"22;"bangalore";"ka"

Re: "Data truncation: Data too long for column" causes

ThWabi — Tue, 20 Mar 2018 09:46:48 GMT

Hello vamsi1453,

could you share the schema definition of your input file component and the database output component?

Best regards,

Thomas

Re: "Data truncation: Data too long for column" causes

Anonymous — Tue, 20 Mar 2018 10:08:30 GMT

Excel CSV usually take the data as you mentioned.

I think there is no wrong with my CSV as its working fine when i am trying to copy the content of file to another file like tfileinputdelimited to tfileoutputdelimited. Problem arising in case of database only.

Re: "Data truncation: Data too long for column" causes

Anonymous — Tue, 20 Mar 2018 10:09:16 GMT

Please find the attachment for schema.

Screenshot (46).png
Screenshot (47).png

Re: "Data truncation: Data too long for column" causes

TRF — Tue, 20 Mar 2018 10:14:18 GMT

Redirect the output to a tLogRow component with "Table" option ticked and share the result printed on the console

Re: "Data truncation: Data too long for column" causes

hami1 — Tue, 20 Mar 2018 10:25:40 GMT

Hello,

Please provide the Table Db structure. In your case I guess you didn't mention the size of the columns. Verify thoroughly the length of your table columns and try to process single record first and let us know. You can even keep the tLogRow Component in between and execute for knowing the execution process.

Thanks,

Hameed

Re: "Data truncation: Data too long for column" causes

Anonymous — Tue, 20 Mar 2018 12:13:14 GMT

when i used the tlogrow the below output came:

[statistics] disconnected
Job CreateTable ended at 17:41 20/03/2018. [exit code=0]

Re: "Data truncation: Data too long for column" causes

Anonymous — Tue, 20 Mar 2018 12:15:31 GMT

I tried in both ways. I tried with mentioning column size and also without size.

Re: "Data truncation: Data too long for column" causes

Anonymous — Tue, 20 Mar 2018 12:47:41 GMT

Thank u TRF

By using tlogrow, i understood the problem. That is, first column is taking the entire data of remaining 4 columns. But am unable to solve the issue.

Help me

Re: "Data truncation: Data too long for column" causes

Anonymous — Tue, 20 Mar 2018 12:59:41 GMT

when i put "," in field separator, i got the output as:

[statistics] disconnected
Job CreateTable ended at 18:28 20/03/2018. [exit code=0]

but failed to insert the data in database.

Re: "Data truncation: Data too long for column" causes

hami1 — Tue, 20 Mar 2018 13:06:05 GMT

Check the Create Table Component. You might have missed mentioning the column sizes.

Try to run the Job in debug mode

Thanks,

Hameed

Re: "Data truncation: Data too long for column" causes

TRF — Tue, 20 Mar 2018 13:08:19 GMT

OK, now your job seems ok but you probably have a problem with the table definition.

Check this part.

Re: "Data truncation: Data too long for column" causes

Anonymous — Tue, 20 Mar 2018 13:14:42 GMT

problem found in table definition

thanks hammed