Incorrect Data Import (1 Viewer)

[dapfeifer]

Hey all,

Would anyone have any idea why my database will properly import my data sometimes, then other times completely mess up the import? I can use the exact same data file and like it said, sometimes it imports fine and others it just messes up completely.

I believe the issue may be in the way I have to break down the data and re-assemble it into the desired table, but it still doesn't really explain to me why it won't import correctly 100% of the time.

Any ideas or thoughts would be appreciated. I can elaborate on my import methods if needed.

 

[georgedwilkinson]

You didn't give us much to go on...how does it mess up? Is the data corrupt? In what way? Does it give an error message? Is there an error table? Mushroom cloud? What?

 

[dapfeifer]

Mushroom Cloud. Big one...

Ha...yeah sorry for being a little (or a lot) vague. I was frustrated at the time I wrote the message so I immediately assumed everyone would know what I meant.

The import process is somewhat elaborate, but has to be for the data im importing. The data comes from an already formatted text file. Unfortunately, the formatting it is in makes it difficult to get certain values (in this case, a payor code and payor name). In the file the payor code and name are followed by the transactions made by that payor. What I need to have done (and have accomplished to an extent) is to assign the payor code and name to each transaction (or in this case, put them in columns in prior to all the transaction information). So the process goes as follows:

1) import text file essentially as is into a table, putting in columns breaks where necessary to break up the data as needed.
2) using a query, insert transaction data into a second table, leaving two blank columns for payor code and payor name to go into.
3) using VBA, go back through the import table and assemble a payor code and name information, then assign it accordingly to its transactions in the second table.
4) repeat 3 until EOF.

Because of the way the data is stored, assigning payor info to transactions is done based on data in the initial import table. I believe this may be where the flaw is, but as mentioned in my first post the import works sometimes and other times it does not.

I am not at the computer that has the database and won't be again until monday. If you would like, i'll upload the database and some sample data and you can have a look if you want. Or else I'll upload the code in question. Thanks for the reply though!

 

[georgedwilkinson]

I'd be happy to take a look when you're ready. Be sure to PM me when you load it or someone else can grab it if I'm not looking.

 

[dapfeifer]

I'm still working on finding a way to make a sample test file. The data isn't exactly something I can just give out, so for now I'll post my import code here. If you can maybe spot what's wrong in it then let me know, otherwise I'll continue to work on the test file.

And I do realize my SQL and VBA coding could be better, particularly the SQL. However, I had even worse issues with importing when I normalized things better, so I just ended up with this.

Option Compare Database
Option Explicit
 
Public Sub SetupUsableData()
    Dim db As DAO.Database
    Dim ImportData As DAO.Recordset
    Dim UsableData As DAO.Recordset
    Dim sqlCode As String
    Dim PayorCode As String
    Dim PayorName As String
    Dim lngRecordCount As Long
 
    ' Warnings are disabled so the user doesn't have to go through the process of clicking yes several times.
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    ' Generating the code to import the data file and get the data I needed was tricky.  In the end I found a way,
    ' but it is a little messy to look at.  I ended up having to break up the first few columns of the data file in order
    ' several times to properly separate all the data I needed.  I then piece the code back together based on the way it is
    ' in tblImportData.  It is inserted into tblUsableData.  If you want to look at how the data is broken up, open
    ' tblImportData or the specification file from File -> Get External Data -> Import -> Advanced.
 
    ' The import method is specifically designed to work with the detail file in its setup as of 7/21/08.  If any
    ' modification is made to the detail file's layout, even the slightest change, the import method probably will no longer
    ' work.
 
 
    sqlCode = "INSERT INTO tblUsableData ( SRCol," & _
    "Client_Code, Client_Name, Tran_ID, Inbnd_Bytes, Otbnd_Bytes, Bytes, Rate, Amount, RCCol, MTCol, MCCol, IOCol, ASCD_Client, " & _
    "Billable_Bytes, ECol ) SELECT tblImportData.PSR, " & _
    "IIf([PN1CC2] Is Null,[CC1],[CC1]+[PN1CC2]) AS Expr1, IIf([PC1CN2] Is Null,[PN2CN1],[PN2CN1]+[PC1CN2]) AS Expr2, " & _
    "tblImportData.PC2TRID, tblImportData.INBND_BYTES, tblImportData.OTBND_BYTES, tblImportData.BYTES, tblImportData.RATE, tblImportData.AMOUNT, " & _
    "tblImportData.RCCol, tblImportData.MTCol, tblImportData.MCCol, tblImportData.IOCol, tblImportData.ASCD_CLIENT, " & _
    "tblImportData.BILLABLE_BYTES, tblImportData.ECOL FROM tblImportData " & _
    "WHERE tblImportData.BILLABLE_BYTES Is Not Null;"
    DoCmd.RunSQL sqlCode
 
    ' Open only entries where the payor_code is null will ensure that only the most recent imported data is opened because
    ' all other data should have a papyor code.
    Set UsableData = db.OpenRecordset("SELECT * FROM tblUsableData WHERE Payor_Code Is Null")
    Set ImportData = CurrentDb.OpenRecordset("SELECT * FROM tblImportData")
 
    ' The following while statement runs until the end of tblImportData.  The code here looks for data in tblImportData's
    ' first column that matches "PA", then get the payor code from the next two columns.  This payor_code is applied to
    ' rows in tblUsableData until "TOTAL" is reached in tblImportData.  At that point, "TOTAL" is inserted into the
    ' Client_Name portion of tblUsableData, and the payor_code and payor_name are applied for the last time in
    ' tblUsableData. The process then loops and a new payor_code is gathered, starting the process over again.
 
    Do While Not ImportData.EOF
    If (ImportData.Fields("PSR").Value = "PA") Then
        If (ImportData.Fields("PC2TRID").Value <> "") Then
            PayorCode = (ImportData.Fields("PC1CN2").Value + ImportData.Fields("PC2TRID").Value)
            If (ImportData.Fields("PN2CN1").Value <> "") Then
                PayorName = (ImportData.Fields("PN1CC2").Value + ImportData.Fields("PN2CN1"))
            Else
                PayorName = ImportData.Fields("PN1CC2").Value
            End If
            ImportData.MoveNext
        Else
            PayorCode = ImportData.Fields("PC1CN2").Value
            If (ImportData.Fields("PN2CN1").Value <> "") Then
                PayorName = (ImportData.Fields("PN1CC2").Value + ImportData.Fields("PN2CN1"))
            Else
                PayorName = ImportData.Fields("PN1CC2").Value
            End If
            ImportData.MoveNext
        End If
        Do Until ImportData.Fields("PN1CC2").Value = "   TOTAL"
        If ((ImportData.Fields("PSR").Value = "R" Or ImportData.Fields("PSR").Value = "S") And ImportData.Fields("BILLABLE_BYTES") <> "") Then
            UsableData.Edit
            UsableData.Fields("Payor_Code").Properties("Value") = PayorCode
            UsableData.Fields("Payor_Name").Properties("Value") = PayorName
            UsableData.Update
            UsableData.MoveNext
        End If
        ImportData.MoveNext
        Loop
        If (IsNull(UsableData.Fields("Client_Code").Value)) And IsNull(UsableData.Fields("Rate").Value) Then
            UsableData.Edit
            UsableData.Fields("Payor_Code").Properties("Value") = PayorCode
            UsableData.Fields("Payor_Name").Properties("Value") = PayorName
            UsableData.Fields("Client_Name").Value = "TOTAL"
            UsableData.Update
            UsableData.MoveNext
        End If
    End If
        ImportData.MoveNext
    Loop
 
    ' Once completed, all data is removed from tblImportData to conserve space
    DoCmd.RunSQL "DELETE FROM tblImportData;"
    DoCmd.SetWarnings True
    MsgBox "Import Complete!"
End Sub

 

[georgedwilkinson]

Sorry, my attention span just isn't long enough for this right now. Perhaps someone else could take a look.

I'll be happy to look when you have something a little more specific to look at.

 

[dapfeifer]

Alright, no problem. I did find some references in my tables and queries that I thought I had cut off and removed, but turns out they were still there and for some reason randomly deciding to prematurely sort the data, which affected the assignments and whatnot. I've also implemented error checking in the event that it happens again so the data won't copy if it has been setup properly.

Consider the problem closed...for now...

But thanks again for at least taking time to look at the issue.

 

[georgedwilkinson]

NP...just took a nap and was gonna have another go at it. Let us know if you get anything.