Is it possible to import a text file where the number of rows per record is dynamic?

[Anonymous]

Can anyone help me figure out how to import the attached file into QlikView? The file is a server log file. The issue I'm having is that each individual record may have a different number of rows. Is there a way to dynamically set the record delimiter using the date stamp in the file. Each new record begins on the line starting with the date stamp.

Any help would be appreciated.

[Oleg_Troyansky]

I'd try to load the text into a temporary table with one long text field and then process the table row by row in a loop and apply custom logic for parsing each line. This way, you can set conditions for certain logic in the currentline, as well as in the previous line(s).

[rwunderlich]

I think the first step is to tag the multiple rows so you can associate them together. You can assemble them into one table row per message like this: (based on the presence of the YYYY-MM-DD)

LOAD

  Concat(RawLine, chr(13)&chr(10), RecId) as Msg

Group by MsgId

;

LOAD

  [@1:n] as RawLine

  ,RecNo() as RecId

  ,if(num(date#(SubField([@1:n],' ',1),'YYYY-MM-DD'))>0

  ,AutoNumber(RecNo(),'MsgId')

  ,peek('MsgId')

  ) as MsgId

FROM

[SampleLogFile.txt]

(fix, codepage is 1252);

-Rob

http://masterssummit.com

http://robwunderlich.com

[Anonymous · Accepted solution]

So I took some time and worked my way through this and came up with the attached solution. It may not be the most elegant piece of work but it does the job and might be helpful to those that might be facing a similar situation.