Import multiple dbf files and filename?

DaveK · Feb 6, 2005

Hi,

I have a question regarding import of multiple dbf files.

These files all have different names but will always be found in the same location.

C:\Documents and Settings\computer\Desktop\F FILES

I have little VBA knowledge and am wondering if I can import ALL these files into an existing Access table FORMGUIDE via a macro?

This is what I have so far to import one file:

Option Compare Database

'------------------------------------------------------------
' Import
'
'------------------------------------------------------------
Function Import()
On Error GoTo Import_Err

DoCmd.TransferDatabase acImport, "dBase 5.0", "C:\Documents and Settings\computer\Desktop\F FILES\", acTable, "ASC0128F.DBF", "tblFORMGUIDE", False

Import_Exit:
Exit Function

Import_Err:
MsgBox Error$
Resume Import_Exit

End Function

A number of problems:

1. It does not import directly into the existing table, instead it creates a new table tblFORMGUIDE1, I'd like to import directly into the existing table without using append or similar.

2. I cannot get it to import ALL files in the folder.

3. I'd like to import the first 7 characters of the filename into a field (KEY).

I've already read the Knowledgebase and found the Batch Import, but this does not help as you have to manually enter the name of each file and I have over 2,500 individual dbf files.

Any help or examples would be greatly appreciated.

Thanks.

Jon K · Feb 6, 2005

To append records from dBase dbf files into a table, you can run an Insert Into statement with an In Clause. (See Access help file for the syntax of In Clause.)

I have attached a sample database and three dBase 5.0 files converted from the NorthWind Products table (all the field names were truncated at the 11th characters during export.)

You can click on the command button on the form to append the records. The code used is in the On Click event of the command button:-

Code:

Private Sub cmdImport_Click()
On Error GoTo ErrHandler
   
   Dim oFSystem As Object
   Dim oFolder As Object
   Dim oFile As Object
   Dim sFolderPath As String
   Dim SQL As String
   Dim i As Integer
    
   sFolderPath = [b]"C:\My Documents\"[/b]
   
   Set oFSystem = CreateObject("Scripting.FileSystemObject")
   Set oFolder = oFSystem.GetFolder(sFolderPath)
  
   For Each oFile In oFolder.files
     If Right(oFile.Name, 4) = ".dbf" Then
       SQL = "Insert into [tblFORMGUIDE]" _
           & " Select """ & Left(oFile.Name, 7) & """ as [Key],*" _
           & " from " & Left(oFile.Name, Len(oFile.Name) - 4) _
           & " IN """ & sFolderPath & """ ""dBASE 5.0;"""
       
       DoCmd.SetWarnings False
       DoCmd.RunSQL SQL
       DoCmd.SetWarnings True
       i = i + 1
     End If
   Next
   
   MsgBox i & " dbf files were imported."
   Exit Sub
   
ErrHandler:
   MsgBox Err.Description
End Sub

I used a file system object to get the file names from C:\My Documents\. It's also possible to get the file names using the Dir() function.

It seems Access can only recognize the DOS file name of 8.3 for dBASE files. It failed to import when the file names are longer than 8 characters.
.

DaveK · Feb 6, 2005

Thankyou Jon K very much!!!

You really solved a difficult problem for me with clear instructions and a great example to follow.

I'm extremely thankful for your help.

Regards,
Dave.

Smilie · Mar 23, 2005

Thank you, Thank you, Thank you!

I have been struggling to figure out how to import or transfer a database into my current database. But I also needed my user to specify the file name. I was able to manipulate this code to work just perfect!

Thanks again!

haribabu · Feb 5, 2009

Dear Jon K,

It was really a great show by you.

I was just searching on net, luckily I could get it. We are extremely thankful to you.

Keep up the good job!

Hari Babu

Tom72Bus · Feb 5, 2009

Coincidentally that's exactly what I was after! any tips on how to convert it to excel files, i keep getting "could not find installable ISAM"?

Cheers

Tom

JANR · Feb 5, 2009

Coincidentally that's exactly what I was after! any tips on how to convert it to excel files, i keep getting "could not find installable ISAM"?

Cheers

Tom

One quick way is to open the dbf-file in excel ans "Save as" excel.

You could automate it in VBA, here is one way to do it:

Code:

Option Compare Database
Option Explicit
Public Function sExcel()
Dim oApp As Object
Dim oExt As String
   On Error GoTo sExcel_Error
oExt = "C:\MYFOLDER\"    'the location of dbf-file
'Debug.Print oExt
Set oApp = CreateObject("Excel.Application")
oApp.Visible = False
oApp.UserControl = False
    oApp.Workbooks.Open FileName:="" & oExt & "" & "yourDBF.DBF"
    oApp.ActiveWorkbook.SaveAs FileName:="" & oExt & "" & "yourExcel.xls", FileFormat:= _
        43, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
    oApp.ActiveWindow.Close
    
    oApp.Quit
    Set oApp = Nothing
    
   On Error GoTo 0
   Exit Function
sExcel_Error:
    oApp.Quit
    Set oApp = Nothing
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure sExcel of Module excelKonvCLS"
End Function

JR

lowey · Jan 13, 2010

great code works perfect, but how would you loop through a directory instead of one folder location?

thanks,

doug

GODZILLA · Apr 21, 2010

Sorry to Dig this thread up, but does anyone know how to make this work with Text files?

abenitez77 · Jan 21, 2011

How do I make this work for dbf files (foxpro 8)?

Jon K said:
To append records from dBase dbf files into a table, you can run an Insert Into statement with an In Clause. (See Access help file for the syntax of In Clause.)

I have attached a sample database and three dBase 5.0 files converted from the NorthWind Products table (all the field names were truncated at the 11th characters during export.)

You can click on the command button on the form to append the records. The code used is in the On Click event of the command button:-

Code:

Private Sub cmdImport_Click() On Error GoTo ErrHandler Dim oFSystem As Object Dim oFolder As Object Dim oFile As Object Dim sFolderPath As String Dim SQL As String Dim i As Integer sFolderPath = [B]"C:\My Documents\"[/B] Set oFSystem = CreateObject("Scripting.FileSystemObject") Set oFolder = oFSystem.GetFolder(sFolderPath) For Each oFile In oFolder.files If Right(oFile.Name, 4) = ".dbf" Then SQL = "Insert into [tblFORMGUIDE]" _ & " Select """ & Left(oFile.Name, 7) & """ as [Key],*" _ & " from " & Left(oFile.Name, Len(oFile.Name) - 4) _ & " IN """ & sFolderPath & """ ""dBASE 5.0;""" DoCmd.SetWarnings False DoCmd.RunSQL SQL DoCmd.SetWarnings True i = i + 1 End If Next MsgBox i & " dbf files were imported." Exit Sub ErrHandler: MsgBox Err.Description End Sub

I used a file system object to get the file names from C:\My Documents\. It's also possible to get the file names using the Dir() function.

It seems Access can only recognize the DOS file name of 8.3 for dBASE files. It failed to import when the file names are longer than 8 characters.
.

abenitez77 · Jan 21, 2011

I get the error msg:
"External table is not in the expected format"

Import multiple dbf files and filename?

DaveK

Guest

Jon K

Registered User.

Attachments

DaveK

Guest

Smilie

Registered User.

haribabu

New member

Tom72Bus

New member

JANR

Registered User.

lowey

New member

GODZILLA

Registered User.

abenitez77

Registered User.

abenitez77

Registered User.

Similar threads

Users who are viewing this thread