Highlighted
Five Stars

Unable to move data from an excel file to .csv file

Hi Team,

 

I am trying to extract data from a .xlxs file and to load to a .csv file.

but the issue is under one column in the excel sheet, there are multiple lines in a single row separated by space, for which i am not getting proper data in the CSV file.

 

please help me how to merge all the lines of single row into a single line.

 

thanks in advance.

 

Regards,

Nabarun

1 ACCEPTED SOLUTION

Accepted Solutions
Five Stars

Re: Unable to move data from an excel file to .csv file

Hi Shong,

Thanks for response.

But i have resolved the issue.

I just use tMap and replace "\n" with " " and the data are perfect in the csv file.

 

Regards,

Nabarun

4 REPLIES
Community Manager

Re: Unable to move data from an excel file to .csv file

Hello
Can you please show us an example data and what are your expected result?

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

Re: Unable to move data from an excel file to .csv file

Hi Shong,

 

sample excel sheet,

 

Name              Age             gender        address

Nabarun           25              male             Ecity

                                                                marathalli

                                                                bangalore

 

Milan                 23              male              Panpana

                                                                  Balasore

                                                                 Odisha

 

I want the above format excel sheet like below in csv file.

 

Nabarun               25           male               Ecity,Marathalli,Bangalore

Milan                     23          male                Panpana,Balaosre, Odisha

 

there are data in several lines under a single row in the Address column in the excel sheet, these needs to be in a single line as i show above separating each line with a comma.

 

Regards,

Nabarun

Community Manager

Re: Unable to move data from an excel file to .csv file

Hi
i think tDenormalize component can fit your need. Before tDenormalize, fill in the empty field with last non-empty value on tJavaRow, eg:
tFileInputExcel--main--tJavaRow--main--tDenormalize--tFileOutputDelimited.

on tJavaRow: fill in the empty fields with last non-value, take name field for example:

//Code generated according to input schema and output schemai
if(input_row.name==null){
output_row.name=(String)globalMap.get("last_name");
}else{
globalMap.put("last_name",input_row.name);
output_row.name=input_row.name;
}

Hope it helps you!.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

Re: Unable to move data from an excel file to .csv file

Hi Shong,

Thanks for response.

But i have resolved the issue.

I just use tMap and replace "\n" with " " and the data are perfect in the csv file.

 

Regards,

Nabarun