Importing FoxPro 7.0 into SQL Server 2000 - HOW DO I DO THIS ?

Question

Importing FoxPro 7.0 into SQL Server 2000 - HOW DO I DO THIS ?

jim Walker-138179

Old Hand

Points: 304
More actions
April 14, 2004 at 3:16 pm

#60094

I have been tring to import FoxPro files into a SQL Server 2000 database. I was instructed to use the VFP OLE DB driver but when I select the .DBF file to import and go through the steps. I don't get any of the Source fields or the destination tables. Do you know what could cause this or know a better way to import directly from a Visual FoxPro 7 file directly into SQL Server ?

Jim

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Peter P SSCrazy Points: 2777 More actions · Answer 1

I've been challenged by this exact problem. The VFP OLE-DB Driver does seem a bit janky. I've had some success using the dBase 5 driver. Try that.

jim Walker-138179 Old Hand Points: 304 More actions · Answer 2

Yea..I have tried that and I get an error stating something about the

JET DATABASE ENGINE error

ERRORPENSETROW something....

Is there a special way to use the dBase 5 driver to get this to import ?

Peter P SSCrazy Points: 2777 More actions · Answer 3

Peter P

SSCrazy

Points: 2777

April 14, 2004 at 4:12 pm

#502826

Try installing the latest version of MDAC.

Arden Weiss Right there with Babe Points: 749 More actions · Answer 4

I use the 2.5 or 2.6 ODBC driver with VFP 6 and above without problems -- the process I use is to scan/endscan the DBF file one record at a time, scatter the data to a mVariables and then use a SQLEXEC INSERT statement to do the post to the SQL Server, Oracle, MySQL, Access, etc -- runs a bit slow but is accurate -- also can do data clean up in the same process.

lnResult = SQLEXEC(hConn, ;

"INSERT INTO COMPANY " + ;

"(DIV_NUM, COMPANY, ELLIE_N, " + ;

" NAME, DIV_NAME ) " + ;

"VALUES (" + ;

" ?mDIV_NUM, ?mCOMPANY, ?mELLIE_N, " + ;

" ?mNAME, ?mDIV_NAME)" )

Arden

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 5

We just went through this same thing. When you set up your VFP DSN, you need to select the option of Database Type: Free table directory. Also make sure you point to the directory that contains your tables.Then when you go to import into SQL Server and select your DSN that you set up, it will show the tables that you have. I just tried it again to make sure I had it right and it worked.

John

Arden Weiss Right there with Babe Points: 749 More actions · Answer 6

Arden Weiss

Right there with Babe

Points: 749

April 15, 2004 at 8:48 am

#502958

Agree with laker 42 -- that's what I also do...

Arden

jim Walker-138179 Old Hand Points: 304 More actions · Answer 7

Laker -

I do that currently with the OLE DB driver ... what driver are you using ( dBase 5, Visual FoxPro OLE DB, etc..) ? Also what type of DSN are you setting up ( file, system,etc...) ? If you can maybe walk me through setting this DSN up... so I know its correct...

Thanks

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 8

I created a user DSN. Here are the steps I followed to create the DSN:

1. Open up ODBC Data Source Administrator and click on Add.

2. Scroll down to the Microsoft FoxPro VFP Driver (*.dbf). Click Finish.

3. Type in the name for the DSN.

4. In the database type box, select Free Table Directory.

5. Then click the browse button and navigate to the directory that contains your dbf files.

6. Then click ok.

Your dsn should be all setup now. Now you should be able to go to SQL Server and import the data. Just select the DSN that you just created.

John

Ron Sissons SSC Enthusiast Points: 115 More actions · Answer 9

I setup a User ODBC and Then went to Sql Enterprise Manager to import the file. It scans trough about 16,000 records then says I have an insert error.

Column9, Data Overflow, invalid character in value for cast specification.

If i import with sql 7 and then from 7 to 2000 there is no problem with the data, only if I try to import straight to 2000.

This is a dbf file from output from another application.

Any suggestions?

Peter P SSCrazy Points: 2777 More actions · Answer 10

You will likely have to look at the data from your source before import to see why there is errant data. But first you might try importing the data into a staging table that is built based on the source. In DTS you can do this quickly by clicking the CREATE button on the Destination Tab of the Transform Data Task and rebuilding your transforms. (You may want to create a new Package for this).

Also, try increasing the max error count property. In the Transform Data Task properties, Options Tab, Under the Data Movement section, increase the Max Error Count. Make sure you have logging enabled on the package. And that the Fail Package on First error checkbox on the Package Properties is not checked. This will get the data in without failing the package, but you will be missing some of the rows that are causing you problems.

Also, make sure that you are checking the exact error that is thrown when the package does fail. you can do this by double-clicking the error in the DTS package. Many times DTS will tell you exactly why your package is failing.

Arden Weiss Right there with Babe Points: 749 More actions · Answer 11

When this happens to me it generally has meant that one of the fields in SQL is shorter than one of the fields in the DBF -- but that is only a first-guess possibility. Peter's advice will get you at the real cause.

Arden

Anthony Ordner Grasshopper Points: 14 More actions · Answer 12

Check the table you are trying to insert data into. I would guess it is a SMALLDATETIME type that is causing the problems. Change all SMALLDATETIME to DATETIME and try the import again.

good luck

tony