Mass import revisited
Posted on 2003-10-21
I had a question on mass import which was solved. Now however some new problems arrived so here they are.
I needed ACCESS to pick up all the text files in a directory and import them into a table in acces. The format of the files were supposed to be in tab delimited none text qualifier format. However I just found out that at the begining of time there were files that used the tab delimited "(quotationmark) textqualifier and some that used the ,(comma) delimited " text qualifier format. Plus I have just found some filet that came in an .xls (excel spreadsheet) format and those actually contain headers as well. I know how to make the schema files but I do not know if there is a way to tell the code to check for and pick up the different type of files with the different schema files that correspond with their settings.
I use the following code:
Private Sub Command0_Click()
myPath = "C:\Records\" ' Set the path.
myName = Dir(myPath, vbDirectory) ' Retrieve the first entry.
Do While myName <> "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
myfullFile = myPath & myName
If Right(myfullFile, 4) = ".txt" Then 'import it
DoCmd.TransferText acImportDelim, "MI", "Imports", myfullFile, 0
myName = Dir()
This works perfectly with one set of files. But it makes a lot of error tables for the rest. Also I would like to pick the files up by their ascending date stamp not by their name if that is possible at all well.