Row separator is not right

Highlighted
Seven Stars

Row separator is not right

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"

image.png

 

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"

 


Accepted Solutions
Employee

Re: Row separator is not right

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


All Replies
Seven Stars

Re: Row separator is not right

Greetings,

 

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

you better specify right column separator.

 

 

Regards

Chandra Kant

Seven Stars

Re: Row separator is not right

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

Seven Stars

Re: Row separator is not right

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

Seven Stars JGM
Seven Stars

Re: Row separator is not right

 

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. 

 

 

 

 

Seven Stars

Re: Row separator is not right

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

Seven Stars

Re: Row separator is not right

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

Employee

Re: Row separator is not right

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.

 

image.png

 

 

Please refer the component details below.

image.png

 

 

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

 

Warm Regards,

 

Nikhil Thampi

Seven Stars

Re: Row separator is not right

I'm using a tfileinputpositional not the tfileinputdelimited one.

 

image.png

Employee

Re: Row separator is not right

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

Seven Stars

Re: Row separator is not right

First this is how the whole parsing scenario:

image.png

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!

Seven Stars

Re: Row separator is not right

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

 

null (The system cannot find the file specified)

Employee

Re: Row separator is not right

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

Seven Stars

Re: Row separator is not right

after doing that, nothing inserted on the table. Smiley Sad

Seven Stars

Re: Row separator is not right

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

Employee

Re: Row separator is not right

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

Seven Stars

Re: Row separator is not right

@nikhilthampi 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. 

Employee

Re: Row separator is not right

Hi,

 

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

image.png

 

 

      And below is the output I got from Talend.

image.png

 

 

Warm Regards,

 

Nikhil Thampi

Seven Stars

Re: Row separator is not right

@nikhilthampi 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.

Employee

Re: Row separator is not right

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

 

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog