Importing a Text File with Complex Structure

[ahdrum87]

Hello everyone! This is my first post and it has been a rough few days trying to make this work.

I run reports that tell me when an individual is supposed to be in the office and when they actually log in, but this is a manual process where I have to go through several hundred people each morning and check. I have created a macro that pulls in their time in and out and makes there name appear as Last, First.

I run a seperate report that creates a schedule with headers and other information that is useless in this process. I have attached a dummy file and am looking for anyone who can help get this down to the last, first name and then the times in and such with no headers? I am also trying to keep the total number of people at the bottom in it's own cell.

I am at a loss as the file uses multiple spaces and not ,'s or ;'s etc.

Thanks in advance for all the help!!


FILE:


From: 12/14/11 Data TotalView 12/14/11
To: 12/14/11 Company ABC 13:45
Daily Start/Stop Unit Name RED
Page: 1
Sorted by: Agent Name
Report All Day Exceptions
Report Across Agent Moves: No Report Agent Moves: No
Include Codes:
AUX - SIS CSES Coaching
ECS Training Early Dismissal Extra Hours
Flex Time Off Govrnmnt Obligation Holiday Observed
ISR Production Late Lunch n Learn
Manual Adj Off Manual Adj Worked Meeting
Meeting Military Leave Offsite Meeting
Open Time Override Absence Override Activity
Peer Advisor Priority Meeting Priority Training
Project Project 1 Project 2
Project 3 Real Time Adjustment Same Day Request
Scheduled Absence Scheduled Medical Self Development
Shared Session Study Time/Exam System Outage
TCE Session Team Meeting Technical problems
Tour Trade Off Tour Trade Work Training
Training Training Off Site Training On Site
Unpaid Time Off Unscheduled Absence Unscheduled Medical
Vacation/Time Off Weather
Wednesday, 12/14/11 Supervisor: <Blank>
Agent ID Agent Name Start Stop Exception Code Start Stop
--------- -------------------- ----- ----- ---------------------- ----- -----
2905 Jones, James 23:00 03:30 Scheduled Medical 23:00 03:30
04:00 09:15 Scheduled Medical 04:00 09:15
9099 Stevenson, Michelle 16:30 22:30 Open Time 16:30 17:00
Override Activity 17:00 19:30
Open Time 19:30 20:30
Open Time 20:45 22:30
1078 Doe, Laura 09:00 15:00 Project 2 09:00 12:00
Project 2 12:15 15:00
1906 Recker, Jane 15:45 00:00 Open Time 15:45 18:45
Open Time 19:00 20:45
Open Time 21:15 22:15
Open Time 22:30 00:00
7309 Bishop, Katy 06:45 14:30 Open Time 06:45 08:45
Open Time 09:00 11:00
Open Time 11:30 12:45
Open Time 13:00 14:30
7830 Posh, Doris 06:00 14:30 Open Time 06:00 08:45
Open Time 09:00 10:15
Open Time 10:45 12:15
Open Time 12:30 14:30
7882 Brown, Sally 15:45 00:00 Open Time 15:45 18:45
Open Time 19:00 20:45
Open Time 21:15 22:15
Open Time 22:30 00:00

____________________
From: 12/14/11 Data TotalView 12/14/11
To: 12/14/11 Company ABC 13:45
Daily Start/Stop Unit Name RED
Page: 2
Sorted by: Agent Name
Report Across Agent Moves: No Report Agent Moves: No
Wednesday, 12/14/11 Supervisor: <Blank>
Agent ID Agent Name Start Stop Exception Code Start Stop
--------- -------------------- ----- ----- ---------------------- ----- -----
7725 Campbell, John 06:00 14:30 Open Time 06:00 08:30
Open Time 08:45 10:30
Open Time 11:00 12:30
Open Time 12:45 14:30
7719 Clark, Robert 15:45 00:00 Open Time 15:45 18:30
Open Time 18:45 20:15
Open Time 20:45 22:00
Open Time 22:15 00:00
3243 Cassin, Renee 23:00 09:00 Open Time 23:00 01:00
Open Time 01:15 04:00
Open Time 04:30 07:00
Project 07:15 09:00
1437 Davis, Timothy 15:45 20:45 Open Time 15:45 17:00
Override Activity 17:00 19:30
Open Time 19:45 20:45
1424 Duffy, Brigette 16:30 22:30 Open Time 16:30 17:00
Override Activity 17:00 19:30
Open Time 19:30 20:30
Open Time 20:45 22:30
2282 Evans, James 06:30 10:15 Open Time 06:30 09:00
Open Time 09:15 10:15
9755 Evans, Stephanie 06:30 10:15 Open Time 06:30 09:00
Open Time 09:15 10:15
9101 Fries, Julie 16:30 22:30 Open Time 16:30 20:30
Open Time 20:45 22:30
2080 Dry, Mary 10:15 15:00 Project 2 10:15 12:30
Project 2 12:45 15:00
7729 Granger, Roger 16:30 22:30 Open Time 16:30 17:00
Override Activity 17:00 19:30
Open Time 19:30 20:00
Open Time 20:15 22:30
1992 Harrington, Tim 15:45 20:45 Open Time 15:45 19:00
Open Time 19:15 20:45
5202 Harris, John 15:45 00:00 Open Time 15:45 18:30
Open Time 18:45 20:45
Open Time 21:15 22:15
Open Time 22:30 00:00
355 Hines, Michael 15:45 21:15 Open Time 15:45 19:15
Open Time 19:30 21:15

____________________

Total agents scheduled on 12/14/11 for supervisor <Blank>: 47



CURRENT VBA:

Public Sub Import_TotalView(FName As String, Sep As String)
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Application.ScreenUpdating = False
'On Error GoTo EndMacro:
SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend
Rows("1:4000").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub

 

[ahdrum87]

The text file did not allow me to add it so i copied it. Please note that there are dozens of spaces in front of the times. Thanks folks!

 

[ahdrum87]

Anyone have an idea?