Row separator is not right

[Anonymous]

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"

[Anonymous]

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!

[Anonymous]

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

null (The system cannot find the file specified)

[Anonymous · Accepted solution]

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

[Anonymous]

after doing that, nothing inserted on the table.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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