Hi everyone,
This marks my first post in these forums! Normally I persevere and work on these problems until I crack them but I feel a little out of my depth on this one and don't really have the time either.
So here is my problem: I have a folder containing text files, 300 of them to be exact. I need to open them one by one and copy some of the data into an excel spreadsheet. These text files are pretty huge, some of them over 100,000 lines so you can't just import them into a spreadsheet as they go over the 65,536 row limit.
Here is a sample of the data:
10.209.32.200, FPHCARE\chani, 4/7/2011, 12:15:18, W3SVC1008243123, HERALD, 10.201.10.26, 218, 6681, 473, 200, 0, GET, /_security/initUser.asp, -,
10.209.32.200, -, 4/7/2011, 12:15:18, W3SVC1008243123, HERALD, 10.201.10.26, 15, 903, 1872, 401, 2148074254, GET, /Application/Drawing/Default.asp, -,
10.209.32.200, FPHCARE\chani, 4/7/2011, 12:15:18, W3SVC1008243123, HERALD, 10.201.10.26, 124, 6690, 591, 302, 0, GET, /Application/Drawing/Default.asp, -,
10.209.32.200, -, 4/7/2011, 12:15:18, W3SVC1008243123, HERALD, 10.201.10.26, 0, 903, 1872, 401, 2148074254, GET, /Application/Drawing/Content.asp, -,
10.209.32.200, FPHCARE\chani, 4/7/2011, 12:15:18, W3SVC1008243123, HERALD, 10.201.10.26, 249, 6690, 7683, 200, 0, GET, /Application/Drawing/Content.asp, -,
10.209.32.200, -, 4/7/2011, 12:15:18, W3SVC1008243123, HERALD, 10.201.10.26, 0, 642, 1872, 401, 2148074254, GET, /_common/fp_hcr.css, -,
10.209.32.200, -, 4/7/2011, 12:15:18, W3SVC1008243123, HERALD, 10.201.10.26, 0, 645, 1872, 401, 2148074254, GET, /_scripts/menu/menu.js, -,
What I need to do is scan each line for the string "drawing/content" (not case sensitive, highlighted in red) and if it matches, scan earlier in the line for a username (highlighted in green). The username will always follow the string "FPHCARE\" and will always be followed by a comma ",". I then need to copy this username into a column in a spreadsheet so that there is a list of them. This list needs to only contain unique names so that if one already exists, it will not be added again to the bottom. Note that some of the lines that contain "drawing/content" will not contain a username, but instead will have a dash "-" (highlighted in orange - if you can see it). These matches should be ignored.
The final requirement is that if a username is scanned but already exists in the column, a count of the number if instances of that username begins in the column next to it. i.e. every time that username is scanned, the number in the cell next to it increases by one. Here is an image to demonstrate what I mean:
excelexample.jpg
My VBA skills are fairly low-level but I am always keen to learn. So if any more experienced users have any good ideas on how to approach this or have any juicy solutions they would like to share, I am all ears (or all eyes, as the case may be)
Thanks,
Phil
Bookmarks