Import a folder of dbf files

[sbg2]

I have to do a daily import of 1 to maybe as many as 50 dbf files that contain 1 to X records (usually under 5). Preferably I would like to have the dbf files saved to a directory and imported in one import step or at least scripted without the need for repeated input from the user.

For now I have created a loop script that is set to import. Once a file is imported I drag the imported file to a "completed" folder and then double click the next file to import.... rinse and repeat. The import folder option doesn't work because the files are not .txt files.

Is there any way to do multiple imports without user intervention?

[IdealData]

As you appear to be on v7 then you don't have the option of script variables which can be used to dynamically change a file reference (ie name).

Sounds like 50 different files each with a specific name, and 50 different scripts to support the import facility, but wrap all the individual scripts into one "Control" script.

[sbg2]

The file names are dynamic and done outside of FileMaker. Writing 50 different scripts is not an option as next week/month/year there is always the possibility of more than 50 files.

[sbg2]

How about a plugin? Or even a stand alone utility to do something like this? Maybe Otherwise looks like I may need to revert to MS Access?

[Wim Decorte]

it will certainly require outside scripting (VBscript). Using VBscript you can get a list of DBF files in a folder. Depending on your version of FM you can then set a variable (8) and import each one in a loop, or you can rename each file to a hard-coded filename and have a FM script loop x times (based on the # of files in the folder) and import the hard-coded file name.

[IdealData]

And you still need FMP v8

[sbg2]

it will certainly require outside scripting (VBscript). Using VBscript you can get a list of DBF files in a folder. Depending on your version of FM you can then set a variable (8) and import each one in a loop, or you can rename each file to a hard-coded filename and have a FM script loop x times (based on the # of files in the folder) and import the hard-coded file name.

As IdealData pointed out I am not working with V8 but rather v7.03. I had considered renaming each file to a hard coded name at the time of import but that seemed rather extreme.

Let me see if I have this straight.

1) create a loop in FileMaker which calls a .vbs script file.

2) the .vbs script file renames the next .dbf file in the folder to a hard coded name such as "CurrentImport.dbf".

3) FileMaker imports the file with the Hard Coded name.

4) call another .vbs script to rename the file back to the original name and move the file to a "done" folder. (no idea how to do this)

5) Exit loop when no more files are in folder (no idea how to do this)

[Wim Decorte]

No, only one VBscript. (The VBscript can be created dynamically from inside FM by the way).

VBscript loops through each file in the folder:

- renames the file to the hard-coded name

- calls the FM import script (check the FM activeX objects)

- loop-checks until the FM script status is idle

- continues with next file

[Wim Decorte]

Having said that... I'd probably do it this way:

- fire a vbscript from FM

- vbscript loops through each DBF file in the folder and creates the DBF with the hard-coded name and appends the content of each DBF file, then calls the FM import script.

Should be a lot faster than x imports.

[Wim Decorte]

or you can rename each file to a hard-coded filename and have a FM script loop x times (based on the # of files in the folder) and import the hard-coded file name.

This approach works in FM7...

[sbg2]

Having said that... I'd probably do it this way:

- fire a vbscript from FM

- vbscript loops through each DBF file in the folder and creates the DBF with the hard-coded name and appends the content of each DBF file, then calls the FM import script.

Should be a lot faster than x imports.

If this was a .tab file I would be more inclined to agree. Trying to dissect a .dbf file does not look fun and I wouldn't know where to begin (see dBase V7 file structure for an example of a .dbf 7 file, then again I don't even know what version the dbf files are because they are generated through a third party provider through the web). I would also be paranoid some important information would be left out. Why reinvent the wheel when a program, FileMaker, already imports and exports the information correctly.

That being said, I'm not sure I know enough about VBScript to do something like your other suggestion (though I have looked at and worked with a few files from your web site).

At the same time I have been asking around to see how hard this would be to accomplish using MS Access. The following is my initial post and the suggested changes from a poster. Seems like this might be an easier route but I would really prefer to have one database solution rather than two. I also do not have a copy of Access here at the office but could scrounge up a copy from somewhere if push came to shove.

-------------------------------

Original post

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.

----------------------

REPLY

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]

OK, in an effort to move forward with doing this in FileMaker I tried using the "List All the Files in a Folder" VBScript but get an error. The problem seems to stem from trying to use a folder not directly under the root level.

strComputer = "."

Set objWMIService = GetObject("winmgmts:" _

& "{impersonationLevel=impersonate}!" & strComputer & "rootcimv2")

Set colFiles = objWMIService. _

ExecQuery("Select * from CIM_DataFile where Path = 'Program FilesLV8dbfNEED_Import'")

For Each objFile in colFiles

Wscript.Echo objFile.Name

Next

Edited June 7, 2006 by Guest

[Wim Decorte]

I find it a little strange that you would consider doing this in Access (using Access VBA). This assumes you know enough VBA to make this work. VBscript is largely the same as VBA...

Here's a VBscript that does not use WMI to get a list of files:

Set fso = CreateObject("scripting.filesystemobject")

Set theFolder = fso.GetFolder("c:somewherefolder")

For Each theFile In theFolder.files

theName = thefile.Name

if right(lcase(thename), 4) = ".dbf" then

... do something ...

end if

Next

[sbg2]

I find it a little strange that you would consider doing this in Access (using Access VBA). This assumes you know enough VBA to make this work. VBscript is largely the same as VBA...

Well I know enough to be dangerous. 1 semester of VB.NET at a Community College which I honestly have not used in the last year and a half. This is a poke and hope situation... poke around with some code and hope it works. Much like the way I knew nothing about QuickBooks but somehow have managed to now be able to create QuickBooks invoices directly from FileMaker. The way I managed was asking a lot of questions and getting a lot of help at different forums.

At any rate, the Access VBA looks like the path of least resistance but I would still rather have the solution done in FileMaker.

[sbg2]

I think I have the VBScript ready but how do I:

1) Execute the VBScript from within a Global file in FileMaker.

2) Call the FileMaker script "Import_dbf" from within the VBSciprt.

-------------------------------

Set fso = CreateObject("scripting.filesystemobject")

Set ImportFolder = fso.GetFolder("c:Program FilesLV8dbfNEED_Import")

Set DoneFolder = fso.GetFolder("c:Program FilesLV8dbfDone")

For Each theFile In ImportFolder.files

NameOfFile = thefile.Name

if right(lcase(NameOfFile), 4) = ".dbf" then

Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.MoveFile (ImportFolder & "" & NameOfFile) , (ImportFolder & "" & "Current.dbf")

'* CALL FILEMAKER SCRIPT (FileName = ASN.fm7, ScriptName = "Import_dbf")

objFSO.MoveFile (ImportFolder & "" & "Current.dbf"), (DoneFolder & "" & NameOfFile)

end if

Next

[Wim Decorte]

Check out the demo file I uploaded in a couple of months ago:

http://fmforums.com/forum/showtopic.php?tid/173921/

Or you can use the VBscript plugin from www.grokittools.com and avoid the import altogether.

Have fun.

[sbg2]

Good Lord! My head is spinning, I don't Grok anything. Both GrokIt and your other file both seem to require FM8? I tried the GrokIt Demo but none of the scripts seemed to work. The other file is so overly involved for such a seemingly simple task.

So I found I can call the VBScript with a Send Event step in FileMaker. How do I then call the FileMaker script from within my VBScript. So close and yet so frustrated.

----------------

Set fso = CreateObject("scripting.filesystemobject")

Set ImportFolder = fso.GetFolder("c:Program FilesLV8dbfNEED_Import")

Set DoneFolder = fso.GetFolder("c:Program FilesLV8dbfDone")

'* Set fmApp = CreateObject("FMPRO.application")

For Each theFile In ImportFolder.files

NameOfFile = thefile.Name

if right(lcase(NameOfFile), 4) = ".dbf" then

Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.MoveFile (ImportFolder & "" & NameOfFile) , (ImportFolder & "" & "Current.dbf")

'* CALL FILEMAKER SCRIPT (FileName = ASN.fm7, ScriptName = "Import_dbf")

'* fmApp.Activate

'* fmApp.DoFMScript("Import_dbf")

objFSO.MoveFile (ImportFolder & "" & "Current.dbf"), (DoneFolder & "" & NameOfFile)

end if

Next

[Wim Decorte]

Good Lord! My head is spinning, I don't Grok anything. Both GrokIt and your other file both seem to require FM8?

Yes, but the general principles are the same:

1- store the master VBscript syntax in a field

2- produce the final VBscript syntax by substituting in your own data

3- export the field content to a real VBscript file (a lot easier in 8 since you can use dynamic export paths, use a fixed hard coded path in FM7)

4- execute the vbscript with a Send Event

5- if you want a result returned to FM, create a file and call a FM script to import it

5- delete the VBScript and the temp import file

(The VBscript plugin takes care of steps 3 through 6 automatically)

I tried the GrokIt Demo but none of the scripts seemed to work.

I do believe the demo file mentions that none of the demos work without the plugin The file just shows you what is possible and how.

How do I then call the FileMaker script from within my VBScript. So close and yet so frustrated.

----------------

Set fso = CreateObject("scripting.filesystemobject")

Set ImportFolder = fso.GetFolder("c:Program FilesLV8dbfNEED_Import")

Set DoneFolder = fso.GetFolder("c:Program FilesLV8dbfDone")

'* Set fmApp = CreateObject("FMPRO.application")

For Each theFile In ImportFolder.files

NameOfFile = thefile.Name

if right(lcase(NameOfFile), 4) = ".dbf" then

Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.MoveFile (ImportFolder & "" & NameOfFile) , (ImportFolder & "" & "Current.dbf")

'* CALL FILEMAKER SCRIPT (FileName = ASN.fm7, ScriptName = "Import_dbf")

'* fmApp.Activate

'* fmApp.DoFMScript("Import_dbf")

objFSO.MoveFile (ImportFolder & "" & "Current.dbf"), (DoneFolder & "" & NameOfFile)

end if

Next

Do check the FM ActiveX section in the help file. The "DoFMscript" is not a method of the app, but of a document (the script lives in a FM file)...

[sbg2]

Sorry I just don't get it, maybe I'm thick. Can you please show me how to execute an FM script from a .vbs file with the attached file? It's the simplest of files...

- Export .vbs text held in a global field to c:MyVBS.vbs

- Send Event to execute the created .vbs file

then the step I cant get to work:

- The .vbs file triggers the FM Script "HelloWorld" in the file "c:MyFile.fp7".

I've tried 9,000 ways to Sunday but just can not figure this out.

MyFile.zip

[Wim Decorte]

I think you need to sit back a bit and carefully read FM's ActiveX help section named "ActiveX Automation objects, methods, and properties (Windows)". There is a definite hierarchy in what methods and properties belong to what object. "doFMscript" belongs to the Document object, not the Documents collection.

In your code:

Set fmApp = CreateObject("FMPRO.application")

Set fmDoc = fmApp.Documents

fmDoc.Activate

fmDoc.DoFMScript("HelloWorld")

you set "fmDoc" to the collection of open FM files (documents), you then try to issue the "activate" and "doFMscript" methods on that collection, where you really wanted to target a file (document)



Try this code instead:

Set fmApp = CreateObject("FMPRO.application")

Set fmDocs = fmApp.Documents

For Each fmDoc In fmDocs

	If InStr(LCase(fmDoc.fullname), "myfile.fp7")> 0 Then

		fmDoc.Activate

	    fmDoc.DoFMScript("HelloWorld")

		Exit For

	End If

Next

We get the collection of open documents (fmDocs) and then loop through that collection looking for our file. When we find it, we activate it (bring it to the front and run the script).

Edited June 10, 2006 by Guest

[sbg2]

Thanks for the continued help Wim. I have read through FileMakers help section but most of it went over my head. In the interest of a little self-help I purchased the Wrox book VBScript over the weekend. A little reading and refreshing will hopefully help some of this to sink in.

I the code you provided above but encounter the following error. This error causes FileMaker to crash and in fact I had to reboot the whole system as it bogged the system down and Apps started behaving strangely.

note: the VB script does in fact trigger the FM script. I get a message box with "Hello World" but again it crashes FM.

Edited June 12, 2006 by Guest

[Wim Decorte]

character 6 in line 6 is where the script is getting executed. Not sure what's going on, never had that error in all the years I've been doing VBS integration with FM.

Some random thoughts:

Do you have multiple copies of FM installed? Try running a script that does not show a dialog.

If the problem persists, uninstall FM, reboot and install it again.

[sbg2]

Interesting. After some experimenting it appears a Show Custom Dialog script will produce an error if it is the first script step.

- replaced the Custom Dialog with a Set Field step and everything executed without an error.

- Then added the Custom Dialog after the Set Field step and everything executed fine.

- Lastly removed the Set Field step and placed a blank Comment step before the Custom Dialog and the script executed without errors.

[sbg2]

Everything seems to be working. Thank you very much for walking me through this Wim. Below is the, hopefully, final code.

Set fso = CreateObject("scripting.filesystemobject")

Set ImportFolder = fso.GetFolder("c:Program FilesLV8dbfNEED_Import")

Set DoneFolder = fso.GetFolder("c:Program FilesLV8dbfDone")

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