Question Anything would help

[marubal21]

I have to import a txt file into a DB to track trends. Below is an example 1 (attached) of the layout of the file. When I import the file the data transfers without a problem. The only thing is that there is a “Batch” column which as the data which is different as the txt file loads. I have been looking to find a way that the column would copy the previous record’s “Batch” field and paste it unto the current one, see example 2 (attached). This is the kind of logic I have running in my head

If (current field is empty) then
{
copy and paste previous field into current field

End If
}

I hoping this would copy and paste the current “Batch” till it get to the new one and changes, I just have no clue on how to implement this.

 

[vbaInet]

Welcome to AWF!

You would need to perform this import operation using a recordset and variable to save the last batch id. Am I right to think that the Batch ID is irrespective of the Customer ID?

 

[marubal21]

Would it be much asking for an example, or where to find more info on using a record set and variable?

 

[vbaInet]

Some air code for you:

Private Function ImportFromFile() As Boolean

    Dim db As DAO.Database
    Dim recordString As String, splitRecord() As String, fnum As Integer, oldBatchID As Integer
    
    Set db = CurrentDb
    oldBatchID = 0
    
    fnum = FreeFile
    Open "Full_Path_To_File.txt" For Input As #fnum
    While Not EOF(fnum)
        Line Input #fnum, recordString
        splitRecord = Split(recordString)
        
        If UBound(splitRecord) <> 0 Then
            CurrentDb.Execute "INSERT INTO [New_Table] ([CustID], [AmtPaid], [Batch#], [Date]) " & _
                                "VALUES ('" & splitRecord(0) & "', '" & splitRecord(1) & "', " & _
                                IIf(Len(splitRecord(2)) <> 0, splitRecord(2), oldBatchID) & ", #" & splitRecord(3) & "#);"
            oldBatchID = IIf(Len(splitRecord(2)) <> 0, splitRecord(2), oldBatchID)
        End If
    Wend
    Close #fnum
            
End Function

The above assumes that AmtPaid is a string due to the $ symbol, if it's actually a type Number then you wouldn't need the single quotes surrounding the splitRecord(1) in the VALUES part.

Apparently, no recordsets needed