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] - 08 Apr 2008 20:36 GMT
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
> I need code that can parse data from log file.
> The log is pipe delimited and in following format:
[quoted text clipped - 6 lines]
> Purpose is to read from text file and store in sql server database.
> Please help!
rossum - 08 Apr 2008 21:49 GMT
>I need code that can parse data from log file.
>The log is pipe delimited and in following format:
[quoted text clipped - 6 lines]
>Purpose is to read from text file and store in sql server database.
>Please help!
Have a look at String.Split()
rossum
Ignacio Machin ( .NET/ C# MVP ) - 08 Apr 2008 22:18 GMT
> I need code that can parse data from log file.
> The log is pipe delimited and in following format:
[quoted text clipped - 6 lines]
> 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] - 08 Apr 2008 23:58 GMT
>I need code that can parse data from log file.
>The log is pipe delimited and in following format:
[quoted text clipped - 6 lines]
>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 - 09 Apr 2008 07:46 GMT
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 - 09 Apr 2008 15:21 GMT
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 - 09 Apr 2008 15:45 GMT
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/microsoft.public.dotnet.languages.csharp/browse_t
hread/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 - 09 Apr 2008 16:58 GMT
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,