We help IT Professionals succeed at work.

import data into SQL 2005 from a *.dbf file?

bigbug7
bigbug7 asked
on
5,497 Views
Last Modified: 2010-04-21
I have an empmst.dbf file which contains data of my employee. this is a foxpro file. i need to export the file to my MS SQL 2005 server...how do i go about?
Comment
Watch Question

Ok

From SQL server ?

 If the sql server can see the drive where the *.dbf  is present then you access it with the Openrowset(). You will need the latest VFP Oledb driver from http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en

Something like

Select * from sqltable as SQLT inner join Openrowset('VfpOLEDB.1','<full path to the dbf>';'';'',select * from empmst.dbf) as VFPT on SQLT.field = VFPT.field

Or If you want to just get the records in SQL Server

Select * from  Openrowset('VfpOLEDB.1','<full path to the dbf>';'';'',select * from empmst.dbf)


Suhashegde
If you are doing this from Foxpro
3 ways

1) openrowset method

han = sqlconnect('A')
Or
Han = sqlstringconnect("valid connection string")
lcsql = [Select * from sqltable as SQLT inner join Openrowset('VfpOLEDB.1','<full path to the dbf>';'';'',select * from empmst.dbf) as VFPT on SQLT.field = VFPT.field]
sqlexec(han,m.lcsql)

2)
Direct Update to the Sql Server table with scan .. Insert endscan or Updatable cursors

Scan .. insert... endscan
Han = sqlstringconnect("valid connection string")
sqlexec(han,"Create table VFPTABLE (f1 char(10),....") && sql server create table syntax
Select empmst.dbf
scan
sqlexec(han,"insert into VFPTABLE(Columns) value (?empmst.field,?empmst.field2 ..) && not the ? prefix as the parameter

endscan

Cursorsetprop and tableupdae()

Han = sqlstringconnect("valid connection string")
sqlexec(han,"Create table VFPTABLE (f1 char(10),....") && sql server create table syntax
sqlexec(han,"select * from VFPTABLE","crs")
cursorsetprop("Tables",Remotetable,localtable)
cursorsetprop("where clause")
cursorsetprop("Keyfield",...)
cursorsetprop("Updatefieldlist",field list)
cursorsetprop("sendupdates",.t.)

select crs
append from empmst

tableupdate(.t.,.t.,'crs')

Look more on cursorsetprop to make the cursor updatable

once the cursor crs is updated then You can use the VFPTABLE in sql server like

sqlexec(han,"select * from SQlservertable as SQLT inner join VFPtable on SQLt.field = VFPTABLE.field ....")

3) BCP
Look more on BCP for Bulk Copy


Suhashegde

Author

Commented:
is there no way i can do it from SQL server management studio..through the export or import wizard?

Author

Commented:
when i executed this statement
select * from Openrowset('VfpOLEDB.1','d:\krish\empmst.dbf';'';'',select * from empmst.dbf)
it gave me following error

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
select * from Openrowset('VfpOLEDB.1','d:\krish';'';'','select * from empmst.dbf')
If it involves a free table


Author

Commented:
yes as per ur link i have download the driver and when i tried the command it gave me the following error
Cannot initialize the data source object of OLE DB provider "VfpOLEDB.1" for linked server "(null)".

Author

Commented:
along with the above msg it is also saying the following

OLE DB provider "VfpOLEDB.1" for linked server "(null)" returned message "Cannot open file d:\krish\ppmsmast.dbc.".
What did you try ?
Is your dbc in use ? by any other appication ?
And if it a Foxpro table, Are u sure ?

Suhashegde
OLE DB provider "VfpOLEDB.1" for linked server "(null)" returned message "Cannot open file d:\krish\ppmsmast.dbc.".

that error means that there is a DBC -- a database container.

So it will be

select * from Openrowset('VfpOLEDB.1','d:\krish\ppmsmast.dbc';'';'','select * from empmst.dbf') as VFPT

Try and let me know

Thanx
Suhashegde



BTW you need to enable the Openrowset  Sp_dboptions which is disabled by default.

Author

Commented:
thanks a lot dude..i had the dbc file missing..it was the wrong file

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.