Break up a single column on the load from a excel file

[twills12]

I have an excel file I'm loading that contains a single column of data.  I want to break it up into multiple rows.

Here's the sample data first 9 rows.  It is coming in loaded in column A of the excel file...

1. New market research

This is the section that will go into detail on critical market research that is new to the team

and will be documented for further exploration.

2. Capability research

An in depth look at the capabilities required for the team and what is effective  through early

approaches.

3. Comparable field intelligence

A new section that does yada yada yada.  And when it isn't doing that, it probably should be

doing that. 

I would like to have it split into 2 columns on the load script...

section          content

1                       This is the section that will go into detail on critical market research that is new to the team...

2                       An in depth look at the capabilities required for the team and what is effective  through early...

3                       A new section that does yada yada yada.  And when it isn't doing that, it probably should be...

Any suggestions would be greatly appreciated.

[crusader_]

Hi,

Check below example (and attached file):

Input:
LOAD
	IF(ISNUM(left(A,1)), left(A,1), 
		IF(ISNUM(Left(Peek(A),1)), Left(Peek(A),1), 				//Reading number from row - 1
			IF(ISNUM(Left(Peek(A,-2),1)),Left(Peek(A,-2),1),		//Reading number from row - 2
				Left(Peek(A,-3),1))									//Reading number from row - 3
			)														//Repeat condition until you get right result (depends on your data)
		) AS ID,
	IF(ISNUM(LEFT(A,1)), REPLACE(LEFT(A,3),'.','|'), A) AS A;
	
LOAD * INLINE [
A
"1. New market research"
"This is the section that will go into detail on critical market research that is new to the team"
"and will be documented for further exploration."
"2. Capability research"
"An in depth look at the capabilities required for the team and what is effective  through early"
"approaches."
"3. Comparable field intelligence"
"A new section that does yada yada yada.  And when it isn't doing that, it probably should be"
"doing that."
];

Output:
LOAD SUBFIELD(FullText,'| ',1) AS section,
	SUBFIELD(FullText,'| ',2) AS content
;
LOAD
	CONCAT(A) AS FullText
RESIDENT Input
GROUP BY ID;

It's not a super graceful solution, however works fine with your example...

Hope this helps

//Andrei