Search for lines from text file and import

[Guest]

I have a text file that contains text that will look a bit like this:
======================
UserName: Joe Bloggs
Password: orange1
Register Address: False
<StartName>Joe Bloggs<EndName>
<StartAddress>1 Long Road<EndAddress>
<StartTel>0123 333555<EndTel>
======================
I want to use VBA Access to open the file and import these lines of data
into fields within a table. This text file would come from various users so
would not just be a one off - but on a regular basis (50-250/day)
I have used this forum before and a user very ably helped to show me how to
import - but these were by identifying say Line 1 and importing it, then move
to line 2 and repeat that. My problem is that the first 3 lines as above may
not always be present in these files.
I would like some code/method to look up a line of data and import that.
I also would like to import the text only between the start <> and end <>

eg <StartName>Joe Bloggs<EndName>
the code would look for the start/end <> and would only import the text "Joe
Bloggs" into the UserName field in the table.
Finally, if I have a line called 'False' - can Access import that into a
'Yes/No' field?
Can anyone please offer some help sample code that would allow me to search
in this way?
Thanks for any advice.

 

[Guest]

This may help

Dim fs As FileSystemObject
Dim a As TextStream
Dim ThisLine As String

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.OpenTextFile("YourFileName", ForReading, False)

Do Until a.AtEndOfStream

ThisLine = a.ReadLine
(code for each line in here) -- find the first "<", if it's not there go
round the loop again,
Read all the stuff to the first ">" to give you the field name.
Write everything up to the next "<" in that field, and go on to the next line

Loop

I still prefer DAO for most table work, but I'm sure ADO will also do the job.

Working like this your 'True/false' question looks trivial:
If (The data part of thislline = "False" then
Field = False
else
Field = True
endif


Any help?????

 

[John Nurick]

Hi Ocean,

It sounds as if each text file contains a single record which can have a
variety of fields, but each field is confined to a single line and
matches one of these two patterns, where NNN is the name of the field
and VVV is a text representation of its value:

NNN: VVV
<StartNNN>VVV<EndNNN>

If that's right, I'd use regular expressions to extract the names and
value. There's an easy-to-use rgxExtract() function at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm

If you read a line from the text file into a variable strLine, you can
parse the "NNN: VVV" lines like this:

Dim strFieldName As String
Dim strValue As String
Dim strRegexp As String

strRegexp = "^(\w+):\s+(.*)"

strFieldName = rgxExtract(strLine, strRegexp, 0)
strValue = rgxExtract(strLine, strRegexp, 1)

To parse the <StartNNN>VVV<EndNNN> lines, just use

strRegexp = "^\<Start(\w+)\>(.+)\<End\1\>"

 

[rene]

I have a text file that contains text that will look a bit like this:
======================
UserName: Joe Bloggs
Password: orange1
Register Address: False
<StartName>Joe Bloggs<EndName>
<StartAddress>1 Long Road<EndAddress>
<StartTel>0123 333555<EndTel>
======================
I want to use VBA Access to open the file and import these lines of data
into fields within a table. This text file would come from various users so
would not just be a one off - but on a regular basis (50-250/day)
I have used this forum before and a user very ably helped to show me how to
import - but these were by identifying say Line 1 and importing it, then move
to line 2 and repeat that. My problem is that the first 3 lines as above may
not always be present in these files.
I would like some code/method to look up a line of data and import that.
I also would like to import the text only between the start <> and end <>

eg <StartName>Joe Bloggs<EndName>
the code would look for the start/end <> and would only import the text "Joe
Bloggs" into the UserName field in the table.
Finally, if I have a line called 'False' - can Access import that into a
'Yes/No' field?
Can anyone please offer some help sample code that would allow me to search
in this way?
Thanks for any advice.