Batch import records from .dbf files

[sbg2]

Want to automate the import of 1 to many .dbf files in a single shot (all the files have the same fields). found the following code at Microsofts site but this is not exactly what I'm looking for. I dont want to create new tables I just want to import all the records from multiple files and then export to one large .dbf file. Any help appreciated.

Private Sub Command0_Click()
Dim InputDir, ImportFile As String, tblName As String
Dim InputMsg as String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
InputDir = InputBox(InputMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
' Use the import file name without its extension as the table
' name.
tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))
' Change FoxPro 3.0 on the next line to the type of file you
' want to import.
DoCmd.TransferDatabase acImport, "FoxPro 3.0", InputDir, _
acTable, ImportFile, tblName
ImportFile = Dir
Loop
End Sub

If its possible to then move the imported .dbf files to a "done" folder that would be great also.

 

[namliam]

Instead of using "tblName" in the transferdatabase command
DoCmd.TransferDatabase acImport, "FoxPro 3.0", InputDir, _
acTable, ImportFile, tblName

Give a prefixed table name
DoCmd.TransferDatabase acImport, "FoxPro 3.0", InputDir, _
acTable, ImportFile, "tblDBFs"

Then finish up by exporting it again....

 

[sbg2]

I tried what you suggested. While this does import all the files it creates a new Table for each imported file. I want to import all the files into a previously created table named ASN Label Data.

DoCmd.TransferDatabase acImport, "dBase IV", InputDir, _
acTable, ImportFile, "ASN Label Data"

 

[namliam]

There should only be the "ASN Label Data" table

Tip: DO NOT use spaces in table names.

There may be tables per file with "Import errors" but if that is happening then there is something going wrong with the import....

 

[sbg2]

OK, I changed the table name to "ASN_Label_Data" but still get a new table for each file. Why should I avoid spaces in the table name? Does this apply to other things like Forms and Reports? No Import Errors reported. I can't even do a manual import into an existing table. Everytime I get a new table???

 

[namliam]

Spaces, well yes it revers to everything in your database. Access is the only DB system that supports spaces. If you ever need to use anything else you will have to kick that habbit anyway.
Also it forces you to use stuff like [my field name] where as if you dont have spaces you mostly dont need them [] either...

I dont work with DBF's so maybe its just not possible with DBFs?
Have you tried using linked tables and importing it using an append query?

 

[sbg2]

Spaces, right now we are using FileMaker for our DB and I am able to use spaces in table names and import new tables from files that have a space in the file name.

Linked Tables. Linked tables do not give me the option to link to an existing table so I don’t see how that gains me anything. The only thing I can see at this moment is to write a script that imports each file creating a new table in Access for each .dbf file and then export to a file type that can be imported into an existing Access table (such as .tab).

I guess I might be able to:

- import each .dbf to a new table and store each table name in an array
- move each .dbf file to a “Done” Folder
- loop through all the tables in the array and export the data to a folder (in .tab format) then delete the table.
- do a For Each theFile In MyTabFolder.files to import the files into my master table in my company DB.
- delete each file from the MyTabFolder
- Export to overwrite the existing master .dbf file
- Delete all the records in the import table.

That seems an awfully long way to go for a simple import though. I have solved the problem with some VBScript in my current DB solution but I was hoping in the next year or so to eventually move to Access… not so sure now. I hate to say it but I think it was easier with FileMaker, code listed below.

Set fso = CreateObject("scripting.filesystemobject")
Set ImportFolder = fso.GetFolder("c:\Program Files\LV8\dbf\NEED_Import")
Set DoneFolder = fso.GetFolder("c:\Program Files\LV8\dbf\Done")
Set fmApp = CreateObject("FMPRO.application")
Set fmDocs = fmApp.Documents

For Each theFile In ImportFolder.files
NameOfFile = thefile.Name
if right(lcase(NameOfFile), 4) = ".dbf" then
' Rename active file to "Current.dbf"
fso.MoveFile (ImportFolder & "\" & NameOfFile) , (ImportFolder & "\" & "Current.dbf")

'* CALL FILEMAKER SCRIPT (FileName = ASN.fm7, ScriptName = "Import_dbf")
For Each fmDoc In fmDocs
If InStr(LCase(fmDoc.fullname), "asn.fp7")> 0 Then
fmDoc.Activate
fmDoc.DoFMScript("Import_dbf")
Exit For
End If
Next

' Move file to Done folder and set to original file name.
fso.MoveFile (ImportFolder & "\" & "Current.dbf"), (DoneFolder & "\" & NameOfFile)
End If
Next

' Call Rewrite Key script from FM
For Each fmDoc In fmDocs
If InStr(LCase(fmDoc.fullname), "asn.fp7")> 0 Then
fmDoc.Activate
fmDoc.DoFMScript("Rewrite Key")
Exit For
End If
Next

 

[namliam]

Spaces, right now we are using FileMaker for our DB and I am able to use spaces in table names and import new tables from files that have a space in the file name.

Aha, but bigger databases like SQL and Oracle dont...

Linked Tables. Linked tables do not give me the option to link to an existing table so I don’t see how that gains me anything. The only thing I can see at this moment is to write a script that imports each file creating a new table in Access for each .dbf file and then export to a file type that can be imported into an existing Access table (such as .tab).

Well if you have the DBF linked, they you can do an APPEND query of that linked table to feed into a permenant table....

I guess I might be able to:

- import each .dbf to a new table and store each table name in an array
- move each .dbf file to a “Done” Folder
- loop through all the tables in the array and export the data to a folder (in .tab format) then delete the table.
- do a For Each theFile In MyTabFolder.files to import the files into my master table in my company DB.
- delete each file from the MyTabFolder
- Export to overwrite the existing master .dbf file
- Delete all the records in the import table.

Import => Export => Import... Why export again? You allready have it in your database...

 

[sbg2]

Import => Export => Import... Why export again? You allready have it in your database...

The final exported data will be used by a seperate application which requires the data in a .dbf file.