Import *.dbf file into SQL server
ta...@cc.memphis.edu
I need to import a *.dbf file (Foxpro database file) into my SQL server
everyday, has anyone had any experience with it, what's the best way to perform
this process? Thanks, tina.
Terry Carmen
Use ODBC and let FoxPro do it.
If you've got FoxPro version 3 (maybe 2.6, I didn't check) or newer,
it will do ODBC, and you can let it insert whatever records you want.
Otherwise, you can convert the FoxPro DBF files to delimited files,
which you can import with BCP, but it's more work.
Terry
--------------------
Terry Carmen
Meadowbrook Software
tpca...@servtech.com
Pete Nelson
On 25 Jan 97 12:37:49 -0500, ta...@cc.memphis.edu wrote:
>I need to import a *.dbf file (Foxpro database file) into my SQL server
>everyday, has anyone had any experience with it, what's the best way to perform
>this process? Thanks, tina.
Our company is working on setting up an SQL server to mirror our MLS
real estate system. We import several DBFs each hour. Let me (attempt
to) briefly run down how we do that.
Each hour, a communications program logs into our MLS system and
downloads a file with any new or changed records. It's clunky as the
only sort of connection you can get is via a 19.2k serial connection,
but it works. After downloading the file, it is processed in Foxpro and
the records are put into a DBF file.
FYI, we're using Foxpro v.26 for DOS. We also have the Foxpro
Connectivity Kit, which allows us to send SQL queries and retrieve data
from the SQL server.
All of the tables on our server are in the same structure as our DBF
files. One of the first things I did was to make a DBF of the structure
of each DBF file we're using (via COPY STRUCTURE EXTENDED). Then, I
wrote a small Foxpro program to scan the structure file and generated a
SQL statement to create the tables. This ensures the SQL tables are
going to match my DBF files.
Importing the DBF files into the SQL server - I wrote another Foxpro
program that uses the DBF with the real estate records, and the
structure DBF. It reads the structure DBF for the field name, then
reads the data from the other DBF file and generates an INSERT statement
for each field. Foxpro looks at the fields in the structure DBF and
formats the insert statement accordingly, with quotes or no quotes,
decimal places, etc.
You need to have the Connectivity Kit to do this directly from Foxpro.
Otherwise, you could have Foxpro generate a file for ISQL and then do it
through ISQL. We do all of this through DOS under Win95. It was much
easier to automate and control in DOS
You can also use MS Access to do stuff like this. I've linked an SQL
table and a Foxpro table into an Access database, and then I can run SQL
macros interact with each table. Very slick!
Drop me a note if you'd like more info!
=====================================================================
Pete Nelson <wea...@ecis.com> "My knees are cold."
http://www.ecis.com/~weasel - Home of the Tech's Support Page, A-10
Warthog Territory, Glocks in Movies & TV, Weasel's wcCODE Archive
=====================================================================
Je...@corel.com
XP.dll comes with the SQL Developers Kit (Probably not the official
name...but it's that single user version of SQL for developers...) and
includes an extended stored procedure which allows you to scan dbf files
directly from SQL. (See SQL documentation: xp_scan_xbase) The
performance is very good, but it only supports local dbf's and may not
be compatible with foxpro's memo fields as it "officially" supports
dBASE III and dBASE IV. (We have used it on foxpro and clipper tables,
without memo fields, and had no problems).
Good Luck,
Je...@corel.ca
MIS, Corel Corporation