none
import data from dbf file RRS feed

  • Question

  • i need to import data in to sqltable from dbf file. kindly suggest to import data directly from dbf file to sqltable with out using datarow(because i have to import more than 100,000 records if i use datarow its taking time to import.)
    Monday, April 20, 2009 1:54 PM

Answers

All replies

  • In a case of SQL Server 2005 or newer you could use SQL Server Integration Services (SSIS). That engine allows integration of the data between different data sources (databases). In a case of SQL Server 2000 it is called DTS. One more option is to load data into DataTable and then use SqlBulkCopy .NET class to insert data into SQL Server as a single batch.
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, April 21, 2009 10:19 AM
    Moderator
  •  

    Thank you sir,

    Sir could you please explain me how to use SqlBulkCopy .

    Tuesday, April 21, 2009 11:01 AM
  • I did an import using SqlBulkCopy and it was way faster that using regular inserts. I opened the dbf table using a DataReader and moved that into teh SqlBulkCoy object. I was able to import a dbf database with over 100 tables and 1 GB ( not very big by Sql standards ) of data in less that 2 minutes.
    Tuesday, April 21, 2009 1:38 PM
  •  thank u michael,


    sir could you please give me any sample code on this.

    Wednesday, April 22, 2009 6:07 AM
    • Marked as answer by sridns Wednesday, April 22, 2009 11:41 AM
    • Unmarked as answer by sridns Thursday, April 23, 2009 6:05 AM
    • Marked as answer by VMazurModerator Thursday, April 23, 2009 10:12 AM
    Wednesday, April 22, 2009 9:52 AM
    Moderator
  • Thank you sir,

     thank you very much now i am able to do import with in 2 secs  using sqlbulkcopy .



    Wednesday, April 22, 2009 11:45 AM
  • Dear sir,

    Can i use this sqlbulk copy to export data from sql table to dbf file. if not how can i export data without using datarow.
    Wednesday, April 22, 2009 12:14 PM
  •  

    private void SendData(String TableName, String ConnStr, String ExePath, String DataPath, StreamWriter log)

    {

     

     

    DataTable tbl = new DataTable();

    tbl

    .TableName = TableName;

     

     

    using (OleDbConnection source = new OleDbConnection("Provider=vfpoledb.1;Data Source=" + DataPath + ";Collating Sequence=general"))

    {

     

    using (SqlConnection dest = new SqlConnection(ConnStr))

    {

    dest

    .Open();

    dest

    .ChangeDatabase("pcs");

     

    // Read with a false where to grab schema from SQL server - like set fmtonly on/off

     

    using (SqlCommand cmd = new SqlCommand("select * from " + TableName + " where 1=2", dest))

    {

     

    using (SqlDataReader sRdr = cmd.ExecuteReader())

    {

    tbl

    .Load(sRdr);

     

    // Load from source into existing schema

    source

    .Open();

     

    using (OleDbCommand sourceCommand = new OleDbCommand(@"select * from " + TableName, source))

    {

     

    using (OleDbDataReader dr = sourceCommand.ExecuteReader())

    {

    log

    .WriteLine("Retrieving table '" + TableName + " from version 7.");

     

    try

    {

    tbl

    .Load(dr);

    }

     

    catch (OleDbException odbex)

    {

     

    DataTable err = new DataTable();

     

    using (OleDbCommand errCommand = new OleDbCommand(@"select * from " + TableName, source))

    {

     

    using (OleDbDataAdapter da = new OleDbDataAdapter())

    {

    da

    .SelectCommand = errCommand;

    da

    .Fill(err);

    }

    // using OleDbReader

    }

    //using OleDbCommand

    WriteError(ExePath,

    "Error in data retrieval\n\n" + odbex.Message.ToString());

    log

    .WriteLine("Error in retrieving table '" + TableName);

    }

     

    catch (Exception e)

    {

    log

    .WriteLine("Exception encountered while loading source data for bulk copy: " + e.Message.ToString());

    }

    source

    .Close();

    }

    // using OleDbReader

    }

    //using OleDbCommand

    }

    // using SqlDataReader

    }

    // using SqlCommand

    }

    // using SqlConnection

    }

    // using OleDbConnection

    log

    .WriteLine("Beginning to copy table '" + TableName + " to server...");

     

    try

    {

     

    // the only way I got this to work was to add the catalog choice to the connection string

     

    // whic should be safe since I have built the db already

     

    using (SqlBulkCopy s = new SqlBulkCopy(ConnStr + ";Initial Catalog=pcs", SqlBulkCopyOptions.KeepIdentity))

    {

    s

    .BatchSize = 500;

    s

    .BulkCopyTimeout = 300;

    s

    .DestinationTableName = "pcs.dbo." + TableName;

    s

    .NotifyAfter = 100;

    s

    .SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);

     

    s

    .WriteToServer(tbl);

    s

    .Close();

    }

     

    log

    .WriteLine("Copying table '" + TableName + "' complete.");

    }

    // try

     

    catch (Exception e)

    {

    log

    .WriteLine("BulkCopy Exception encountered: " + e.Message.ToString());

    }

    }

    // SendData(

    Wednesday, April 22, 2009 1:30 PM
  • Thank u sir,


    I am able to import data fro dbf file to sql table but sir here i am unable to export data from sql table to dbf file.


    Kindly help me to insert data from sql table to dbf file.
    Wednesday, April 22, 2009 3:13 PM
  • Open aa Sq;Reader object to read in your data.

    Instaniate and OleDbConnection and OleDbCommand objects pointed at your dbf tables. Here you can do one of many things. You can build an insert command or something else.
    Wednesday, April 22, 2009 3:36 PM

  • If i used insert command using datarow its taking so much time more than 20 mins to insert all records because i have to unse more than  100,000 rows . can't i use any direct method like sqlbulk copy  to insert into dbf file. sql bulk copy is not supporting to insert to dbf file. kindly give any of sample to insert into dbf file from sql wihout using row by row  .
    Thursday, April 23, 2009 5:53 AM
  • SqlBulkCopy works only for SQL Server. If you need to export into DBF, you would need to write your own code or use some sort of third-party tool.
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, April 23, 2009 10:14 AM
    Moderator