Enter our monthly contest at http://www.codeoftheweek.com/contest.html for the chance to win FREE software for your development needs.
View back issues of Code of the Week online at http://www.codeoftheweek.com/issues
Check out http://www.codeoftheweek.com/links/specialoffer.html for an exciting subscription offer. You do not want to miss this one.
This issue was designed for VB 4.0 32-bit and up and describes how to import a comma separated value (CSV) file. It uses DAO database access methods on an Access database. Questions? Email us at email@example.com.
The complete source code appears near the end of this issue. Be sure not to miss it!
There are two properties (CSVFilePath and CSVFilename), two variables (DatabaseName and NewTableName) and one function (ImportData) that provide the necessary information to import a CSV file into an Microsoft Access database.
DatabaseName is the name of the database to import the CSV file into. NewTableName is the name of the table in DatabaseName to store the imported data. If your table name has blanks in it, you should surround it with square brackets [ ]. CSVFilePath is the directory that contains the CSV file to import. CSVFilename is the name of the CSV file that you are importing. You need to call CSVFilePath before calling CSVFilename.
Public DatabaseName As String Public NewTableName As String Public Property Let CSVFilePath(sCSVFilepath As String) Public Property Get CSVFilePath() As String Public Property Let CSVFilename(sCSVFilename As String) Public Property Get CSVFilename() As String
ImportData is the routine that does all the good stuff. This routine will open the CSV file as a database, build a SQL string to import the file and then import the file. If the table already exists in the destination database it will remove it and overwrite it. Depending on your application you might want to change this behavior. You might want to raise an error and give the end user a choice to overwrite the table or not.
Public Function ImportData() As Long
ImportData will return the number of records that it imported. CSVFilename will return some errors if you do not call CSVFilepath before calling CSVFilename or if the import file does not exist.
Below is an example of how this class works. It is assumed e:\temp contains the file freesub.txt to import. It also assumes the database e:\temp\importtest.mdb exists already.
Dim Import As New cImportCSV Dim lRecs As Long Import.CSVFilePath = "e:\temp" Import.CSVFilename = "freesub.txt" Import.DatabaseName = "e:\temp\importtest.mdb" Import.NewTableName = "newimport" lRecs = Import.ImportData MsgBox lRecs & " records imported."
To see the source code for this issue you must be a subscriber to Code of the Week. If you are a subscriber the source code is available at the following address: http://www.codeoftheweek.com/membersonly/bi/0065.html