How to Import CSV Into Access using VBA

1. How to Link CSV File Into Access Database
2. Semi-Colon Delimited CSV File
3. Export a Text File Manually Into MS Access
4. Conclusion

VBA provides developers with a variety of different functions and features. This article will guide you on how to import CSV into Access VBA.

How to Link CSV File Into Access Database

Why use VBA code to import data when you have other options available? Importing data through VBA codes helps you navigate and work with various formats that aren’t available when you import data using the visual interface.

Also, on a more practical level, when you have to make the same import a couple of times, importing data with VBA codes is an efficient, more accessible, and faster method to do the job for you.

It creates a function that can be used every time, making the process quicker and easier than going through the import wizard every time.

CSV stands for Comma Separated Values. This means that the default import functionality looks for commas in the file.

However, if you’re using anything but commas, you can try the following steps to make it work.

DoCmd.TransferText acImportDelim, "Specification of Data Import", "T1", "D:\Data.csv", False

One of the easiest ways to import CSV into Access VBA is by linking the CSV file into the Access database as a table. You can then work on these tables like any other ordinary Access tables.

For example, you can create an appropriate query based on the table, which will return what you want.

The table can either be linked manually or with VBA in the following manner:

DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tableToImport", _
    FileName:="C:\MyData.csv", HasFieldNames:=true

Then, run the following code to re-link the CSV tables and perform the import.

Dim DB As DAO.Database
' Re-linking the CSV Table
Set DB = CurrentDb
On Error Resume Next:   DB.TableDefs.Delete "tableToImport":   On Error GoTo 0
DB.TableDefs.Refresh
DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tableToImport", _
    fileName:="C:\MyData.CSV", HasFieldNames:=True
DB.TableDefs.Refresh
' Now perform the import
DB.Execute "INSERT INTO anyTable SELECT c1, c2, ... FROM tableToImport " _
   & "WHERE NOT F1 IN ('A1', 'A2', 'A3')"
DB.Close:   Set DB = Nothing

If you need to import some rows from the beginning and some from the end into Access, that can also be done. Once you have linked the CSV file into Access, you can import any required rows with the following:

INSERT INTO anyTable SELECT c1, c2, ... FROM tableToImport WHERE NOT F1 IN ('A1', 'A2', 'A3')

In the statement, rows with values 'A1', 'A2', and 'A3' in column F1 are skipped and excluded from the final result set. However, if your CSV file has field names, it is important to note that if you import with HasFieldNames:=true, these names will be used as column names.

Otherwise, Access assigns column names on its own, for example 'F1', 'F2', 'F3', and so on. If your file is smaller, lines can easily be read and written quickly.

Semi-Colon Delimited CSV File

If your CSV file is semicolon-delimited, you need an Import Specification. To create this certain specification, you must attempt to import the data manually once.

This allows you to save the specification and use it with the VBA code later. This can be done in the following way:

Export a Text File Manually Into MS Access

It is also possible to import a text file manually into MS Access. This allows you to choose your text delimiters and cell delimiters.

For this to work, you need to choose external data from the menu, select the file you intend to use and step through the wizard.

Once you get the imports working through the wizards, you can easily save a particular import specification and use it for your next DoCmd.TransferText. Doing this will allow you to have nonstandard delimiters such as single quotes text and semicolons.

Conclusion

Importing CSV into Access VBA can be complicated; however, if you know the correct technique, the process becomes much more straightforward. This article outlines the exact methods and details on how to do this correctly.