none
How to import data into SQL 2005 from dbf file

    Question

  •  

    Hi all,

     

    I need to migrate data from dbf file to SQL 2005. For this its enough for me if I keep all the data (present in the dbf file) into a table in sql 2005.

     

    can you please help on this...

     

     

    Thanks,

    Suresh 

     

    Wednesday, September 10, 2008 12:00 PM

Answers

  • Hi,

    There are 2-3 ways you can do that.

    1. Create a Linked Server using Microsoft.Jet.OLEDB.4.0 and then query dbf files.

    2. use directly script like

    Code Snippet

     

    insert into table_name

    select * from OPENROWSET

    ('MICROSOFT.JET.OLEDB.4.0','dBase IV;HDR=NO;IMEX=2;

    DATABASE=PAth_of_folder','select * from File_name.dbf')

     

     

    Before running above query execute this

    Code Snippet

    exec sp_configure 'Ad Hoc Distributed Queries',1

    go

    RECONFIGURE

    GO

     

     

     

    3. With SSIS packages also you can Import the data into Sql server by using Microsoft.Jet.OLEDB.4.0

     

     

    Wednesday, September 10, 2008 1:21 PM
    Moderator

All replies

  • Hi,

    There are 2-3 ways you can do that.

    1. Create a Linked Server using Microsoft.Jet.OLEDB.4.0 and then query dbf files.

    2. use directly script like

    Code Snippet

     

    insert into table_name

    select * from OPENROWSET

    ('MICROSOFT.JET.OLEDB.4.0','dBase IV;HDR=NO;IMEX=2;

    DATABASE=PAth_of_folder','select * from File_name.dbf')

     

     

    Before running above query execute this

    Code Snippet

    exec sp_configure 'Ad Hoc Distributed Queries',1

    go

    RECONFIGURE

    GO

     

     

     

    3. With SSIS packages also you can Import the data into Sql server by using Microsoft.Jet.OLEDB.4.0

     

     

    Wednesday, September 10, 2008 1:21 PM
    Moderator
  • Suresh,

     

    It depends how complex you need this to be.

     

    From Access you can create a linked table to the SQL server with the same structure as your access table and simply cut and paste the rows between the two!

     

    From SQL you can open SQL Server Management studio and right click the database you want the data in and select Tasks/Import Data and follow the wizard to import the table and structure directly from the Access DB!

     

    Is this what you were after?

     

    Regards

     

    Rupert

     

    Friday, September 12, 2008 11:55 AM
  • Hi Rupert,

    That will only apply if, those dbf files are of Access.

     

    Friday, September 12, 2008 12:05 PM
    Moderator
  • Mangal,

     

    this process also applies to foxpro or other dbf files.

     

    I do a lot of work on assessing data for imports and data conversion.

    Rather than writing programs for the conversion it is often a lot easier to simply use existing tools to open/save as or use the import tools.

     

    If you want a quick and easy way then let me know the database format and I can tell you how to import the data into SQL - unless of course you're intention is to write a program to consistently import data then its the long way round ;-)

     

    Regards

     

    Rupert

     

    Monday, September 15, 2008 11:05 AM
  • Hi Rupert

    Will surely love to do with the click of mouse.

    I had some .dbf files generated by some dos base system, couldn't able import them using Wizard.

    Googled around for many days, posted on msdn also but nobody gave the soltuin.

    If you can help me on that that will be great.

    What properties to be set insuch cases?

    I have done the acccess and foxpro db import using wizard.

    Monday, September 15, 2008 11:40 AM
    Moderator
  • Rupert,

     

    Thank you for your response

     

    I have imported data from the dbf file itself into SQL using Enterprise Manager.

    For the same I tried using Enterprise Management Studio. I was not able to import the data from dbf as it (dbf source) was not showing up in the data source list items.

     

    It seems that first we need to import the data into access and then to sql as you suggested.

     

     

    Regards,

     

    Suresh

    Tuesday, September 16, 2008 8:57 AM
  •  

    installed a VFP 9.0 where SQL Server resides, this is for testing purposes only. Whenever I migrate a table to SQL using the native OLEDB of VFP everything works fine until during the actual conversion process. 3 error are always reported:

     

    1. (Pre-Execute) Warning: 0x80202066 Source Query [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used....

     

    2. (Setting Source Connection) Warning: 0x8202066 Source Query...

     

    3. (Validating) Warning: 0x80202066 Data Flow Task:...

     

    From my observations, it might be that the 'date' data type conversion to datetime has an effect in the migration, if so, what is the best practive used by DBAs in this scenario? Thanks..

     

    Yong

    Friday, October 24, 2008 6:34 AM
  • It was the right syntax, but I had to undergo more hurdles liste below

     

    sp_configure 'Ad Hoc Distributed Queries', 1

    ' got an error so googled..... and got this....'

     

    sp_configure

    sp_configure 'show advanced options', 1

    reconfigure

    sp_configure

    sp_configure 'Ad Hoc Distributed Queries', 1

    reconfigure

     

    insert into march2008

    select * from OPENROWSET
    ('MICROSOFT.JET.OLEDB.4.0','dBase IV;HDR=NO;IMEX=2; DATABASE=Path_of_Folder','select * from c:\temp\mar0809.dbf')

     

    ' got error again googled and found the solution, you have to set the variables TEMP and TMP point to a local directory'
    ' My Computer  >> Properties >> Advanced >> Environment Variables >> User Variables for admin >> edit and set the

     

    path to say...c:\temp'

     

    insert into march2008
    select * from OPENROWSET
    ('MICROSOFT.JET.OLEDB.4.0','dBase IV;HDR=NO;IMEX=2; DATABASE=Path_of_Folder','select * from c:\temp\mar0809.dbf')

     

    ' again error, silly mistake, the sql was copied from google as it is, you have to replace Path_of_Folder above with actual folder name'

     

    insert into march2008
    select * from OPENROWSET
    ('MICROSOFT.JET.OLEDB.4.0','dBase IV;HDR=NO;IMEX=2; DATABASE=c:\temp','select * from mar0809.dbf')

     

    ' now what is the problem ???? you must specify which database the sql table belongs to....in my case the database name is mspatel0708''

     

    insert into mspatel0708.dbo.march2008
    select * from OPENROWSET
    ('MICROSOFT.JET.OLEDB.4.0','dBase IV;HDR=NO;IMEX=2; DATABASE=c:\temp','select * from mar0809.dbf')

     

    Saturday, December 06, 2008 8:27 AM
  • What finally worked for us was to use the FoxPro driver (available from Microsoft Download) and use the following syntax.  In our case we are using SQL 2008.

     

    select *

    from

     openrowset

    ('VFPOLEDB','\\VM-GIS\E\Projects\mymap.dbf';''

     '','SELECT * FROM mymap')

    Substitute the '\\VM-GIS...' with the location of your dbf file, either UNC or drive path.  Also, substitute 'mymap' after the FROM with the name of the dbf file without the .dbf extension.  Best thing is NO linked server needed.  It is just a SQL statement.  In fact you could make it part of an automated process by parameterizing the file name.

    ;

    Tuesday, June 14, 2011 4:05 PM
  • i'm able to import dbf data into sql server database table, if there is not date format field.

    If there are date format field in DBF and tries to import into sql server it gives an error as given below.

    Msg 8114, Level 16, State 9, Procedure spImportPreDB, Line 9
    Error converting data type (null) to datetime.

    please advise

    thanx in advance

    biju

    Friday, July 13, 2012 12:32 PM
  • I have created the below stored procedure but when executing this procedure it gives an error.

    /*

    (0 row(s) affected)
    OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "No value given for one or more required parameters.".

    */

    Please help

    Regards,

    biju

    USE [MetroDB]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[spImportPreDB]

    @pRefNo varchar(50)

    AS
    BEGIN

    insert into InvDetl (plmu1, plmu2, plmu3, plmu4,
    plmu5, plmu6, plmu7, plmu8, SbDt, TermType)
    select  plmu1, plmu2, plmu3, plmu4,
    plmu5, plmu6, plmu7, plmu8,sbdt, TermType   FROM OPENROWSET
    ('MICROSOFT.JET.OLEDB.4.0','dBase IV;HDR=NO;IMEX=2;
    DATABASE=D:\expo\', 'select  plmu1, plmu2, plmu3, plmu4,
    plmu5, plmu6, plmu7, plmu8, sbdt, TermType from Invdetl where Refno = @pRefNo ')
    END

    exec spImportPreDB @pRefNo='41337'

    Monday, July 16, 2012 10:54 AM