Parsing Mulitple CSV files from Hell?? 1

[Beeps]

Hi,

I was wondering what would be the best vehicle to read through a series of .csv files of varying sizes? I would like to be able to sort through the entries. Here's an example of one of the files:


Date: 02/12/2004 Time: 01:21:22 System Name: D134P1
Name: 0099A52PDI03
Operator: SYSTEM
Action: Alarm received from Node 41. Value: 0.0058, Status: -N-, Cmd Pri: NONE
********************************************************************************
Date: 02/12/2004 Time: 01:25:03 System Name: D134P1
Name: 0099A52PDI03
Operator: SYSTEM
Action: Alarm received from Node 41. Value: -0.0055, Status: *A4*, Cmd Pri: NONE
********************************************************************************
Date: 02/12/2004 Time: 01:26:58 System Name: DA51T1
Name: DA51T1
Operator: SYSTEM
Action: Alarm received from Node 38. Value: 50.03, Status: -N-, Cmd Pri: NONE
********************************************************************************
Date: 02/12/2004 Time: 01:26:59 System Name: DA51T1
Name: DA51T1
Operator: SYSTEM
Action: Alarm received from Node 38. Value: 50.00, Status: *A4*, Cmd Pri: NONE
********************************************************************************

etc/
Name of sample file : "System Activity Daily Alarm_02-13-04_02-20.csv"
I'm familiar with using ADO, but not with text. I'd like to be able to search through the records for different Status and date/time values and then paste into Excel.
I was thinking of using some kind of recordset function, but don't know what would be the most efficient to perform this task. Maybe an FSO object?

Any guidance to get me started would be greatly appriciated.

Thanks,

BP

 

[vb5prgrmr]

Hmmm... Something like...
[tt]
Public Sub MyCSVParsingSub(FName As String)

On Error GoTo MyCSVParsingSubError

Dim FNumb As Integer, Temp As String, MyDate As String, MyStatus As String
Dim Pos1 As Integer, Pos2 As Integer

'check to make sure the file exists and that they did give us a file to work with
If Dir(FName) = "" Then
MsgBox "File Not Found"
Exit Sub
ElseIf Trim(FName) = "" Then
MsgBox "Error: MyCSVParsingSub - No File Passed", vbOKOnly + vbInformation, "Please Try Again"
Exit Sub
End If

'get an available file number
FNumb = FreeFile

'open file for input and run till the end
Open FName For Input As #FNumb
Do While Not EOF(FNumb)

'Input each line
Line Input #FNumb, Temp

'since we are only looking for 2 item on different lines
If UCase(Left(Temp, 4)) = UCase("date&quot Then

'get just the date
Temp = Trim(Mid(Temp, 5))
MyDate = Left(Temp, InStr(1, Temp, " &quot)

ElseIf UCase(Left(Temp, Len("action&quot)) = UCase("Action&quot Then

Pos1 = InStr(1, Temp, "Status&quot
Pos2 = InStr(Pos1, Temp, ",&quot
MyStatus = Mid(Temp, Pos1 + Len("Status:&quot, Pos2 - (Pos1 + Len("Status&quot))
Call UpdateExcelSub(MyDate, MyStatus)'this is for you to build

End If

Loop

Close #FNumb

Exit Sub
MyCSVParsingSubError:

MsgBox Err.Description

End Sub
[/tt]

That is an example that will get you started on opening up a file and reading it in and parsing it out.

(BTW: This is not a CSV file)

So in summary you have the following to look up in help...

Open Statement
Line Input
FreeFile Function
Left
Mid
Right
InStr
Close

Good Luck

 

[Beeps]

vb5prgrmr

You are the man!! Thanks for the tip. I think that this is a great place to start. I need to brush up on my file access chops since I haven't had to use it in a while.

I'm working on incorporating a ADO recordset to capture the data and am having a little trouble. I'll repost as I work through this issue.

 

[Beeps]

Oh yeah,

I know that the files are not a .csv, but that's how they are saved coming from the "propritary" database. I also have found out that there are slight deviations from this format. Loops seems to work through this though, but I need to test it further.