Parsing log file using C#

[Prem Parekh]

I need code that can parse data from log file.
The log is pipe delimited and in following format:
time=2006-11-03 13:13:56| fw=199.23.48.120| user=tjolen| realm=EMEA| roles=Network Connect| src=24.141.197.139| dstname=| duration=| msg=AUT22670: Login succeeded for tjolen/EMEA.

time=2006-11-03 13:14:07| fw=199.23.48.120| user=tjolen| realm=EMEA| roles=Network Connect| src=63.131.197.139| dstname=TUN-VPN| duration=| msg=JAV20021: Connected to EPN port 443

time=2006-11-03 13:16:10| fw=199.23.38.120| user=tjolen| realm=EMEA| roles=Network Connect| src=34.131.127.139| dstname=| duration=| msg=AUT22673: Logout from 24.131.127.139

Purpose is to read from text file and store in sql server database.
Please help!

 

[Peter Bromberg [C# MVP]]

There are several good open-source implementations of CSVReader that you can
use for this. Just google "C#" CsvReader.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short Urls & more: http://ittyurl.net

 

[Ignacio Machin ( .NET/ C# MVP )]

I need code that can parse data from log file.
The log is pipe delimited and in following format:
time=2006-11-03 13:13:56| fw=199.23.48.120| user=tjolen| realm=EMEA| roles=Network Connect| src=24.141.197.139| dstname=| duration=| msg=AUT22670: Login succeeded for tjolen/EMEA.      

time=2006-11-03 13:14:07| fw=199.23.48.120| user=tjolen| realm=EMEA| roles=Network Connect| src=63.131.197.139| dstname=TUN-VPN| duration=| msg=JAV20021: Connected to EPN port 443      

time=2006-11-03 13:16:10| fw=199.23.38.120| user=tjolen| realm=EMEA| roles=Network Connect| src=34.131.127.139| dstname=| duration=| msg=AUT22673: Logout from 24.131.127.139  

Purpose is to read from text file and store in sql server database.
Please help!

Hi,

You can split the line using String.Split( new char[] {'|'}); and then
later split each piece by the '=' then you have a pair of key,value.

 

[Gilles Kohl [MVP]]

I need code that can parse data from log file.
The log is pipe delimited and in following format:
time=2006-11-03 13:13:56| fw=199.23.48.120| user=tjolen| realm=EMEA| roles=Network Connect| src=24.141.197.139| dstname=| duration=| msg=AUT22670: Login succeeded for tjolen/EMEA.

time=2006-11-03 13:14:07| fw=199.23.48.120| user=tjolen| realm=EMEA| roles=Network Connect| src=63.131.197.139| dstname=TUN-VPN| duration=| msg=JAV20021: Connected to EPN port 443

time=2006-11-03 13:16:10| fw=199.23.38.120| user=tjolen| realm=EMEA| roles=Network Connect| src=34.131.127.139| dstname=| duration=| msg=AUT22673: Logout from 24.131.127.139

Purpose is to read from text file and store in sql server database.
Please help!

Assuming the (path and) name of your log file is stored in "fileName", here's a one-liner that prints the required INSERT statements to the console ;-)

Console.WriteLine(File.ReadAllLines(fileName).Select(l =>l.Split('|').Aggregate(new {a="",v="" },(cur,seg)=>{string[]p=seg.Split('=');return new{a=cur.a+((cur.a== "")?"":",")+p[0],v=cur.v+((cur.v=="")?"":", ")+"'"+p[1].Replace("'","''") + "'"};},avl=>String.Format("INSERT INTO LOGRECORDS ({0})
VALUES ({1});",avl.a,avl.v))).Aggregate((c, e)=>c+"\n"+e));

Regards,
Gilles.

P.S.: Nah, I'm NOT being serious about this.

 

[Vasanthakumar D]

Hi,
try this....

string str = System.IO.File.ReadAllText("PathName");

now str hold the content of the file... you can get substring and split the values based on your criteria and use it...

 

[Prem Parekh]

Vasanth,

Thanks for your reply. I've done similar to what you suggested. Now how I can I put this parsed text into a table by removing column names (time, fw,etc) that are repeating in everyline and write it to Sql Server.
Should I use Dataset?
Please advise.

My code:
using (StreamReader sr = new StreamReader("c:\\textfiles\\modified.txt"))
{
String str;
// Read and display lines from the file until the end of
// the file is reached.
while ((str = sr.ReadLine()) != null)
{
string[] arInfo = new string[4];

// define which character is seperating fields
char[] splitter = { '|' };

arInfo = str.Split(splitter);

for (int x = 0; x < arInfo.Length; x++)
{
Console.Write(arInfo[x] + "<br>");
}

}
}

 

[Marc Gravell]

It depends on the length of the file.

The fastest way to get it into a SQL Server is using SqlBulkCopy -
WriteToServer.
If it is reasonable to hold it in memory, then DataTable is a
quick'n'dirty answer. If the file is very large, you will want to
stream, by impersonating an IDataReader. You could steal
SimpleDataReader from here:
http://groups.google.com/group/micr...read/thread/84d08dd9778efd77/91c7a20056ffe8e1

and the rest of your code replaces XmlDataReader (from the same post);
in particular, your DoRead override would read the next line [return
false if EOF], call SetValues(arInfo) and return true.

Marc

 

[Marc Gravell]

For info - I've just (for fun) written a LINQ-based implementation of
this that makes it easy to chain an object stream to an IDataReader.
Let me know if you'd find it useful,