CODEWITHSUNDEEP
sqlexcelrowsvba
Manuel Alejandro Balanza Vzque
Manuel Alejandro Balanza Vzque

Reputation: 11

Import 200,000 rows in Excel to SQL with Macro VBA

I have a problem when passing information from Excel to SQL by a macro.

When you run the macro, the file begins to upload the rows, but suddenly stops with the following error "Overflow (6)" and this makes every 32676 rows processed.

At first I thought it was something in the records, but when I return to execute the sentence starts up all over again and stops again on record 32676,

I'm trying to upload an Excel file with more than 200,000 rows each record consists of 25 columns.

I may be doing something wrong for this to happen.

I share transaction code.

Private Sub CommandButton1_Click()
Dim conn As New ADODB.Connection
conn.CommandTimeout = 0
    Dim iRowNo As Integer
    Dim PK_ID As String, CUST_ID As Long, DOC_TYPE As String, BILL_DOC As Long, REFERENCE As String, INV_CON As String, ORDER_SALER As String, INV_REF As String, DOC_NUM As Long, GL As Long, DOC_DATE As Double, DUE_DATE As Double, ECURRENCY As String, DOC_AMNT As Currency, USD_AMNT As Currency, PAY_TERM As String



    With Sheets("ONREPSAP")

        'Abrimos conexion con el SQL server
        conn.Open "Provider=SQLOLEDB;Data Source=,1433;Initial Catalog=AmericanMovil;User ID=;Password=;Encrypt=True;"

        'Omitimos la linea de encabezados
        iRowNo = 2

        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            PK_ID = .Cells(iRowNo, 1)
            CUST_ID = .Cells(iRowNo, 2)
            DOC_TYPE = .Cells(iRowNo, 3)
            BILL_DOC = .Cells(iRowNo, 4)
            REFERENCE = .Cells(iRowNo, 5)
            INV_CON = .Cells(iRowNo, 6)
            ORDER_SALES = .Cells(iRowNo, 7)
            INV_REF = .Cells(iRowNo, 8)
            DOC_NUM = .Cells(iRowNo, 9)
            GL = .Cells(iRowNo, 10)
            DOC_DATE = .Cells(iRowNo, 11)
            DUE_DATE = .Cells(iRowNo, 12)
            ECURRENCY = .Cells(iRowNo, 13)
            DOC_AMNT = .Cells(iRowNo, 14)
            USD_AMNT = .Cells(iRowNo, 15)
            PAY_TERM = .Cells(iRowNo, 16)



            'Generamos y ejecutamos la QUERY SQL para importar las lineas de excel a SQL
            conn.Execute "insert into dbo.ONREPSAP (PK_ID, CUST_ID, DOC_TYPE, BILL_DOC, REFERENCE, INV_CON, ORDER_SALES, INV_REF, DOC_NUM, GL, DOC_DATE, DUE_DATE, ECURRENCY, DOC_AMNT, USD_AMNT, PAY_TERM) values ('" & PK_ID & "', '" & CUST_ID & "', '" & DOC_TYPE & "', '" & BILL_DOC & "', '" & REFERENCE & "', '" & INV_CON & "', '" & ORDER_SALES & "', '" & INV_REF & "', '" & DOC_NUM & "', '" & GL & "', '" & DOC_DATE & "', '" & DUE_DATE & "', '" & ECURRENCY & "', '" & DOC_AMNT & "', '" & USD_AMNT & "', '" & PAY_TERM & "')"

            iRowNo = iRowNo + 1
        Loop

        MsgBox "Reporte Cargado Correctamente | The report has finish to Uploading"


        conn.Close
        Set conn = Nothing

    End With
End Sub

In Debug, the VBA select iRowNo + 1 before the "Loop"

Upvotes: 0

Views: 447

Answers (1)

Horaciux
Horaciux

Reputation: 6477

As @Comintem said you need to change type for holding greater values.

You should declare Dim iRowNo As Long instead of Integer

Here Data Type Summary

Integer uses 2 bytes and Range from -32,768 to 32,767
Long    uses 4 bytes and Range from -2,147,483,648 to 2,147,483,647

Upvotes: 2

Related Questions