Reading a long text file to insert rows in a table

[pacctono]

Hello, guys!

I am reading a long text file (>140.000 lines) to get the values to be inserted in a table using VBA, but it takes too long (more than 40 minutes). This is my code:

    entFile = FreeFile()                    ' Returns an Integer representing the next file number available for use by the Open statement.
    txtFile = "D:\CAEES\ENERO-2021.txt"
    Open txtFile For Input As #entFile
    DoCmd.SetWarnings False
Debug.Print "Inicio", Now()
    Do While Not EOF(entFile)
        Line Input #entFile, txtEntrada     ' Reads a single line from an open sequential file and assigns it to a String variable.
'Debug.Print "Lngitud de la linea: " & Len(txtEntrada)
        If 79 <= Len(txtEntrada) Then
            txtComprobante = Mid(txtEntrada, 1, 6)
            txtNumero = Mid(txtEntrada, 7, 5)
            txtCuenta = Mid(txtEntrada, 12, 17)
            txtFecha = Mid(txtEntrada, 29, 8)
            txtConcepto = Mid(txtEntrada, 37, 30)
            txtOperacion = Mid(txtEntrada, 67, 1)
            txtMonto = Mid(txtEntrada, 68, 12)
            fecFecha = Format(txtFecha, "00/00/0000")
            If "D" = txtOperacion Then
                monDebe = CCur(txtMonto) / 100#
                monHaber = 0#
            Else
                monDebe = 0#
                monHaber = CCur(txtMonto) / 100#
            End If
'Debug.Print txtComprobante, txtNumero
'
' Insertar los datos en la tabla local
'
            txtMiSql = "INSERT INTO co_comprobantes (lo_asiento_id, en_numero_registro, tx_cuenta, fe_fecha, " & _
                            "tx_concepto, tx_operacion, mo_debe, mo_haber)" & vbCrLf & _
                        "VALUES(" & txtComprobante & ", " & txtNumero & ", '" & txtCuenta & "', " & _
                            "'" & fecFecha & "', '" & txtConcepto & "', '" & txtOperacion & "', " & str(monDebe) & _
                            ", " & str(monHaber) & ")"
            DoCmd.RunSQL txtMiSql
        End If
    Loop
Debug.Print "Final", Now()
    DoCmd.SetWarnings True
    Close #entFile

Can I get any help to do this faster, please?

 

[theDBguy]

You could try to use the second method mentioned in this article.

How to Read a Text File

Invariably, at one point or another in your journey to write VBA in Access, there will come a time when you will need to read the contents o...

thedbguy.blogspot.com

 

[sonic8]

Can I get any help to do this faster, please?

A tiny change that should make it a little bit faster is:
Replace: DoCmd.RunSQL txtMiSql
with: CurrentDb.Execute txtMiSql

For bigger performance improvements, try using the TransferText import, as suggested by @Pat Hartman.

 

[The_Doc_Man]

To amplify this, Pat's suggestion is really better than what you are doing, but it doesn't stop you from having to run a query to fix up a few cases.

You can "cheat" by copying a few lines from your file into another file. I.e. keep just a few lines in order to develop the Import Specs. Import that file, which should be easy enough. You might have to "adjust" the data type for the columns - but once that is done the first tie, it should stay as you set it. Then erase the test run and import the entire file.

Using this method, you MIGHT have to run an UPDATE query to go back and fix those cases represented by

            If "D" = txtOperacion Then
                monDebe = CCur(txtMonto) / 100#
                monHaber = 0#
            Else
                monDebe = 0#
                monHaber = CCur(txtMonto) / 100#
            End If

because the ImportText will not do this kind of fixup in-line.

Beware of one other factor. From your description this might be done often enough that if you use an intermediate table and erase it each time, you will develop a significant case of database bloat. This occurs when the DB file grows in size enough that you need to run a Compact & Repair. If this operation will be frequent enough, you might need to ask about a solution for THAT problem, too. But I don't want to confuse you with too many issues at once.

 

[xavier.batlle]

You can also link the txt file as a linked table and then execute an insert query.

 

[ebs17]

sSQL = "INSERT INTO co_comprobantes (lo_asiento_id, en_numero_registro, tx_cuenta, fe_fecha," & _
        " tx_concepto, tx_operacion, mo_debe, mo_haber)" & _
        " SELECT F1, F2, F3, Format(F4, '00/00/0000'), F5, F6," & _
        " IIF(F6='D', F7/100, 0) AS monDebe, IIF(F6='D', 0, F7/100) AS monHaber" & _
        " FROM [Text;DSN=NameSpec;FMT=Fixed;HDR=no;IMEX=2;CharacterSet=850;DATABASE=D:\CAEES\].[ENERO-2021.txt]"
      
CurrentDb.Execute sSQL, dbFailOnError

This looks like a text file with fixed lengths. To do this, you create the import specification once, which would then be used within the query
=> NameSpec

 

[pacctono]

Hello, guys!

I solved it! I forgot to say that I could control the text file (It is created with BBX a business basic).

This was my solution:

Sub cargarComprobantes()
    Dim oFSO As Object
    Dim oFolder As Object, objFile As Object
    Dim strSql As String, txtFile As String
    Dim txtMes(14) As String, txtNombre As String, entPeriodo As Integer, entEjercicio As Integer
    Const strDirTra As String = "D:\caees\comprobantesAC"

    txtMes(0) = "INICIO"
    txtMes(1) = "ENERO"
    txtMes(2) = "FEBRERO"
    txtMes(3) = "MARZO"
    txtMes(4) = "ABRIL"
    txtMes(5) = "MAYO"
    txtMes(6) = "JUNIO"
    txtMes(7) = "JULIO"
    txtMes(8) = "AGOSTO"
    txtMes(9) = "SEPTIEMBRE"
    txtMes(10) = "OCTUBRE"
    txtMes(11) = "NOVIEMBRE"
    txtMes(12) = "DICIEMBRE"
    txtMes(13) = "CIERRE"

    Set oFSO = CreateObject("Scripting.FileSystemObject")

    DoCmd.SetWarnings False
    For entEjercicio = 2020 To 2024
        For entPeriodo = 1 To 13
            txtNombre = txtMes(entPeriodo) & "_" & entEjercicio & ".txt"
            txtFile = strDirTra & "\" & txtNombre

            If oFSO.FileExists(txtFile) Then
                strSql = "INSERT INTO co_comprobantes_detalles" & vbCrLf

                strSql = strSql & "SELECT * FROM [Text;HDR=Yes;FMT=Delimited;Database=" & strDirTra & "]." & txtNombre
                DoCmd.RunSQL strSql
            Else
            End If
        Next entPeriodo
    Next entEjercicio
    DoCmd.SetWarnings True
    Set fso = Nothing
End Sub     ' cargarComprobantes

It took less than 20 minutes to load 47 texte files (more than 1,500,000 lines) to a table.

Thansk for your help!