Import Flat file then Transpose rows to columns

Question

I have a flat text file with several thousand rows that i want to import into a table then transpose the rows to columns. The data in the rows are related so long as an "ID" matches the "ID" of the previous record until the next ID = 0 or InfoObject = "YOO_CH056" then start all over with a new row. The transposed rows can vary from one or two columns called "nodes" up to a maximum of 7 "nodes" also, Ids 1 and 2 are consistent columns for every row. (see sample of expected resulted for a better understanding).

 

Here is a sample of the Flat Text File:

Function	ID	InfoObject	Node Name	Link ID Parent ID	Child ID	Next ID
Function	1	0HIER_NODE	FUNCTION	0	2	0
Global Functions	2	0HIER_NODE	GF000001	1	3	0
Finance	1331	0HIER_NODE	GF110000	2	1332	1487
Corporate Finance	1333	0HIER_NODE	GF110500	1331	1334	1407
Finance Investment & Other	1372	0HIER_NODE	GF110840	1333	1373	1384
Finance Investment Management	1373	0HIER_NODE	GF110855	1372	1374	1379
Finance Global Real Estate IM	1375	0HIER_NODE	GF110865	1373	1376	1378
AMG/FSD Controllers GRE New	1377	YOO_CH056	GF110875	1375	0	0
Chief Investments Office	1652	0HIER_NODE	GF219000	2	1653	1852
Investments - AMG	1653	0HIER_NODE	GF066400	1652	1654	1783
AIG Investments Other	1753	0HIER_NODE	GF067130	1653	1754	1755
Investments Other	1754	YOO_CH056	GF067150	1753	0	0

 

Here is the expected result:

 

Node0

Node0_Name

Node1

Node1_Name

Node2

Node2_Name

Node3

Node3_Name

Node4

Node4_Name

Node5

Node5_Name

Node6

Node6_Name

Node7

Node7_Name

FUNCTION

Function

GF000001

Global Functions

GF219000

Chief Investments Office

GF066400

Investments - AMG

GF067130

AIG Investments Other

GF067150

Investments Other

FUNCTION

Function

GF000001

Global Functions

GF110000

Finance

GF110500

Corporate Finance

GF110840

Finance Investment & Other

GF110855

Finance Investment Management

GF110865

Finance Global Real Estate IM

GF110875

AMG/FSD Controllers GRE New

 

 Thanks in advance,

I'm wrecking my brain.