We have a Windows application that stores data in DBF. I need to connect to a certain table a_sname.dbf
and extract the SN_ACCOUNT
, SN_CURRBAL
and SN_CRLIM
columns to a text file to be imported by another application.
The text file ideally needs to look like.
SN_ACCOUNT SN_CURRBAL SN_CRLIM
10 100 1000
20 200 2000
The above is an example only.
At the moment I am looking to at least get connected to the DBF.
I have BASH scripting with Oracle SQL knowledge but the Windows side is all new to me. I have the drive mapped to Z:\
.
Update 1: Here is the script that I'm testing.
Option Explicit
Dim sDir : sDir = "Z:\"
Dim sCS : sCS = Join(Array( _
"Provider=Microsoft.Jet.OLEDB.4.0" _
, "Data Source=" & sDir _
, "Extended Properties=""dBASE IV;""" _
), ";")
Dim oCN : Set oCN = CreateObject("ADODB.CONNECTION")
oCN.Open sCS
Dim oRS : Set oRS = oCN.Execute("SELECT SN_ACCOUNT,SN_CURRBAL,SN_CRLIM FROM a_sname.dbf")
Do Until oRS.EOF
WScript.Echo oRS.Fields(0).Name, oRS.Fields(0).Value
oRS.MoveNext
Loop
oCN.Close
I am getting the error message Microsoft JET Database Engine: External table is not in the expected format.
.
Update 2: Here is a screenshot of the table information of a_sname.dbf
.
Update 3: It works! Here is the query that I am using.
SELECT SN_ACCOUNT,SN_CURRBAL,SN_CRLIM FROM a_sname.dbf
Here is the echo that I am using.
WScript.Echo oRS.Fields(0).Value, oRS.Fields(1).Value, oRS.Fields(2).Value
.
Is it possible to pipe all this to a text file and have the columns spaced more evenly?
According to the source your connection string for the ADODB.CONNECTION should look something like this:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\FileServer01\Server VFTP Dynamic\Data;
Extended Properties=dBASE IV;User ID=Admin;Password=;
Your first (test) query could be
SELECT * FROM [a_sname.dbf]
In case of trouble, I'd
\\FileServer01\Server VFTP Dynamic
to a drive letter, change the connection string accordingly and try againAdded:
Some tested code to increase your confidence:
Option Explicit
Dim sDir : sDir = "\\gent\eh\...etc...\testdata\dbf"
Dim sCS : sCS = Join(Array( _
"Provider=Microsoft.Jet.OLEDB.4.0" _
, "Data Source=" & sDir _
, "Extended Properties=""dBASE IV;""" _
), ";")
Dim oCN : Set oCN = CreateObject("ADODB.CONNECTION")
oCN.Open sCS
Dim oRS : Set oRS = oCN.Execute("SELECT TOP 2 * FROM [tblAbles]")
Do Until oRS.EOF
WScript.Echo oRS.Fields(0).Name, oRS.Fields(0).Value
oRS.MoveNext
Loop
oCN.Close
output:
cscript 22934726.vbs
RTATID 2
RTATID 3
Troubleshooting I:
sDir
contains a file spec ("Z:\a_sname.dbf"), not a folder/directory specWScript.Echo CreateObject("ADODB.Connection").Version
Troubleshooting II:
Troubleshooting III:
see here for getting the version from the file header
use a GUI (e.g. Data Sources) to find possible values for "dBase XX"
Troubleshooting (not dBase!) IV:
According to the source, you can/must(?) use a VFP specific connection string: maybe
Provider=vfpoledb;Data Source=C:\MyDataDirectory\;Collating Sequence=general;
Perhaps you need to download a provider DLL (be carefull wrt versions!)