Parsing table with append query

[NZArchie]

Hi again,

I've been doing a bit of research around this, and rather than re-starting old threads or dragging mine off topic, I thought I should start a new one. My situation is keeping track of insurance payment details emailed in .csv format. My database is set out like this:

ADVISOR: Advisor ID, Name etc

CUSTOMER: Cust ID, Name etc

PROVIDER: Provider ID, Name etc

PAYMENT: Payment ID, Amount, Policy number etc



POLICY: Policy number, Type, Customer ID, Advisor ID, Provider ID

So, having imported the .csv file (which has multiple irrelevant rows/columns/subtotals etc) into a temporary table, how can I parse the temp table, and append it across multiple tables? A row is a relevant payment if it is after row 6 and has a value in the first field. The struggling point for me is that I would need to check if, for instance, the customer making the payment is already in the database

 

[NZArchie]

I really wish I could parse it with VBA, because the date, pay run ID etc are all within strings in the first few rows, and I would need to take substrings of these to get the date of the payment. Is this possible?

I have created a simple query which returns all rows with something in the first field, but I really need more processing than that.

Thanks for any help

 

[Galaxiom]

Open the csv using either the OpenTextFile method of the FileSystemObject or the OpenAsTextStream method of the File Object.

This will allow you to read the file line by line into a string variable and parse it.

Direct the flow of the processing to write values to recordsets as required.

 

[NZArchie]

Oh that is such a shame to rewrite my method. Is there no way to parse a record before the insert statement?

 

[Galaxiom]

You may be able to parse the header records after they are imported. You would need some reliable way to determine which records are the headers. Without seeing samples the data it is a bit difficult to offer specific suggestions.

 

[NZArchie]

The headers aren't really important for me, I've made an import specification which creates its own headers, but for example, the first column of the table is an agent name like "Baker, Rod" I want to be able to parse that, and retrieve first and last names from it.

Then I would need to check if that agent is already in the database etc. This complicates very quickly, can I do it through vba instead of SQL, one record at a time?

 

[Galaxiom]

Parsing is pretty simple if the format is consistent.

LastName: Left(namefield, Instr(namefield, ",") - 1)
FirstName: Right(namefield, Len(namefield) - Instr(namefield, ",") - 1)

Beware though that any missing or extra commas or a double space after the comma can screw it up. When data has been entered by humans, it is rare that the format is consistent enough to be 100% reliable. Ideally you need to be able to verify the format trap inconsistencies.

If I am checking for existing entries I open a recordset and use FindFirst (or Filter if multiple fields are searched in an ADO recordset). This can also be done with a DLookUp. However while this is fine for a single check it is comparatively slow for bulk processing.

It could also be done by creating a temporary table with the parsed output then outer joining to the main table on the firstname and lastname to detect those that are already in the table and only appending those records with no match.

 

[gemma-the-husky]

Archie

can you post a sample of the csv file?

 

[NZArchie]

So I think, using Dave's solution from an earlier thread, I am on the right track. From a form, I am using transferText to import to a temporary table, then parsing that with a recordset. My temporary table looks exactly the same as the .csv file, only in access, and with relevant headers

I think I will do the above, and create another temp table from my recordset parsing and perform massive join SQL statements.

I attach an example csv in case its format means there is a clearly better way. (Just change the file extension back to .csv) I am also worried about how to extract names that don't have a comma/are business names.

How do you create a new table and append from vba?

 

[Galaxiom]

Normally you would just keep a permanent table as the "temporary". Run a delete query to clear it out and then run an append query. There are several ways to run a query from VBA.

DoCmd.OpenQuery "queryname"
DoCmd.RunSQL " INSERT INTO sometable(field1, field2) etc"

With these you will want to turn off Access warnings before they run.
DoCmd.SetWarnings False

Be sure to turn on Warnings again or they will remain off including warnings about deleting objects. Put this in the Exit section of the code to ensure it is also run after any error.

You can also avoid the warnings by using the Execute method:

Currentdb.Execute "INSERT INTO etc", dbFailOnError

The FailOnError is optional but without it Access will not report if the operation had problems such as key conflicts.

Although many developers will hold a temporary table in the front end it is not a good practice. Better to maintain a linked table in another database specially for the temporary tables in that project. This minimises the cance of corruption and eliminates the bloading of the front end.

I call it a Side End. Search the forum for this term if you want to previous discussion of how to use a Side End.

You could check if the client name includes a comma and process differently where there is none. But what about a business name that happens to have been entered with a comma?

Inconsistencies in the format of names are always going to be a problem. Such things are the bane of developers. All you can do is run test procedures over the data, see what comes out and adapt it as necessary.

 

[NZArchie]

Cheers for that, it seems to definitely be a good idea to keep the tempTable out of the front end, but I'm going to postpone doing that because at the moment I still don't have anything useful working at all.



I'm currently running this SQL DELETE statement to delete the irrelevant rows before my processing, but it's leading me to a error "3167: object deleted" when I try to access the recordset afterwards. If it is specified as a dynaset, how is this so?

The important bits of code are like this

 Dim db As Database
    Dim rsTempTable As Recordset
    Dim rsPayDetail As Recordset

    importFromSovTempTable = False
    Set db = CurrentDb ' Reference to current database
    Set rsTempTable = db.OpenRecordset(tempTable, dbOpenDynaset) ' Open the temporary table as a recordset

.... some prelim processing.....

    ' SQL from query to extract only payment details
    strSql = "DELETE [TempTable-SovImport]![AgentName] AS Expr1, * " & vbCrLf & _
            "FROM [TempTable-SovImport] " & vbCrLf & _
            "WHERE ((([TempTable-SovImport]![AgentName]) Is Null Or ""«Expr»""='Products' Or ""«Expr»""='Benefits'));"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strSql)
    
    DoCmd.SetWarnings True


        ' move back to beginning, and begin actual processing
        
        .MoveFirst
       
        Do Until .EOF
            wholename = rsTempTable!agentname
            If wholename = "" Or wholename = "Benefits" Or wholename = "Products" Then
                
                .Delete
                
            Else
            
            End If
            .MoveNext
        Loop
    End With
    rsTempTable.Close

It errors on the line
wholename = rsTempTable!agentname

after about 10 runs through the loop. .Update seems to not want to work, because there has been no edit or addition. Any thoughts?

Also, once I extract the last name (if applicable) can I use vba to create a new column for it, so that I can pass those two to my actual database?

Cheers again guys,
Arch

 

[Galaxiom]

Run the Delete query first then open the recordset.

 

[NZArchie]

Oh awesome galaxiom that did the trick, just had to rearrange the module. Sorry I've been distracted by minor problems here, back to the original topic. Can an SQL statement like this:

INSERT INTO Advisors ( LastName )
SELECT [TempTable-SovImport].AgentName, *
FROM [TempTable-SovImport]
WHERE ((InStr(1,[TempTable-SovImport]![AgentName],',')="0"));

work? By work I mean can it be made complex enough to do everything I would in a VBA loop, and to append to my database?

How does processing in a query work? If your first criteria manipulates the data (in design view), can other criteria access it?

I guess what I'm really asking is should I loop through my table in VBA, or try to do it with SQL?

 

[Galaxiom]

The * means all fields so you can't have that. The number of fields in the Insert line must match the number of fields in the Select.

The return value from InStr is a Long integer so drop the quote marks from around the zero. The first argument is optional and 1 is the default so you can drop that too.

Single quotes sometimes only work when they are nested inside double quotes. I have not tried this particular circumstance but it might need double quotes around the comma.

Derived fields need the same derivation from the original field when referred to in the Where clause unless it is derived in a subquery first.

 

[NZArchie]

Ok, so to append with all this complicated formatting, I should call many smaller SQL queries? i.e add advisors who aren't in system already, add customers who aren't in system already, add policies to link these two, add payments to link all three

Cheers again for the help

 

[Galaxiom]

A single query can only append to one table so you would need multiple queries.

Queries are good at doing joins so they would do well at avoiding dupicate entries via a join. But then you would have to run through the data four times. You would want to clean up and parse the data into a temporary table first. Otherwise the joins would be complex SQL and unable to be built in the query design view.

Personally I would read the csv as a TextStream and open four recordsets to write the new data.

As our member MissingLinq says, "There is always more than one way to skin a cat."

 

[NZArchie]

Yeah I think you might be right in the textstream being the way, but I really need to show some results, even if they are slow, and I'm quite far down the track now. Too many ways to skin the cat!

I've got a temporary table cleaned up from an import call, and now I'm heading on the road of learning SQL. Any idea why this is the wrong syntax?

ALTER TABLE TempTable-SovImport ADD [LastName] TEXT;

 

[NZArchie]

Oop no, problem solved, seemed the table name needed []'s around it

 

[Galaxiom]

Don't worry about creating a temporay table with sql.

Just create one in Access and empty the records each time you use it.
Then you just need a garden variety Append Query to add them and a Delete to clear it out.

 

[NZArchie]

I still need to loop through my temporary table to split the advisor and customer name fields up though. eg, "Baker, Rod" has to be "Baker" in one column, and "Rod" in the other. Names in the .csv come as one. It is hard to read them in, especially things like "Mr & Mrs I A Farva".

how would four writing recordsets work?