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

 

[Uncle Gizmo]

it won't split it correctly

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

 

[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?

 

[Uncle Gizmo]

There doesn’t appear to be any suitable delimiters. Two possible solutions to this problem, if you have control of the text file, then arrange for the export process to include delimiters in it. If this is not possible then you will probably have to stream the file as indicated by the_net_2.0, my guess is that each entry will appear on a separate line, if that’s the case then the logical process would be to detect the first occurrence of a “:” (colon) that would mean the text before the colon was your entry description, and the text after the colon would be the data associated with it.

 

[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.