Import multiple dbf files and filename? (1 Viewer)

D

DaveK

Guest
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.
 
J

Jon K

Registered User.
Local time
Today, 23:02
Joined
May 22, 2002
Messages
2,209
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.
.
 

Attachments

  • Import dbfs Access 2000.zip
    22.2 KB · Views: 2,818
D

DaveK

Guest
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.
 
S

Smilie

Registered User.
Local time
Today, 15:02
Joined
Jun 22, 2004
Messages
32
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! :D
 
H

haribabu

New member
Local time
Tomorrow, 03:32
Joined
Feb 5, 2009
Messages
1
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
 
T

Tom72Bus

New member
Local time
Today, 23:02
Joined
Jan 22, 2009
Messages
9
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
 
J

JANR

Registered User.
Local time
Tomorrow, 00:02
Joined
Jan 21, 2009
Messages
1,623
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
Click to expand...

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
 
L

lowey

New member
Local time
Today, 15:02
Joined
Jan 13, 2010
Messages
1
great code works perfect, but how would you loop through a directory instead of one folder location?

thanks,

doug
 
GODZILLA

GODZILLA

Registered User.
Local time
Today, 15:02
Joined
Mar 15, 2010
Messages
70
Sorry to Dig this thread up, but does anyone know how to make this work with Text files?
 
A

abenitez77

Registered User.
Local time
Today, 18:02
Joined
Apr 29, 2010
Messages
141
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.
.
Click to expand...
 
A

abenitez77

Registered User.
Local time
Today, 18:02
Joined
Apr 29, 2010
Messages
141
I get the error msg:
"External table is not in the expected format"
 
You must log in or register to reply here.

Similar threads

N
Solved Mass Importing Txt Files
Replies
14
Views
380
ebs17
E
T
Using multiple criteria in DCOUNT function using a comparison
Replies
1
Views
129
June7
June7
M
Importing data from excel with file name
Replies
15
Views
784
Gasman
Gasman
D
Solved BULK IMPORT CSV file to SQL Server
Replies
8
Views
703
CJ_London
C
D
Solved Importing Multiple Excel Files
Replies
16
Views
789
Lightwave
Lightwave

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom