Enter our monthly contest at http://www.codeoftheweek.com/contest.html for the chance to win FREE software for your development needs.
Download an evaluation of PowerFindPro at http://www.codeoftheweek.com/powerfindpro
Back issues are now available in Spanish for subscribers of Code of the Week at http://www.codeoftheweek.com/membersonly (free trial members will need to subscribe to access them).
Check out http://www.codeoftheweek.com/links/specialoffer.html for an exciting subscription offer. You do not want to miss this one.
This issue is the second part to last week's issue. It 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 questions@codeoftheweek.com.
The complete source code appears near the end of this issue. Be sure not to miss it!
Last week we discussed how to import any CSV file. The one problem with last week's routine is that if the first record in the CSV file did not contain field names, DAO will just assign the names F1, F2, F3, F4, etc. This week we show how to rename any columns you want after performing the import. We will assume you have read the content of issue #65. If you did not receive it, you can view it at http://www.codeoftheweek.com/issues
Public Sub RenameColumn(sOldColumnName As String, sNewColumnName As String) Public Function ImportDataAndRename() As Long
RenameColumn allows you to specify a column from the import file (sOldColumnName) and the name (sNewColumnName) that this column should be after importing the data. The ImportDataAndRename function will use this information to change the column names in the imported table. You will need to call RenameColumn once for each column you would like to rename. Keep in mind that if your CSV file does not contain the field names as the first row, the import table will have columns called F1, F2, F3, F4, and so on starting with the first column. See the sample code for details on using RenameColumn.
ImportDataAndRename is a wrapper around the ImportData routine discussed last week. It will first call the ImportData routine and then process the OldColumnNames and NewColumnNames collections.
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.
ImportDataAndRename will return the number of records that it imported. Error 457 will be returned if you try to call RenameColumn with the same column name more than once. Error 3265 will be returned if you call ImportDataAndRename and specify an non-existent column name as the sOldColumnName parameter in RenameColumn (to put another way, if you try to rename a column that 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"
' will rename column F1 to email, F11 to firstname, and F2 to lastname
Import.RenameColumn "F1", "email"
Import.RenameColumn "F11", "firstname"
Import.RenameColumn "F2", "lastname" ' will return err=457 if the names were
' already used.
lRecs = import.ImportDataAndRename ' will return err=3265 if you specified
' an illegal (doesn't exist) old column name
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/0066.html