Parse Data (1 Viewer)

Hi Everyone

I previously had a thread https://www.access-programmers.co.uk/forums/showthread.php?t=295127 which was solved by member "moke123".

The users have now changed the structure of the incoming mail and I am unable to get the new process to work.

In the attached example Db there are 2 tables , 2 Forms and a Module.

The module which runs from the Command Button on frmOne extracts from the list displayed and then should open frmTwo to display the results.

When I try to run the Module I get the following Error:-


Any help appreciated

View attachment MikeSample2.zip

The problem is your array. Array's in VBA are Zero (0) based which means they start with 0 instead of 1. In your example, there is no 25 which is why you are getting the "out of range" error.

Here is the fix

arnelgp said:

Here is the fix

Click to expand...

Excellent clean-up of the code, Arnelgp.

Hi arnelgp

Many thanks for looking at this.

Any idea why the Course Date is also showing the Nr Of Participants in the same field??

Hi Everyone

OK solved it just my stupid mistake with spelling of field name.

Thanks very much for excellent cleanup of code.

Hi Everyone

Spoke too soon.

I imported the 2 tables & both Forms together with the new Module into my working Db.

When I run the Module from frmOne I get the following error:-



Hit Debug and it highlights this line:-



Any thoughts??

It has to be your data. There are assumptions that EVERY txtmemo field is going to be formatted correctly. When they are not, your code has no validation or error handling procedures.

Hi NauticalGent

I did a Debug.Print strOut and the Immediate window shows all the correct data:

| Test | Testington | louisejames567@gmail.com | 155956 | Test Org | Test address 1 | test address 2 | | Test Town | Test Postcode | Test Billing ad1 | Test Billing ad2 | Billing | Test Town Billing | Test Postcode | Bereavement Awareness | 5th April 2017 | 3 | Test P1 | Test P2 | Test P3 |

The data comes from a web form via an email and will be fixed with this format

better to copy paste your code so it can be copy/pasted back. Try

for I=0 to ubound(strWrite)-1

As NG mentioned in his first post, Arrays start from 0 so for an array with 3 elements, ubound is 3 and the range is 0 to 2.

I would also check your source file. Unless it is a csv file, it is possible you have a hyperlink for the email address which may be confusing the import routine

Hi cj London

The code is this with your suggested amendment:-

Public Function Replacements(varString As String) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

Dim strRep As Variant
Dim strWrite As Variant
Dim RepString As String
Dim i As Integer
Dim strOut As String

strOut = Replace(varString, "Message Body:", "") 'remove "Message Body:" from the 0 position

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' arnel gp
' 20-aug-2017
'
' check end of text if no vbcrlf and ends with ":"
'
If Right(strOut, 1) = ":" Then strOut = strOut & " " & vbCrLf
'
' end of arnelgp code
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'string of items to replace with |
RepString = "Contact First Name:,Contact Surname:,Contact EMail:,Contact Phone:,Organisation:,Organisation Address 1:,Organisation Address 2:,Organisation Address 3:,Town/City:,Postcode:,Billing Address 1:,Billing Address 2:,Billing Address 3:,Billing Town/City:,Billing Postcode:,Course Title:,Course Date:,Number of Participants:,Participant Name 1:,Participant Name 2:,Participant Name 3:,Participant Name 4:,Participant Name 5:,PO Number:,Additional Comments:,--"

strRep = Split(RepString, ",")

strOut = Replace(Replace(strOut, Chr(10), ""), Chr(13), " ") 'remove the carraige returns and line feeds

'loop throught the string and make the replacements
For i = 0 To UBound(strRep)
strOut = Replace(strOut, strRep(i), "|")
Next i
Debug.Print strOut 'jjed for testing
strWrite = Split(strOut, "|") 'split the resulting string by the | character


strSql = "select * from Table2 where id = 0"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql)

'write the values to the table by there position in the array
With rs
.AddNew
For i = 0 To UBound(strWrite) - 1
'Contact firstname starts at column 1
'so we add 2 to each column
rs(i + 1).Value = Trim(strWrite(i))
Next
.Update
End With


MyExit:
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

I now get the following error:-



hit debug and it highlights the following line of code:-

rs(i + 1).Value = Trim(strWrite(i))

hi mike,

not to muddy the waters, but I was tinkering with this and tried a somewhat different approach.

It still uses the replace routines but instead of using a for next loop to add records, it constructs an sql insert statement.

After processing the string passed in, it iterates through the array and concatenates the values into a string.

it then iterates through the fields collection of the table and concatenates a string of the relevant field names.

the 2 strings are then incorporated into an sql insert and executed.

it may not be pretty or efficient but its easier to visualize so i thought i'd pass it along.

re posting code - better, but please use the code tags (the # button) to preserve indentation when you have more than a few lines of code.

The error message is telling you what the problem is but I am also confused with your code -



I can't see why this should not just be

In, fact the whole lot could just be

with regards your error, whilst in the debug mode and in the immediate window, type

?I
?strWrite(I)
?rs(I).name

to ensure these are all aligned as you require.

My guess would be that it is failing on organisation address3 - your data has no value and you are not handling the resultant null

hi CJ,

Originally Mike's data looked like the attached pic. Note the line feeds after the titles such as the participants. So using the line feeds to replace with a delimiter didn't seem to be uniform. for that reason I used the titles (Contact Name:,etc) as the items to replace with the bar delimiter.

Ah, OK, I was going by what is in the database provided

but the principle would be the same but with an extra replace function to replace

":" & chr(13) & chr(10) with "|" first

or perhaps it is
": " rather than ":"

then replace ":"

and then chr(13) & chr(10)

Also see there are a couple of extra lines at the top, so the For I= would change

I usually go the build a sql string route if data is going to vary (some web outputs exclude fields if they are blank for example) - I have a match table to match incoming field names to table field names (so I don't end up with field names with spaces/non alpha characters) - if the field is not there in the source, then (depending on requirements), the equivalent field in the table is either not populated by excluding it from the sql string or populated with a warning 'missing data', 'N/A', whatever

hi there is it resolved.

i made new subroutine in Module2.
also created new table, tblImailFields.
the table contains Fields in Text Email that corresponds to your Fields in table Table2.
the new subroutine now check if there is New Field in the Text that is not in Table2.
hope you try it.

Hi Everyone

I have tested moke123's version and this works just fine.

I will test all other process's and get back to you soonest.

Many many thanks for all your knowledge.

Many Many Thanks

Hi arnelgp

Tested your version and works a treat

Thanks very much for all the help

By the way, the code that I gave you is generic. You don't need to modify it if there are addition or subtraction of fields in the text email. You just delete and insert new column to your table and to the new table I gave you.