Loading CSV file which have new line with blank columns

One Star

Loading CSV file which have new line with blank columns

Hi,
I have CSV file which has a new line in middle of the column and the rest of the data in that row has gone to next line.
because of which i am facing issue while loading the data into DB.
please see the below screen shot.
I need to remove the spaces after column with data "MANJUN" and append the rest of the data after it.


Thanks,
Tulasi
Moderator

Re: Loading CSV file which have new line with blank columns

Hi,
You screenshot is missing. Please make sure that your file must not be bigger than 2 MB.
Is it OK with you when check out "Trim" option  in tfileinputdelimited to trim the leading and trailing white space?

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Loading CSV file which have new line with blank columns

Hi Sabrina,
I am attaching the screenshot.
Yeah, i have tried it with the Trim Option, but no luck.
Thanks,
Tulasi
One Star

Re: Loading CSV file which have new line with blank columns

 
Screen shot uploaded
One Star

Re: Loading CSV file which have new line with blank columns

screenshot for reference.
One Star

Re: Loading CSV file which have new line with blank columns

i am unable to upload the screen shot.
Can you please me without the screen shot
Five Stars

Re: Loading CSV file which have new line with blank columns

select check csv options in advance tab of tFileInputDelimited. if your file has string data enclosed with double quotes.
One Star

Re: Loading CSV file which have new line with blank columns

Hi Umesh,
There are no string data in my csv file enclosed in double quotes.
There is a complete blank line after the first two columns and the next line contains the data.
Thanks,
Tulasi
Five Stars

Re: Loading CSV file which have new line with blank columns

Can you show us, source row or paste the raw row here?
One Star

Re: Loading CSV file which have new line with blank columns

Thanks,
Tulasi
Five Stars

Re: Loading CSV file which have new line with blank columns

please open file in notepad then locate the row which causing the problem. 
One Star

Re: Loading CSV file which have new line with blank columns

I know the line numbers of the row which are causing the problem.
Five Stars

Re: Loading CSV file which have new line with blank columns

then would you like to share that line with us?
One Star

Re: Loading CSV file which have new line with blank columns

Thanks,
Tulasi
One Star

Re: Loading CSV file which have new line with blank columns

Can you please provide a solution since it is urgent
Five Stars

Re: Loading CSV file which have new line with blank columns

design job as below, 
tFileInputFullRow----tjavarow---textractDelmitedField---youroutput. 
in tJavaRow add following code to replace multiple white spaces with one space. 
yourcolumn.replaceAll("+", " ");

Note: please make copy of source file before doing test. 
One Star

Re: Loading CSV file which have new line with blank columns

Hi Umesh,
I test above code with sample data.It's not removing new line in string.Pls see attached pics.
1st image have sample data.
2nd image talend generated output.

 
Regards,
kumar.talend
One Star

Re: Loading CSV file which have new line with blank columns

Hi Tulasi,
Use below code in tamp:
row1.Customer_name.replaceAll("\\r\\n|\\r|\\n", " ") (Replace row1.Customer_name with your  column)..See attached screen


Regards,
kumar.talend
Five Stars

Re: Loading CSV file which have new line with blank columns

Hi Kumar.Talend, 
Thank you for testing it, but you tested it with wrong data. 
As I asked Tulsi earlier, if he show us raw data of file then we can suggest, the images he shared previously showing from excel open view. 
he has to open same file in notepad or notepad++ and then locate the problematic row. then take snap and share with us. 

Either your file row delimiter is different? 
Column has long spaces. 
may be Filed delimiter is different? 
or anything else. 
please share the actual data not the created one, last solution provided to replace multiple white spaces with single one. 
One Star

Re: Loading CSV file which have new line with blank columns

Hi Umesh,
I have attached the screen shot of the file. i am attaching i once again.
The screen shot i have attached is the one form the notepad++ only.
The delimited file is tab separated.
Thanks,
Tulasi
Five Stars

Re: Loading CSV file which have new line with blank columns

Not able to see the attached. please upload it again. or you can attached the same line(including split) as file to the post.
Moderator

Re: Loading CSV file which have new line with blank columns

Hi Tulasi,

Did you get any login issue? We saw that you posted your issue as "Guest".

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Loading CSV file which have new line with blank columns

Hi Umesh,
Sorry for the inconvenience,Please find attached the screen shot.
Five Stars

Re: Loading CSV file which have new line with blank columns

Hi Tulsi, 
By looking at your file i am unable to find what is the problem, but I would like to suggest that use below code to replace multiple Tab spaces, e. g. if your line contains more than 1 tabs then replace with {2} if it contains more than that then use multiple replace statements on same line. check for below solution. 
String foo = "bar	1		2			3";
System.out.println(foo.replaceAll("\\t{2}"," ").replaceAll("\\t{2}"," "));

or you can use below statement regardless of Tab, space, empty value & new line as well. 
System.out.println(foo.replaceAll("\\s+","	"));

hope this will work for you..
One Star

Re: Loading CSV file which have new line with blank columns

Hi Umesh,
Where should i use this code.
Currently i am using it in tjavaRow.
The actual problem is the row that i have showed here is a single line and due to the tabs spaces the row got split into 2 line and the number field is taking the string field in the second row and the issue For Input String : "MANJUNATH" is displayed. as shown in the screen shot.
Five Stars

Re: Loading CSV file which have new line with blank columns

try below flow 
tFileInputFullRow--tJavaRow--tMap--tExtractDelimitedField---yourOutput. 
you will get "line" column in tFileInputFullRow. 
use mention code in tJavaRow 
output_row.line=input_row.line.replaceAll("\\s+","	");

rest of the things need to modify as per requirement.