Parsing a log file using C# - (Log file is already formatted and is in .xlsx format. It contains license usage data and the objective is to calculate total usage

Question

1.00/5 (1 vote)

See more:

HTML

Hi All,

I am in a fix with this problem and I kind of have figured the parts but I am not able to bring the together. To begin with I have a processed log file data in xlsx format and the structure looks like this :

CSS

STATUS    LICENSE         USERNAME  HOSTNAME    DATE    TIME
OUT   Complte_Package     user8   ABCD1458108   1-Jan   6:30:36
OUT   Custom_Package_1    user5   XYZQ152102    1-Jan   7:00:35
OUT   Complte_Package     user6   LMOD1458561   1-Jan   7:12:25
OUT   Complte_Package     user1   KROL253584    1-Jan   7:23:15
OUT   Custom_Package_2    User7   BCDL255657    1-Jan   8:56:12
IN    Custom_Package_2    User7   FGKL1255657   1-Jan   9:46:38

The given data is from a continuous log file, so when a license is checked out from the server it marks the status as "OUT" and logs the License type,username,hostname,date and time and when it is returned, it records the same info with the status as "IN" - The difference between the OUT time and IN time is the total usage.

I did figure out that I could use microsoft.office.interop.excel and read this file but I am not sure how to read this whole file into a dictionary and then calculate the usage. Can you guys please suggest how to read this into a dictionary? Once its loaded into dictionary with the given columns as keys I would like to calculate the usage as license package/user/month. I have 35000 entries and the log file continuously grows. Any help/tip will be much appreciated

(PS: Very new to c#)

Posted 10-May-15 17:06pm

Member 11679749

Updated 11-May-15 3:01am

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 11-May-15 0:20am

Simply don't use Excel; it makes no sense.
—SA

Member 11679749 11-May-15 9:22am

Hi Sergey,

The file is already processed/formatted as xlsx by the existing the VBA solution (written by someone else).

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2015-05-10T18:04:00

Solution 1

Quote:
log file data in xlsx format

are you sure ? is it actually a text type file with a csv extension instead that you can read into Excel easily ?

If you're very new to c#, why not settle for a 'simpler' method than Interop & Excel ?

a) read the (text) file into a string array, using File.ReadAllLines(, or, if it grows too big, read it line by line
b) For each line (not including the header) use String.Split() with tab or comma (could be tab in your example), to split the string into 'fields'
c) Then think about your dictionary and keys and such

Posted 10-May-15 18:04pm

Garth J Lancaster

Comments

Member 11679749 11-May-15 9:23am

Hello Garth,

Thanks for responding to my question, the log file is already processed(using VBA) and cleaned to look like this:

STATUS LICENSE USERNAME HOSTNAME DATE TIME
OUT Complete_Package user8 ABCD1458108 1-Jan 6:30:36
OUT Custom_Package_1 user5 XYZQ152102 1-Jan 7:00:35

The format is xlsx and not a .csv. So now I have to read this given range into some data structure that will make it easier to search through the range for given license type and calculate usage as user/month.

F-ES Sitecore 11-May-15 9:30am

Excel isn't intended to work in an asp.net environment and it won't work reliably. Use Open XML SDK to read your file instead.