topic Re: Converting Columns to Rows in Excel in Talend Studio

Converting Columns to Rows in Excel

Anonymous — Sat, 16 Nov 2024 12:24:40 GMT

I have an excel file with data in following format

Region Name Region Type Data Type Oct 2008 Nov 2008 Dec 2008
Abbeville Metro metro All Homes $137,000 $129,500 $149,500
Abbeville city All Homes $145,000 $125,000 $149,000
Erath city All Homes $124,500
Kaplan city All Homes
Maurice city All Homes $224,000 $224,000 $224,000
I am trying to produce an output file which would the data in the following format

Region Name Region Type Data Type MonthYear Price
Abbeville Metro metro All Homes Oct 2008 $137,000
Abbeville Metro metro All Homes Nov 2008 $129,500
Abbeville Metro metro All Homes Dec 2008 $149,500
Abbeville city All Homes Oct 2008 $145,000
Abbeville city All Homes Nov 2008 $125,000
Abbeville city All Homes Dec 2008 $149,000
Erath city All Homes Oct 2008 $124,000
Erath city All Homes Nov 2008
Erath city All Homes Dec 2008
Kaplan city All Homes Oct 2008
Kaplan city All Homes Nov 2008
Kaplan city All Homes Dec 2008
Maurice city All Homes Oct 2008 $224,000
Maurice city All Homes Nov 2008 $224,000
Maurice city All Homes Dec 2008 $224,000
I tried using tMap but just couldnt get it done. What is the best approach to get this done?

Re: Converting Columns to Rows in Excel

Anonymous — Thu, 02 Feb 2012 02:47:03 GMT

Hi
Try to download tUnPivotRow at Exchange.
tUnPivot will change columns into rows.
Deploy a new component
Specify a ?User components folder? in TOS preferences (preferences/Talend/Components)
Copy your newly downloaded component folder in the user components folder
After launch, you should see your component in folder ?components/user? in org.talend.designer.components.localprovider plugin
your component should also appear in the Palette in the job designer (in a family folder if relevant)
Regards,
Pedro

Re: Converting Columns to Rows in Excel

Anonymous — Thu, 02 Feb 2012 03:29:57 GMT

Looks like this would work. I downloaded the component and did as per suggestion.
Per one of the reference images on talend forge site, could you please tell me how i can add the input rows in Basic Settings of the tUnpivotRow component.
Thanks a lot for all your help.

Re: Converting Columns to Rows in Excel

Anonymous — Thu, 02 Feb 2012 03:37:48 GMT

Hi
It is related to this topic https://community.talend.com/t5/Design-and-Development/How-to-create-two-or-more-sequences-from-a-row-in-Excel-file/td-p/110604.
Because tUnPivotRow is a custom component, it may encounter code generation error in WIN7.
I have tried to update it with specified TOS version, which is still not compatible for all versions.
Regards,
Pedro

Re: Converting Columns to Rows in Excel

Anonymous — Thu, 02 Feb 2012 06:40:38 GMT

Thank You. I could clear stage - 1 with your help. Thanks a lot pedro

Re: Converting Columns to Rows in Excel

Anonymous — Fri, 05 Oct 2012 17:09:35 GMT

Hi Pedro/Talend Team,
You helped me a great deal last time around with this query. However i have run into a strange problem this time around and i hope you can bail me out of this problem.
1. I have attached one excel and one csv file. Excel is the input file that is used in the talend job. I have attached it so that you can take a look at the data that needs to be pivoted.
2. The output.csv file is the what i need to produce from the talend job.
As you can see the source excel file has columns with month and year as headers. The main problem here is i have a folder full of thousands of such files and all the files do not have the same starting point or in other words
not all the excel files have data starting from October 2008. Some might start from feb 2010 or some might start from any month and year. But i need to produce an output which looks like this
Region Name Region Type Data Type Pivot Key Pivot Value
Algonac City All Homes Oct 2008 $139,900
Algonac City All Homes Nov 2008 $139,900
Algonac City All Homes Dec 2008 $139,900
Algonac City All Homes Jan 2009 $125,900
Algonac City All Homes Feb 2009 $115,000
'
'
'
'
'
Algonac City Single Fam Oct 2008 $115,000
and so on. Basically it should unpivot the headers as well so that i dont have to hard code the month and year value in excel input schema.
I have attached screenshots of my talend job and their respective schemas. I am trying hard to find another post or any reference material to do this but i havent had any success yet.
Please help me on this as this is the most critical part of the overall business process of my company. I would really appreciate it.
Thanks
Devesh

Re: Converting Columns to Rows in Excel

Anonymous — Fri, 05 Oct 2012 17:11:41 GMT

Seems like this wont allow me to attach an excel file.

Re: Converting Columns to Rows in Excel

alevy — Sat, 06 Oct 2012 09:24:03 GMT

I think you would have to do this yourself using reflection to concatenate the pivot key with the pivot value i.e.:
tFileList -iterate-> tFileInputExcel_1 (no header) --> tJavaFlex --> tFilterRow --> tNormalize Data on "~" --> tExtractDelimitedFields Data on "#" --> tFileOutputDelimited.
tFileInputExcel would have to have enough columns to cover as many months as there could possibly be in one file. tFilterRow just removes the header row (tos_count_tFileInputExcel_1!=1). In tJavaFlex with schema RegionName|RegionType|DataType|Data, your code would be like:

============= Start code =============
java.lang.reflect.Field[] input_fields = row1.getClass().getDeclaredFields();
Object[] months = new Object;
============= Main code =============
int position = -1;
row2.Data = "";
for( java.lang.reflect.Field field : input_fields ) {
	if (++position<5) {
		continue;
	}
	field.setAccessible(true);
	Object fieldvalue = field.get(row1);
	if (tos_count_tFileInputExcel_1==1) {
		months = fieldvalue;
	} else if (months!=null) {
		row2.Data += (position==5?"":"~") + months + "#" + (fieldvalue==null?"":fieldvalue);
	}
}