Parsing (tokenising) text files into excel 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

 

[Petemush]

I've writtem a program that does something like this(could easily be modified) in Java. Don't know if that's going to be any use though?

Cheers,

Pete

 

[JimmyL]

Hi Pete,

can you give me the code? I unfortunately don't know Java, but it may be possible to translate the code into VB. At worst it might give me pointers on how to start.

Cheers, JimmyL

 

[Petemush]

Sorry Jimmy, I was a bit ahead of myself there and realised after I sent the post that I'm at work and the code will be at home.

I might have a go at knocking up some code to do it in VBA though, give me a break from current work!

Sorry again,

Cheers,

Pete

 

[JimmyL]

Hi Pete!

No Probs.

If you do get round to getting some code together, I'll be very interested.

Cheers, JimmyL

 

[Petemush]

Okay this really will need some work from you to finish it off but it should be enough to get you started. Rather than your requested format I went for a standard import text file with fields delimited by commas so you should have no problem importing it.

Only thing is, this is very particular and is assuming the filenames will always end .xls. I know this probably isn't true but once again I'm sure you can change it.

Public Sub ConvertFiles()

Dim MyString, MyString2, MyString3, m1, m2, MainPath, path, size
Open &quot;c:\pete\serverlog.txt&quot; For Input As #1
Open &quot;c:\pete\serverlog2.txt&quot; For Output As #2
Input #1, MainPath
MainPath = Mid(MainPath, 14)

Do While Not EOF(1)
Input #1, MyString
If Not EOF(1) Then Input #1, MyString2
If Not EOF(1) Then Input #1, MyString3
If Right(MyString, 3) = &quot;xls&quot; Then
path = MainPath & &quot;\&quot; & MyString
size = m1 & m2
Write #2, path, size
ElseIf Right(MyString2, 3) = &quot;xls&quot; Then
path = MainPath & &quot;\&quot; & MyString2
size = m2 & MyString
Write #2, path, size
ElseIf Right(MyString3, 3) = &quot;xls&quot; Then
path = MainPath & &quot;\&quot; & MyString3
size = MyString & MyString2
Write #2, path, size
End If
m1 = MyString2
m2 = MyString3
Loop
Close #1
Close #2

Open &quot;c:\pete\serverlog.txt&quot; For Input As #1
Open &quot;c:\pete\serverlog2.txt&quot; For Input As #2
Open &quot;c:\pete\serverlog3.txt&quot; For Output As #3

Do While Not EOF(1)
Line Input #1, MyString
If MyString Like &quot;*xls*&quot; Then
Line Input #2, MyString2
Debug.Print MyString2 & &quot;,&quot; & Left(MyString, 8)
Write #3, Mid(MyString2, 2, (Len(MyString2)) - 2) & &quot;,&quot; & Left(MyString, 8)
End If
Loop
Close #1
Close #2
Close #3

End Sub


It's a bit long winded but my brains not working today so i just did it the first way I thought of.

Hope it helps, a little bit at least!

Cheers,

Pete

 

[JimmyL]

Thanks a lot Pete!

I'll have a play with this code.

Many thanks,

JimmyL

 

[jnicks]

River is sort of Streams on Steroids, and it handles producing Title line , tab separated columns, ready to import to excell or Paradox/Access...

File	Size	Time/Date
Guide.xls	53,760	09:57a	08/25/00
Guide2000.xls	41,984	10:48a	08/25/00
referential data.xls	20,480	08:26a	08/23/00
Referential.xls	180,736	08:07a	08/24/00
[code]

Using as input the snip from your post....

[tt]
  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
[/tt]

The River script took the input, dirinfo.txt, and sorted it out into ImportMe.txt, both above.

Here's the script.  It took me longer to post than to do the conversion, grin.

[code]type  dirinfo.txt| deltop 4| delbot 1 >temp
type temp | snip 40-999  17-39 11-16 1-8 | trimcols 2 >temp
echo File   Size   Time/Date| postpend temp > importme.txt
del temp
{code]


If you want a link, let me know j@roninsg.com

J

p.s. River would be just as happy with the filtering the output from a [b]DIR /S[/b} command.