Search for lines from text file and import Ocean 18.07.2006 07:57:01 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.

RE: Search for lines from text file and import DavidAtCaspian 18.07.2006 15:01:02 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????? "Ocean" wrote: [Quoted Text] > 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. >

Re: Search for lines from text file and import John Nurick <j.mapSoN.nurick[ at ]dial.pipex.com> 18.07.2006 20:43:19 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\>" On Tue, 18 Jul 2006 00:57:01 -0700, Ocean <Ocean[ at ]discussions.microsoft.com> wrote: [Quoted Text] >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. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.

Re: Search for lines from text file and import "rene" <rensi128[ at ]planet.nl> 05.08.2006 14:25:31 "Ocean" <Ocean[ at ]discussions.microsoft.com> schreef in bericht news:962736F7-917F-4D45-BBFA-BD99B34820DD[ at ]microsoft.com... [Quoted Text] > 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. >