topic Re: Row separator is not right in Talend Studio

Row separator is not right

Anonymous — Tue, 30 Oct 2018 11:39:52 GMT

Hello,

What is the right row separator to be use if the data that I'll be loading is like below:

"123456","ABC","ABC DEFG HIJKL","100 Yellow Street","Saigon","SAI","5000","GON"

I used "\n" but the data became like what I post below, which is the last letter or number inserted on the next column.

"12345","6BC","AABC DEFG HIJK","L100 Yellow Stree","tsaigo","nAI","S500","0GON"

Re: Row separator is not right

Anonymous — Tue, 30 Oct 2018 11:51:34 GMT

Greetings,

Row separator is okay if all the data rows are on new line,

you better specify right column separator.

Regards

Chandra Kant

Re: Row separator is not right

Anonymous — Tue, 30 Oct 2018 11:53:50 GMT

Where should I specify that and what would be the right column separator for that kind of data?

Re: Row separator is not right

Anonymous — Tue, 30 Oct 2018 12:14:26 GMT

Which component you are using as if now ?

What is your input file type ?

If it is csv go for tFileInputDelimited and you will easily find the "Field Separator "

Regards

Chandra Kant

Re: Row separator is not right

Anonymous — Tue, 30 Oct 2018 13:05:49 GMT

The issue appears to be that your positional file pattern ( "1, 6, 3, 35, 35 ...." ) is incorrect. This is a comma separated list of lengths. The pattern shown in your screenshot says: map the first 1 character in a line to the first column, then the next 6 to the second column, followed by the next 3 in the third column and so on.

With that said, the data you show in your post does not match up with the pattern in the screenshot, so there is likely some confusion going on.

Re: Row separator is not right

Anonymous — Tue, 30 Oct 2018 14:02:47 GMT

tfileinputpositional is the component that I am using and I'm trying for both CSV and TXT file.

Re: Row separator is not right

Anonymous — Tue, 30 Oct 2018 14:03:51 GMT

@JGM Indeed and I'm looking on the something weird.

Re: Row separator is not right

Anonymous — Tue, 30 Oct 2018 18:56:59 GMT

Hi,

Could you please advise which component you are using to parse the file? I was able to read the sample data you had provided from the file in right fashion.

Please refer the component details below.

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

Re: Row separator is not right

Anonymous — Tue, 30 Oct 2018 19:01:29 GMT

I'm using a tfileinputpositional not the tfileinputdelimited one.

Re: Row separator is not right

Anonymous — Wed, 31 Oct 2018 04:25:36 GMT

Hi,

The data you have shown in your query is in delimited format. Is there any specific reason to choose the tfileinputpositional instead of tfileinputdelimited for your use case?

Warm Regards,

Nikhil Thampi

Re: Row separator is not right

Anonymous — Wed, 31 Oct 2018 05:32:44 GMT

First this is how the whole parsing scenario:

tfilelist ( contains the source directory and the files) the iterate to tfileinputpositional which I used ((String)globalMap.get("tFileList_3_CURRENT_FILEPATH")) on the file name/stream then row separator I tried is "\n" "\t" and the other valid separator, then tmap to map the values to the tmysqloutput (table) and before that there's a look up table for the values on the column that is not exists on the file.

If I will use the tfiledelimitedinput, can I use the same logic? Should I use tflieexists - iterate-> tfileinputdelimited - tmap - tmysqlouput?

thanks!

Re: Row separator is not right

Anonymous — Wed, 31 Oct 2018 05:52:48 GMT

I have tried to use tfliexist + tfileinoputdelimited + tmap +tmysqlouput but I got an issue:

null (The system cannot find the file specified)

Re: Row separator is not right

Anonymous — Wed, 31 Oct 2018 06:16:14 GMT

Hi,

You can use any type of file (positional,delimited etc) for file list and once we select the right fiel reading component, it will work fine.

Could you please try tfilelist -> tfileinputdelimited -> tmap -> tmysqloutput

Please also add the full job flow and if you are getting error please add the corresponding component screenshot.

Warm Regards,

Nikhil Thampi

Re: Row separator is not right

Anonymous — Wed, 31 Oct 2018 06:32:41 GMT

after doing that, nothing inserted on the table.

Re: Row separator is not right

Anonymous — Wed, 31 Oct 2018 06:38:55 GMT

I don't know, why there's no data inserted, upon checking on the database, there's nothing as well.

Processing .zip, please wait...

Process finished
Data dump to staging data_staging completed.
Data ID: 1
Data: Inserted Rows: 0
Data: Updated Rows: 0
Directory Data: For Deleted Rows: 0
Directory Data: Deleted Rows: 0

Re: Row separator is not right

Anonymous — Wed, 31 Oct 2018 06:50:58 GMT

Hi,

I doubt whether the file is being picked up in your flow. A simple way of debugging is to add tlogrow in each phase to identify where the data is missing in the flow.

If you could put the screenshot of the latest flow, I will get more idea.

Warm Regards,

Nikhil Thampi

Re: Row separator is not right

Anonymous — Wed, 31 Oct 2018 08:22:09 GMT

@nthampi one last question; What if the data is look like:

123456,ABC,ABC DEFG HIJKL,"100 Yellow Street","Saigon",SAI,5000,GON

What should be the right Row and column separator? If you will notice there's a values that has "" and most of them don't have.

Thanks for any response.

Re: Row separator is not right

Anonymous — Wed, 31 Oct 2018 09:33:25 GMT

Hi,

It will still work with tfileinputdellimited with same configuration. I created 2 records as shown below.

And below is the output I got from Talend.

Warm Regards,

Nikhil Thampi

Re: Row separator is not right

Anonymous — Thu, 01 Nov 2018 01:41:35 GMT

@nthampi I didn't noticed that, the second column was not inserted. And I got an error:

Data truncation: Data too long for column 'column2' at row 1

I checked the source data it's having 3 characters the target column having 3 characters as well, so I'm not sure what is happening on why it is not inserted and having error that it is truncated.

Re: Row separator is not right

Anonymous — Thu, 01 Nov 2018 05:11:26 GMT

Hi,

The column naming convention starts with Column0. Since you are mentioning column2, it means you need to actually check the length of third column in the file.

Warm Regards,

Nikhil Thampi