Parsing a text file into Excel or Access format

[JimmyL]

I have an NT server report which logs all dates when files are created.
This file is modified every night. It produces output in the following
format:-

Directory of E:\DATA\ITINFO\DIR02

08/20/00 09:38a <DIR> .
08/20/00 09:38a <DIR> ..
08/25/00 09:57a 53,760 Guide.xls
08/25/00 10:48a 41,984 Guide2000.xls
08/23/00 08:26a 20,480 referential data.xls
08/24/00 08:07a 180,736 Referential.xls
6 File(s) 296,960 bytes

I wish to write a program (VBA or VB or any suitable language) to parse
this text file into a more useable format for import into MS Access. Such
a format would be:-

File Size Date created
===========================================
E:\DATA\ITINFO\DIR02\Guide.xls 53,760 08/25/00
E:\DATA\ITINFO\DIR02\Guide2000.xls 41,984 08/25/00
E:\DATA\ITINFO\DIR02\referential data.xls 20,480 08/23/00
E:\DATA\ITINFO\DIR02\Referential.xls 180,736 08/24/00

I've not written anything of this kind before am not sure how to start.

Cheers, JimmyL

 

[vb5prgrmr]

Here are my results

INSERT INTO MyTable(PathFile,Size,Date) VALUES('E:\DATA\ITINFO\DIR02\Guide.xls', 53760, 2000-08-25
INSERT INTO MyTable(PathFile,Size,Date) VALUES('E:\DATA\ITINFO\DIR02\Guide2000.xls', 41984, 2000-08-25
INSERT INTO MyTable(PathFile,Size,Date) VALUES('E:\DATA\ITINFO\DIR02\referential data.xls', 20480, 2000-08-23
INSERT INTO MyTable(PathFile,Size,Date) VALUES('E:\DATA\ITINFO\DIR02\Referential.xls', 180736, 2000-08-24

from the following code...

in a module place the following

Option Explicit

Public Type MyInfo
BasePath As String
FileName As New Collection
FileSize As New Collection
FileDate As New Collection
End Type


Public Function AssemblePaths(PathToTextFile As String) As MyInfo

On Error GoTo APE

Dim FNumb As Integer, T As String, I As Integer

If Dir(PathToTextFile) = &quot;&quot; Then Exit Function

FNumb = FreeFile

Open PathToTextFile For Input As #FNumb

Do While Not EOF(FNumb)

Line Input #FNumb, T

T = Trim(T)
If T = &quot;&quot; Then GoTo NextLine

If Left(T, Len(&quot;Directory of&quot) = &quot;Directory of&quot; Then
T = Mid(T, Len(&quot;Directory of&quot + 1)
If Right(T, 1) <> &quot;\&quot; Then T = T & &quot;\&quot;
AssemblePaths.BasePath = Trim(T)

End If

If IsDate(Left(T, 8)) = True Then
If InStr(1, T, &quot;<DIR>&quot = 0 Then
AssemblePaths.FileDate.Add Left(T, 8)
T = Trim(Mid(T, 17))
I = InStr(1, T, &quot; &quot
AssemblePaths.FileSize.Add Left(T, I - 1)
AssemblePaths.FileName.Add Mid(T, I + 1)
End If
End If
NextLine:
Loop

Close #FNumb

Exit Function
APE:

MsgBox Err.Description

End Function

in a form add a command button (command1) and add the following code.


Option Explicit

Private Sub Command1_Click()

Dim MyType As MyInfo, I As Integer, SQL As String

MyType = AssemblePaths(&quot;C:\z\demo.txt&quot

For I = 1 To MyType.FileDate.Count

SQL = &quot;INSERT INTO MyTable(PathFile,Size,Date)&quot;
SQL = SQL & &quot; VALUES('&quot; & MyType.BasePath & MyType.FileName.Item(I)
SQL = SQL & &quot;', &quot; & CDbl(MyType.FileSize.Item(I)) & &quot;, &quot;
SQL = SQL & FormatDateTime(MyType.FileDate.Item(I), vbGeneralDate)

Debug.Print SQL

Next I

End Sub

Walk through the code and try to start to understand the string parsing functions that I put in the code and also look up in help for the left,right,mid,instr,format,formatdatetime,formatnumber functions along with cint,clng, and cdbl and I think you will be on you way to parsing out complex text files in no time.

Good Luck

 

[JimmyL]

Many thanks for this!

I'll have a good look at the string manipulation functions and the rest of the code. This is a good place to start at.

Cheers, JimmyL