Combining headers from 2 rows into 1

[Paul_s_Moody]

Hi,

I'm using a workflow to try to read in a large *.csv file which is the ouput from an upstream process generating a set of simulated random numbers.

The difficulty is that the file has headers on the top 2 rows where the 1st row is the part of the organisation the data relates to (so not unique!) and the 2nd row is the investment type.

A (very) simplified example is:

	Group	Group	Group			Company A	Company A			Company B	Company B
	Interest Rates	Credit	Equity			Interest Rates	Credit			Credit	Equity
Iteration315	0.455744847	0.959374447	0.247120185		Iteration389	0.355218072	0.0665424		Iteration2	0.487998998	0.775020332
Iteration71	0.679092875	0.409290041	0.319077558		Iteration978	0.399188855	0.325160899		Iteration7974	0.188592053	0.562055868
Iteration4159	0.445870814	0.67456765	0.764315277		Iteration5	0.332550813	0.829799849		Iteration11578	0.043875038	0.406174081

I'd like to read in the data and transform so that I have a single (and unique) set of headers: Company¦Investment, e.g:

	Group¦Interest Rates	Group¦Credit	Group¦Equity			Company A¦Interest Rates	Company A¦Credit			Company B¦Credit	Company B¦Equity
Iteration315	0.455744847	0.959374447	0.247120185		Iteration389	0.355218072	0.0665424		Iteration2	0.487998998	0.775020332
Iteration71	0.679092875	0.409290041	0.319077558		Iteration978	0.399188855	0.325160899		Iteration7974	0.188592053	0.562055868
Iteration4159	0.445870814	0.67456765	0.764315277		Iteration5	0.332550813	0.829799849		Iteration11578	0.043875038	0.406174081

This operation is trivial in Excel, but as the file of random numbers is ~0.5Gb I'd rather not have to generate a copy of the data!

Hopefully there is a simple solution in Alterxy too...

Once read in, I'd then need to split into each block of data (so in this case 3 seperate tables) as different operations will be carried out on the different parts of the company.

[binuacs · Accepted solution]

@Paul_s_Moody One way of doing this

[Prometheus]

@Paul_s_Moody Here's one way to do it. First, you can configure your Input Data tool so the field names are in your data by unchecking "First Row Contains Field Names." Then you can use the Sample tool to grab the first two records and concatenate the values. For some reason, the Summarize tool wouldn't let me use the pipe "|" as a delimiter, so I just used the Multi-Field Formula tool after Summarize to replace commas with pipes. Then I unioned the rest of the data (make sure the field names are the #1 connection. Then renamed the fields.

 

[Paul_s_Moody]

Thank you!