Need help to load flat files

[#1]

Hi

I am getting data in flat file in the attached format so that data will come either in these two formats

Wed Jul 15 10:56:36 EDT 2015

Usage – 100%

and

Wed Jul 15 11:06:15 EDT 2015

The system ABCD down for 5 minutes

ABCD Usage  – 99.99%

in the load script i want to load the timestamp , System name , duaration and usage % as following

Timestamp	System	Duration	Usage %
Jul 15 11:06:15 EDT 2015	ABCD	5	99.99
Jul 15 10:56:36 EDT 2015	ABCD	0	100

[Ralf-Narfeldt]

In the first message, ABCD is not included. Is this to be assumed for all entries, or do you have other possible System?

[#3]

It should be assumed please refer the expected data xls which i have attached just now

[Ralf-Narfeldt]

First step is to load the raw data as fix files as that can manage data that spans several lines easily. They will concatenate into one table.

Each record will contain one log item of several lines, including CR characters (Chr(13)).

You will of course need to adjust the path:

Directory;

Logs:

LOAD [@1:n] As Log

FROM

[..\Downloads\Sample1.txt]

(fix, codepage is 1252, record is 3 lines);

LOAD [@1:n] As Log

FROM

[..\Downloads\Sample2.txt]

(fix, codepage is 1252, record is 3 lines);

Then you need to parse the data into a new table.

Parsed:

Load

Mid(Log, 5, Index(Log, Chr(13)) - 5) As Timestamp,

'ABCD' As System,

PurgeChar(Subfield(Log, 'Usage', 2), '% –') As Usage%,

If(Index(Log,'minutes'),Subfield(SubField(Log, 'used for ', 2), ' minutes', 1), 0) As Duration

resident Logs;

finally, drop the raw table

drop table Logs;

[#5]

thanks i checked but it is not giving the timestamop

[Ralf-Narfeldt]

This is the result I get when loading the script using the sample files you provided.

Did you try with the same sample files?

I did notice that there is a discrepancy between the sample written in your question, and the actual sample file you uploaded.

In the question it says "The system ABCD down for 5 minutes" while the sample file says "The system ABCD used for 5 minutes"

[#7]

Yes you are right thanks

[qlikviewwizard]

Hi binodkumar_22

If you get the solution, please close the thread. Thank you.

[Ralf-Narfeldt]

Great! Please mark as Correct if you are satisfied with the answer!