Parsing a web form in to a data table

Question

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: [email protected]

Most Recent or Current Employer: Employer.Blah

Answer 1

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