Parsing a web form in to a data table

Hi to all, has anyone an idea of how to write a dynamic data parser in VBA to capture data from a text file and input it in to a database table (ms access)? The form has about 55 fields, so I'd rather write a code that is capable of picking up the field name from the text than copying a load of text. Is there a smart way to do this and avoid running a third party app to carry out the task?

The web form (which is output from the Joomla component "PERforms") can also attach an XML file, but this doesn't seem compatible for a straightforward record import in to Access.

The format of the data is as below (I've had to add extra carriage returns in order for it to display across multiple lines.:

Field1 : Test

Field2 : Test 2

Field3 : This is the address

Which is a textarea

on the form

Field4 : Field4

I'm fine getting the data somewhere Access can pick it up from, it's only parsing it that's causing me the problem.

As always, your help is most appreciated.

EDit, as requested :

Role Applied For: Door Supervisor
Title: Mr

Full Name: John Smith



SIA DL Badge Number: 01300114000000000







Home Address: Catford Road,Bellingham
London

Home Postcode: SE1 1SE

Nationality: Nigerian

I certify that I am entitled to work within the United Kingdom: Yes

Term Time Address: Sheep St, Bellingham
London

Term Time Postcode: se1 1se



Evening Phone Number: 07222284806

Mobile Number: 07922226206

Email Address: yxs@yahoo.co.uk

Most Recent or Current Employer: Employer.Blah

From stackoverflow

• Have you considered the FileSystemObject and textstream? It woulod take a little coding, but not that much.

  Access can import HTML tables, if that is an option.

  EDIT with reference to comments.

  Note that this is a rough outline and I have not made allowances for the last field being more than one line.

  Sub BuildTable()
  'Reference Windows Scripting Host Object Model '
  Dim fs As FileSystemObject
  Dim f As TextStream
  Dim strfile
  Dim a, fld, fldlist, strSQL
  
  Set fs = CreateObject("Scripting.FileSystemObject")
  
  strfile = "C:\Docs\TestData.txt"
  Set f = fs.OpenTextFile(strfile)
  
  Do While AtEndOfStream <> True
      If f.AtEndOfStream Then Exit Do
  
      a = f.ReadLine
  
      'Assumes all lines with colons have a field '
      ' at the start '
      If InStr(a, ":") > 0 Then
          fld = Left(a, InStr(a, ":") - 1)
          fldlist = fldlist & ",[" & fld & "] Text(250)"
      End If
  Loop
  
  'Run once'
  strSQL = "CREATE TABLE ImportData (" & Mid(fldlist, 2) & ")"
  
  CurrentDb.Execute strSQL
  End Sub
  
  Sub FillTable()
  'Reference Windows Scripting Host Object Model '
  Dim fs As FileSystemObject
  Dim f As TextStream
  Dim rs As DAO.Recordset
  Dim strfile
  Dim a, fld, dat, lastfield 
  
  Set rs = CurrentDb.OpenRecordset("ImportData")
  lastfield = rs.Fields(rs.Fields.Count - 1).Name
  
  Set fs = CreateObject("Scripting.FileSystemObject")
  
  strfile = "C:\Docs\TestData.txt"
  Set f = fs.OpenTextFile(strfile)
  
  rs.AddNew
  Do While AtEndOfStream <> True
      If f.AtEndOfStream Then Exit Sub
  
      a = f.ReadLine
  
      If InStr(a, ":") > 0 Then
          'field and data, assumes all lines with '
          'a colon have a field '
          'If you have tidied the table, now is a '
          'good time to check that this is a field '
          If fld <> "" Then
              rs(fld) = dat
  
              fld = ""
              dat = ""
  
          End If
  
          fld = Left(a, InStr(a, ":") - 1)
          dat = Mid(a, InStr(a, ":") + 1)
      Else
          If Trim(a) <> "" Then
              dat = dat & a
          End If
      End If
  
      If InStr(a, lastfield) > 0 Then
          rs(fld) = dat
  
          fld = ""
          dat = ""
  
          rs.Update
          rs.AddNew
      End If
  
  Loop
  End Sub
  

  David-W-Fenton : Care to post a longer answer that gives some instructions in doing this?

  Paul Green : That would be a good starting point, but as mentioned, there are 50+ fields, so ideally I'd like to use a loop to recycle the code and pick the field name up from the text file...