Creating column headers from two fields

Support for DataSlave

Moderators: Tom, ian

Creating column headers from two fields

Postby CharlesBrewer » Fri Mar 16, 2012 11:09 am

I am importing a file in which the fields of the first two records combined form a unique key. For example, the first record might contain the following:

Name / email/ colour of car / colour of car / colour of car / etc....

And the second contains

Blank / blank / red / blue / green / etc

I therefore wish to concatenate the fields in the first two rows, and then declare that the result is the first row (containing the combined value) and that it comprises the headers for the following records. in other words, there will be a new row containing:

Name / email / colour of car red / colour of car blue / colour of car green / etc

Clearly I can transform the incoming file to create the concatenate value, drop the individual values in the first two rows and write out an intermediate text file, but the text file thus created contains F1, F2 etc as the values for the first record which will be taken as column headers. Is there a solution to this which does not involve me messing around with excel or some other external software?
Posts: 1
Joined: Fri Dec 31, 2010 7:31 pm

Re: Creating column headers from two fields

Postby ian » Wed Mar 21, 2012 9:24 am

The solution depends on how dynamic the problem is. If the column headings are not changing then a simple approach is to:

  • Read in the data without headings. The column headings will be Column, Column1, Column2, ….
  • Using a Validate discard the first two rows. If there is no simple way to identify the first two rows then in a Transform first add a sequence column and discard using this.
  • Pass the data into a Transform and then edit the schema to give the appropriate column headings.
If the column headings vary from input file to input file then you will need to use a Map-level script object to combine the data in the first two columns.

I hope this helps. Please let us know if you need more description or a sample Map.
Posts: 364
Joined: Sat Dec 18, 2004 8:13 am
Location: UK

Return to Support

Who is online

Users browsing this forum: No registered users and 3 guests