Question about automating the importing process.

[kjspear]

Hello,

I would like to automate importing a .CSV file into Access. The .CSV file is exported then imported to create a new table in Access 2003. What I'm trying to do is use Form to where the user clicks on a button and that opens up the file dialog box so that the user can select where the file is (ie My Documents folder.) Once the file is selected, it is then imported something like transfer to text. The queries and the fields are already set up as I'm doing it manually. Also, if possible I can skip the fields I do not need. Just like the way you do it in the Import Wizard. Any assistance will be apreciated as that is the last thing I need to do to complete this at work.

Thanks
Chelsea

 

[oharab]

Hi Chelsea,
There are a number of ways of getting the file name for selecting a file, this is probably one of the easiest to do. Just drop the code into a module in your database, then use the example code

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.txt")

strInputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select an input file...", _
                Flags:=ahtOFN_HIDEREADONLY)

You can then pass the file name you've got to the TransferText command

DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:="specName", TableName:="TableName", FileName:=strInputFileName, HasFieldNames:=True

SpecificationName is the name of an import specification. To get this, import the file manually using the import wizard, then before you finish the wizard, click Advanced, Save As... and give it a relevant name.

This assumes that your import files are going to be the same format each time. If you are trying to import different 'shaped' data files, then let me know and we can investigate.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.