Parsing a text file to import into a table

[myzer92]

Help! I'm having a mind block -- I have a text file with data as the script runs every 30 minutes that I need import into Access but when I run the wizard it won't split it correctly. I need very little out of teh text file so we can start running queries and getting a baseline. I have included an example of the repeating text in my text file. For example I need to grab out the Bytes, Creation Time, Completion Time.


Copy of text file
GUID: {CD8EC9A3-AA36-4B56-91F2-A692FB28B2BC} DISPLAY: EGIS
TYPE: DOWNLOAD STATE: TRANSFERRED OWNER: test
PRIORITY: NORMAL FILES: 1 / 1 BYTES: 1266868 / 1266868
CREATION TIME: 9/22/2011 9:25:01 AM MODIFICATION TIME: 9/22/2011 9:25:18 AM
COMPLETION TIME: 9/22/2011 9:25:18 AM ACL FLAGS:
NOTIFY INTERFACE: UNREGISTERED NOTIFICATION FLAGS: 3
RETRY DELAY: 600 NO PROGRESS TIMEOUT: 1209600 ERROR COUNT: 0
PROXY USAGE: PRECONFIG PROXY LIST: NULL PROXY BYPASS LIST: NULL

 

[the_net_2.0]

if you need to stream it, here's how:

Const wMode = 1&

Dim fso As Object
Dim oFile As Object
Dim stext as string

   Set fso = CreateObject("Scripting.FileSystemObject")
   Set oFile = fso.OpenTextFile("[B]file[/B]", wMode)
   
Do Until oFile.AtEndOfStream
   stext = oFile.ReadLine
[COLOR="DarkGreen"][B]      'check if data needed and throw to table here[/B][/COLOR]
      
Loop

the tougher part though would be the combination of the extraction functions.

 

[myzer92]

I can't identify the delimiters? Please could you point them out for me.

Good question! I'm thinking the delimter is a space. For example "GUID:" should be the field name and {CD8EC9A3-AA36-4B56-91F2-A692FB28B2BC} the data then DISPLAY: the field name and EGIS the data and so on. Does that help?

 

[vbaInet]

I think it's a line by line kind of file. So every eight lines is a record. A bit like an xml file but without the meaningful hierarchical structure.

 

[gemma-the-husky]

obviously part of the problem is that the line length will not be consistent

take this -
9/22/2011 9:25:01

it would be easier if the date and time was formatted with two digits, so it was always in the same place. ie 09/22/2011 09:25:01 - then you could use MID to get the bit you wanted, from the line.

But as long as the format is consistent - you can read in lines until you get to the line starting "creation time: ", then split it using the space character, and then pick whichever bit of the split array you need.


FWIW, I think its just as easy handling text files as an xml file. Easier in many ways. You can't read a XML file by just reading in a line at a time. And a text file will be smaller.

 

[vbaInet]

The date isn't too bad but like gemma-the-husky said it would be easier if the date was a fixed format. What you can do for the Date lines is to read from ": " up to "M" as the date will always end with either AM or PM.