Splitting single input column into multiple columns

[twills12]

Greetings.

Is it possible to split data on the load coming in from excel in a single column (InputField) into multiple columns using the blank rows between questions as a delimiter?

Current...

InputField

1. Question1?       ____ Yes, we use this practice today

____ No, we have a different practice in place

2.  Question2?

____ Yes, they can do this independently in all scenarios

____ Yes, they can do this independently in some scenarios

3. xxxxxx

Desired...

newcolumn1	newcolumn2
Question1 ____ Yes, we use this practice today ____ No, we have a different practice in place	Question2 ____ Yes, they can do this independently in all scenarios ____ Yes, they can do this independently in some scenarios

[steeefan]

You need to figure out the exact content that makes up the empty line. One possible solution is chr(10)&chr(13)&chr(10)&chr(13), i.e. line feed (LF) and carriage return (CR) in combination, twice. Each line break will also have one combination of those, i.e. chr(10)&chr(13), or at least one the characters. An empty line would then be two in succession.

If your data is in inputField, then you could try SubField(inputField, chr(10)&chr(13)&chr(10)&chr(13)).

Without seeing your actual data, these are however only speculations.

[marcus_sommer]

I think splitting it into n columns wouldn't be useful else I would create these 4 Q&A columns:

Q-Nr   Type    Content    Content-Nr
1         Q         Q1            1
1         A          A1            1
1         A          A1            2

and to get the information I would use interrecord-functions, like:

t: load *, rowno() as RowNo,
           if(Type = 'Q', 1, if([Q-Nr] = previous([Q-Nr]) and previous(Type) = 'Q', 1,
              peek('Content-Nr') + 1)) as [Content-Nr];
   load *, recno() as RecNo,
           if(isnum(subfield(InputField, '.', 1)), subfield(InputField, '.', 1), peek('Q-Nr')) as [Q-Nr],
           if(isnum(subfield(InputField, '.', 1)), 'Q', 'A') as [Type],
           replace(InputField, .......) as Content // may require n replace
   from Source where len(trim(InputField));

Depending on the real data-set you may need some adjustments but the general logic should be working.