"Data truncation: Data too long for column" causes

Four Stars

"Data truncation: Data too long for column" causes

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.


Accepted Solutions
Six Stars skh
Six Stars

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

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

Try to run the Job in debug mode

Thanks,

Hameed

Forteen Stars TRF
Forteen Stars

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

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

Check this part.


TRF

All Replies
Forteen Stars TRF
Forteen Stars

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

Hi,

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

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


TRF
Four Stars

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

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?

Forteen Stars TRF
Forteen Stars

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

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


TRF
Four Stars

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

please find the attachment in notepad.

Thank u

Forteen Stars TRF
Forteen Stars

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

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 Smiley Wink.

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.


TRF
Four Stars

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

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.

Forteen Stars TRF
Forteen Stars

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

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"

TRF
Seven Stars

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

Hello vamsi1453,

 

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

 

Best regards,

 

Thomas

 

Four Stars

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

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.

Four Stars

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

Please find the attachment for schema.

Forteen Stars TRF
Forteen Stars

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

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


TRF
Six Stars skh
Six Stars

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

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

Four Stars

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

when i used the tlogrow the below output came:

[statistics] connecting to socket on port 3920
[statistics] connected
Data truncation: Data too long for column 'First' at row 1
.-----------------------------+----+------+----+-----.
| tLogRow_1 |
|=----------------------------+----+------+----+----=|
|First |Last|Number|City|State|
|=----------------------------+----+------+----+----=|
|vamsi,krishna,11,cittoor,ap |null|null |null|null |
|amit,sharma,22,bangalore,ka |null|null |null|null |
|sachin,tendulkar,33,mumbai,mh|null|null |null|null |
'-----------------------------+----+------+----+-----'

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

Four Stars

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

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

Four Stars

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

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

Four Stars

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

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

 

[statistics] connecting to socket on port 3849
[statistics] connected
Data truncation: Data too long for column 'First' at row 1
.------+---------+------+---------+-----.
| tLogRow_1 |
|=-----+---------+------+---------+----=|
|First |Last |Number|City |State|
|=-----+---------+------+---------+----=|
|vamsi |krishna |11 |cittoor |ap |
|amit |sharma |22 |bangalore|ka |
|sachin|tendulkar|33 |mumbai |mh |
'------+---------+------+---------+-----'

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

 

but failed to insert the data in database.

Six Stars skh
Six Stars

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

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

Try to run the Job in debug mode

Thanks,

Hameed

Forteen Stars TRF
Forteen Stars

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

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

Check this part.


TRF
Four Stars

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

problem found in table definition

thanks hammed

Four Stars

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

problem found in table definition

thanks TRF and all who helped me

But want to know the reason behind this error occurrence? 

Forteen Stars TRF
Forteen Stars

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

Great!

We can't tell you th reason as we are not in the process you follow to create the table.

Probably one or many columns are smaller than the data you're trying to inject.


TRF
Four Stars

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

Thanks buddy