Parsing table with append query

[Galaxiom]

The procedure would read the csv a line at a time, parse the names and write the relevant records to Advisor, Client, Policy and Payment recordsets.

If the names were consistent you could still do it in a query instead of a loop.

However as I said earlier, parsing names where there is no consistent pattern is a nightmare. You have to design the logic to cover every potential format. So you have to recognise words like Mr and Mrs and progress through the algorithm accordingly.

Maybe you can find some kind developer who has pulished something you can use or adapt.

Otherwise there is no magic function for this and the only way to proceed is by working out the logic and testing your results. Even then it is virtually inevitable that you will get errors if new data is being imported regularly. No algorithm is foolproof because fools are so ingenious.

Ideally your algorithm should have some way to asses its confidence and present the its worst cases for review by the operator.

The Split function is quite useful in this kind of processing.

 

[NZArchie]

yep, I think that name parsing is going to be a nightmare that I will put off for a while. Although I did find something near the solution here: http://www.tek-tips.com/faqs.cfm?fid=6468

Are recordset modification noticeably slower than SQL queries? I've now moved on to making a recordset for each table, because I couldn't get the SQL to work, and it will flow easily if I do decide to change to reading a text stream

But how do you deal with foreign keys? At the stage I am writing, I have no idea what the foreign key will be. eg:

 ' Add new customer (if not already in database)
            ?????
            
            ' add new policy
            rsPolicyTable.AddNew
            rsPolicyTable!PolicyNumber = rsTempTable!PolicyNumber
            varRecordBookmark = rsTempTable.AbsolutePosition
            rsPolicyTable!customerID = ????
            rsPolicyTable!StartDate = payRunDate
            rsPolicyTable!AdvisorID = ???
            
            ' Add payment to payment field
            rsPayTable.AddNew
            rsPayTable!DateOfPayment = payRunDate
            rsPayTable!payRunNo = payRunNo
            rsPayTable!PolicyNumber = rsTempTable!PolicyNumber
            rsPayTable!AmountExGST = rsTempTable!TotalCommissionExclGST
            rsPayTable.Update
            .MoveNext

 

[Galaxiom]

yep, I think that name parsing is going to be a nightmare that I will put off for a while. Although I did find something near the solution here: http://www.tek-tips.com/faqs.cfm?fid=6468

The link certainly looks like a good start.

Are recordset modification noticeably slower than SQL queries?

Generally speaking queries are usually the fastest way if they lend themselves to it. Having said that it is remarkable just how fast a computer can churn through a recordset. I have a reasonable comparision.

I have a function that takes a 3500 record recordset, parses a field containing between one and ten words into separate words while replacing all instances of several characters, adds these words to another recordset checking for each one that there are no previous matches to end up with 4,000 unique words from a total of over 11,000 words.

The destination recordset started out blank but that last entry checked for 3999 potential matches and that recordset was tested for matches 11,000 times.

It also writes 11,000 records to another recordset as an index of these words and the original phrases.

This whole process take 65 seconds, using ordinary DAO recordsets and running on a fairly busy Gigabit network back to a server.

But how do you deal with foreign keys? At the stage I am writing, I have no idea what the foreign key will be.

The original maximum primary key values for each table need to be read into variables at the start. Then each time you write a new record you simply increment the corresponding variable.

Each time you write a record you have to check the appropriate recordset for a previous match and read back the requred foreign key. If no match is found then you write a new record to the table and use that as the foreign key for the related records.

If you are working on very large numbers of records and especially if the backend is across a network I would consider opening ADO recordsets then disconnecting them during the processing.

This writes everything into RAM (if you have enough or if you don't, to the local swap file). Then reconnect the recordsets back to the tables. I'm no expert on this but I expect it would speed up the writing. Could be worth trying later if the process takes an inordinate amount of time.

However in the meantime it might be worth starting out with ordinary connected ADO recordsets. The ADO process of detecting matches is different from DAO and it could avoid you having to change the code so much if you do try the disconnected strategy later.