Importing a text file and adding date to each imported record

[Introfield]

My client wishes to regularly import a "campaign" from Thompson Business Search Pro (.csv). I have two problems...
1. A date field in the destination (Access 2000) table is a required field. The .csv file does not contain such a field (I am happy for current date to be inserted at import).
2. My second problem is that field names differ from bsp to my access tables.
I currently perform the export/import via Excel and use an Excel macro to clean up the data (and insert the required date field) before sending to Access. This works but is not ideal - I'd like to skip the Excel bit.
Also, the BSP campaign occasionally produces an irregular number of fields (presumably when there is data in that field). I can see how to overcome most issues with the specifications option on the "get external data" dialogs but can anyone please help with these two?

 

[Henaniy]

Hey Instructor,

First, do a manual import of text file. On the advanced tab, import the names of the fields you use in your access tables. Then save it.

When you want to call in code, you refer to the import specifications that you saved.

(i.e. DoCmd.TransferText acImportDelim, "New Import Specification","", varName, True, ""

varName is name of *.txt or *.csv file.