HELP - Import FoxPro data file into SQL Server

[icemel]

Hi,

I am attempting to import a FoxPro (Office 2003) .dbf file into a SQL Server 2000 database.

I am using the Import wizard, but I am not sure what to select in the drop-down list box "Data Source". All of the things I've tried so far (dBase, FoxPro, SQL Server) do not work.

Has anyone else had success importing FoxPro database files into SQL Server? And how-to.

Thanks much

 

[SQLDenis]

What I do is usually this kind of stuff

--insert into table
SELECT * 
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;
SourceDB=\\server1\blah\blah2\tables\report;
SourceType=DBF',
'SELECT *  FROM report_hist ')

There is also an upsizing wizard in FoxPro another option is DTS

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

Thanks SQLDenis, I haven't tried your script yet... my question is, how should your script look if the data source was:

c:\myDir\myFoxPro.dbf << want to import this to a table in the database

?

Thanks!

 

[SQLDenis]

like this

SELECT *
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;
SourceDB=c:\myDir;
SourceType=DBF',
'SELECT * FROM myFoxPro ')

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

SELECT *
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;
SourceDB=c:\myDir;
SourceType=DBF',
'SELECT * FROM myFoxPro ')

Hi SQLDenis,

I think I am still confused...

My understanding of your script is, it will import ALL .dbf files in the c:\myDir directory? What if I only want to copy ONE specific .dbf file from among a list of .dbf files?

How can I specify the table name in SQL Server that I want the FoxPro data to go to? For example, I want the FoxPro customers.dbf file to go to the customers table in SQL Server.

Also, what is "SELECT * FROM myFoxPro" for?

THANKS

 

[SQLDenis]

On my C drive I have a file named "vendor_codes.dbf"
to import that I use


SELECT *
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;
SourceDB=C:\;
SourceType=DBF',
'SELECT * FROM vendor_codes')

to insert it into the customer table you would do

insert into customers
SELECT *
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;
SourceDB=c:\myDir;
SourceType=DBF',
'SELECT * FROM myFoxPro ')

and then of course you would change myFoxPro and the directory to the correct names




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

Hi, well it appears to be doing something... it's been chugging away for over 2 minutes now... is that normal?

The customer.dbf file is only 363 kb in size!

Thanks

 

[icemel]

I should mention that I have been getting an MS Jet database error when I've tried to import this foxpro db using both Access and SQL Server. Yes, I have the latest msjet40.dll and updates on my xp computer...

 

[SQLDenis]

you are running it from Query Analyzer right?

what happens if you do this?

SELECT top 1 *
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;
SourceDB=c:\myDir;
SourceType=DBF',
'SELECT * FROM customer')

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

Yeah, the original script really seems to hang up the server... it was running for over 6 minutes when I killed it. And hitting the little red "stop" button doesn't even stop the query!

Hmmm

 

[icemel]

Hate to say it, the script killed our server... now I can't connect or do anything. Even EM won't open, not sure how to kill the process if I can't even query the database to get the PID.

But I don't think it's your scipt's fault... something on this end.

 

[SQLDenis]

It's not a production server is it?

use sp_who2 look for PID use DBCC INPUTBUFFER (Spid) if you need to then KILL SPID

The file has to be located on the same box as where the table is located for it to work

That's bizarre, the file is so small it should have been done in less than a second, did you try the select without the insert first?



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

No, not production

I was able to get control of the database again by closing down Query Analyzer

I was hoping your nice little script would work, but I've been having major problems trying to get this FoxPro table into SQLSErver - I'm getting Jet 4.0 errors, table in wrong format error, etc. So it doesn't surprise me the script doesn't work either.

This is an OLD version of FoxPro, over 10 years old.

I really have no idea what the problem is, the import works fine on my co-worker's machine, just not mine.

I am going to have to load it as a flat file by converting it first, I think.

Thanks for your help

 

[SQLDenis]

Could it be Dbase instead of Foxpro?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

I have found a workaround, thanks for your help, SQLDenis

 

[SQLDenis]

So what did you end up doing

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SqueakinSweep]

An interesting note on this point, and the main sticking point on getting Foxpro tables into SQL is simply the way that Foxpro allows empty date fields. Another stumbling block maybe Memo fields. I have found that simply replacing the empty dates in Foxpro with a SQL empty date of say 31/12/1899, enables Foxpro tables straight into SQL using the Wizard. One other consideration maybe to install VFPOLEDB first.



Sweep
...if it works, you know the rest..
Always remember that Google is your friend