Loading/Transposing data in row format from text file

[sbfernandes]

Greetings

I am trying to load data from a text file. The Headers are in rows while data is in columns.

Below is the data in text file (also attached).

----------------------------FILE START--------------------------------------------------------------------------

SEQ | 1 | 2 | 3 |
Name | John | Steven | Mark |
Family Name | Happy | Glad | Sun |
ID | 111 | 222 | 333 |
| | | |
Gender | Male | Male | Male |
DOB | 020110 | 150211 | 221113 |

SEQ | 7 | 8 | 9 |
Name | mary | Hope | Lisa |
Family Name | Wilkins | Gladys | Sinter |
ID | 119 | 229 | 339 |
| | | |
Gender | FeMale | FeMale | FeMale |
DOB | 020110 | 150211 | 221113 |

SEQ | 10 |
Name | Susan |
Family Name | Marlb |
ID | 449 |
| |
Gender | FeMale |
DOB | 120114 |

--------------------------------------------------------FILE END------------------------------------------------------

Required table should look like

SEQ	Name	Family Name	ID	Gender	DOB
1	John	Happy	111	Male	020110
2	Steven	Glad	222	Male	150211
3	Mark	Sun	333	Male	221113
7	mary	Wilkins	119	FeMale	020117
8	Hope	Gladys	229	FeMale	150218
9	Lisa	Sinter	339	FeMale	221119
10	Susan	Marlb	449	FeMale	120114

[chrismarlow]

Hi,

Here is a start;

data:
LOAD
	If(RowNo()=1,1,If(@1='SEQ',Peek(RC)+1,Peek(RC))) AS RC,
	*;
LOAD @1, 
     @2, 
     @3, 
     @4
FROM
SampleInputFile.txt
(txt, codepage is 28591, no labels, delimiter is '|', msq);

seqmap:
CrossTable (TextField,SEQ,1) 
Load	
	RC,
	@2,
	@3,
	@4
RESIDENT data
WHERE @1='SEQ';


name:
CrossTable (TextField,Name,1) 
Load	
	RC,
	@2,
	@3,
	@4
RESIDENT data
WHERE @1='Name';

Left Join (seqmap)
LOAD
	*
Resident name;

DROP TABLES data, name;

You should be able to make copies & amend the code from 'name:' for each of your fields & you might need to add more to the DROP TABLES statement & possibly tidy up (you get some empty rows where you don't have 3 SEQs in a row).

Cheers,

Chris.

[sbfernandes]

Hi Chrismarlow

Thanks for the suggestion. This would work for the sample file. However my actual data contains around 50 column headers (in rows)  and 10's of thousands of records.