Duplicate column names - xlsx

IanM — Tue, 13 Oct 2015 15:24:41 GMT

Hello,

I'm using the excellent tFileExcelSheetInput component but I'm having an issue. It is not with the component itself but with the incoming data.

tFileExcelSheetInput allows me to 'search' for column names across the top row of each sheet, and then return the data under that column heading into my flow (which is great).

My issue is that the incoming spreadsheet can contain duplicate column names. tFileExcelSheetInput appears to be taking the right hand most column with that heading and returning that data. As I said, it isn't really the component I have an issue with it is the duplicate column names.. The duplicated column names have different data below.

So I can have column headings something like:

Name
Tel
Mobile
House Number
Name
Address1
Address2
etc..

The column positions can change from sheet to sheet - hence why I am using tFileExcelSheetInput.
As you can see above, the Column name 'Name' is repeated.
tFileExcelSheetInput will return the second 'Name' data (applies to a house name here and is hardly ever populated) rather than the persons 'Name' data.

I am getting around this by reading the top row, pivoting it, adding a numeric counter (to maintain its position) then doing a tUniqRow to identify duplicates, then renaming the duplicate with a 1,2,3etc re-forming the column, order it on the numeric counter to get the column positions correct, and then unpivot back to a spreadsheet, where I then append the data from the original worksheet.

In this example I end up with column names:

Name
Tel
Mobile
House Number
Name1
Address1
Address2
etc..

This is a very roundabout process.

Does anyone know of a better way of dealing with the duplicate column names?

many thanks

Ian

Re: Duplicate column names - xlsx

Anonymous — Mon, 19 Oct 2015 10:49:29 GMT

Hi Ian
I think you have to identify the uniqueness of column whatever way you use with community version. You can take a look at the dynamic schema feature in Talend Subscription product.

Best regards
Shong

topic Re: Duplicate column names - xlsx in Talend Studio

Duplicate column names - xlsx

Re: Duplicate column names - xlsx