How to Import data to QuickBooks through CSV using QODBC

Note: QODBC does not support direct import, But you can write VBA code that reads a CSV / Excel file & generates insert statements.

There are two ways to Import data to QuickBooks through CSV using QODBC.

1) Using Wizard

Create a new MS Access Database file & link the Customer table.

Please refer to Using QuickBooks Data with Access 2016 / 365 / 2013 32-bit for lining the Customer table.

Go to the "External Data" tab on Microsoft Access, as shown in the screenshot below, and click on the "Text File."

The "Get External Data" screenshot will be shown below. Select the CSV file by clicking the "Browse" button. (*Note: the file name must be less than 64 characters. Otherwise, Microsoft Access will not be able to import it). Choose the second option. Append the copy of the records to the table.

Next, select the option "Delimited - Characters such as comma or tab separate each field," as shown in the screenshot below, and click "Next."

Next, make sure "Comma" is selected in the "Choose the delimiter that separates your fields" and the double quotes symbol " is selected in the "Text Qualifier" as shown in the screenshot below, and click "Next" to continue.

Now click on the Advanced button to configure import fields.

Now write the field name you want to insert from the CSV file & click OK.

Click Finish to complete the import process.

Import Done. Customer data from CSV is inserted into the QuickBooks customer table.

Or

2) Using VBA Code

Please refer to Using QuickBooks Data with VBA

In this tutorial, we are showing a sample VBA script inserting Customer & Invoice.

Importing Customer to QuickBooks through CSV file using QODBC & Microsoft Access VBA

We have a Customer CSV file with Customer Name, CompanyName, Phone & Email fields.

We will import these fields to QuickBooks using VBA. You can see the below records from the Customer CSV file.

Please refer to the sample VBA code for Importing Customers to QuickBooks through CSV file using QODBC.

By clicking on the "Add Customer" button, the Customer list in a CSV file will be imported into QuickBooks using QODBC.

Please Note: You need to change the CSV file location & VBA script according to your CSV file location & file data.

Please refer below code which is used in this example:

Import Customer:

Option Compare Database

Public Sub exampleCsvImportCustomer()

Dim oConnection As New ADODB.Connection
Dim sConnectString
Dim MyArray As Variant
Dim fso As Variant
Dim objStream As Variant
Dim objFile As Variant
Dim sSQL As String
Dim sMsg
Dim rs
Dim I As Integer
i = 0
sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

'' For 64-bit use this one-->sConnectString = "DSN=QuickBooks Data 64-bit QRemote;"

sSQL = "SELECT * FROM customer"

Set rs = New ADODB.Recordset
oConnection.Open (sConnectString)
rs. Open sSQL, Connection, adopt dynamic, adLockOptimistic

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists("C:\Input\Customer.csv") Then
Set objStream = fso.OpenTextFile("C:\Input\Customer.csv", 1, False, 0)
End If

Do While Not objStream.AtEndOfStream
strLine = objStream.ReadLine
ReDim MyArray(0)
MyArray = Split(strLine, ",")
rs.AddNew
rs("Name") = MyArray(0)
rs("CompanyName") = MyArray(1)
rs("Phone") = MyArray(2)
rs("Email") = MyArray(3)
rs.Update
i = i + 1

Loop

sMsg = sMsg & "Customer Added!!!"
MsgBox sMsg

End Sub

Please Note: If you have a 64-bit application, then you need to use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote" (i.e., sConnectString = "DSN=QuickBooks Data 64-bit QRemote; OLE DB Services=-2;")

Importing Invoice to QuickBooks through CSV file using QODBC & Microsoft Access VBA

We have an Invoice CSV file with CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineQuantity, InvoiceLineSalesTaxCodeRefListID & FQSaveToCache fields.

In this example, we are creating two Invoices, each having 3 InvoiceLine. We will import these fields into QuickBooks using VBA. You can see the below records from the Invoice CSV file.

Please refer to the sample VBA code for Importing Invoices to QuickBooks through CSV file using QODBC.

By clicking the "Add Invoice" button, the Invoice list in a CSV file will be imported into QuickBooks using QODBC.

Please Note: You need to change the CSV file location & VBA script according to your CSV file location & file data.

Please refer below code which is used in this example:

Import Invoice:

Option Compare Database

Public Sub exampleCsvImportInvoice()
Dim oConnection As New ADODB.Connection
Dim sConnectString
Dim MyArray As Variant
Dim fso As Variant
Dim objStream As Variant
Dim objFile As Variant
Dim sSQL As String
Dim rs
Dim sMsg
Dim I As Integer
i = 0

sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

'' For 64-bit use this one-->sConnectString = "DSN=QuickBooks Data 64-bit QRemote;"

sSQL = "SELECT * FROM InvoiceLine"

Set rs = New ADODB.Recordset
oConnection.Open (sConnectString)
rs.Open sSQL, oConnection, adOpenDynamic, adLockOptimistic

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists("C:\Input\Invoice.csv") Then
Set objStream = fso.OpenTextFile("C:\Input\Invoice.csv", 1, False, 0)
End If

Do While Not objStream.AtEndOfStream
strLine = objStream.ReadLine
ReDim MyArray(0)
MyArray = Split(strLine, ",")
rs.AddNew
rs("CustomerRefListID") = MyArray(0)
rs("RefNumber") = MyArray(1)
rs("InvoiceLineItemRefListID") = MyArray(2)
rs("InvoiceLineDesc") = MyArray(3)
rs("InvoiceLineRate") = MyArray(4)
rs("InvoiceLineQuantity") = MyArray(5)
rs("InvoiceLineSalesTaxCodeRefListID") = MyArray(6)
rs("FQSaveToCache") = MyArray(7)
rs.Update
i = i + 1

Loop

sMsg = sMsg & "Invoice Added!!!"
MsgBox sMsg

End Sub

Please Note: If you have a 64-bit application, then you need to use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote" (i.e., sConnectString = "DSN=QuickBooks Data 64-bit QRemote; OLE DB Services=-2;")

Download Sample

Also, Refer:

QODBC Reports Architecture Mismatch Problem When connecting