Import the DBF file to Sqlserver database _ MySQL

Source: Internet
Author: User
Tags dbase
Project problems: the user selects N dbf files to import to the sql2005 database. because the dbf table structure is changing every year, it does not exist in sql2005 at all. it is necessary to automatically create a table based on the dbf structure every year. Solution (excerpted from the network): -- Method 1: select * I... project problems: the user selects N dbf files to import to the sql2005 database. because the dbf table structure is changing every year, it does not exist in sql2005 at all. it is necessary to automatically create a table based on the dbf structure every year.

Solution (extracted from the network ):

-- Method 1:

Select * into the SQL table name to be generated from OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'dbase IV; HDR = NO; IMEX = 2; DATABASE = c:/', 'select * from dbf table name. dbf ')

-- Method 2:

Select * into the SQL table name to be generated from OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'dbase III; HDR = NO; IMEX = 2; DATABASE = c:/', 'select * from dbf table name. dbf ')

-- Method 3:

Select * into the SQL table name to be generated from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c :/', 'select * from dbf table name. dbf ')

-- After the SQL SERVER is imported using the first two methods, if the source table is opened with VFP, the system does not prompt "file cannot be accessed". after the statement is executed, the source table is disabled. However, it is not satisfactory that the SQL table fields in the source table are converted to NVARCHAR after being imported using the first two methods.

-- The third method has one drawback: after importing the DBF table into SQL Server, you can use VISUAL FOXPRO to open the DBF table immediately and the system will prompt "file access unavailable ", that is, the table is still opened by SQL. However, after about one minute, you can open the DBF table, indicating that the analyzer will shut down the table after a period of time.

You can directly import the dbf file to the sqlserver database, or import the dbf file to the dataset file before importing the dataset data to the database. The implementation of importing a dbf file to dataset is as follows:

System. data. Odbc package needs to be introduced


Public DataSet importDbfToDataSet (string FilePath, string tabname) {string strConnection = @ "Dsn = Visual FoxPro Tables; sourcedb =" + FilePath. substring (0, FilePath. lastIndexOf ("//") + "; sourcetype = DBF; exclusive = No; backgroundfetch = Yes; collate = Machine"; // for connection strings, pay attention to version issues: string strSelect = "SELECT * FROM" + tabname; OdbcConnection thisConnection = new OdbcConnection (strConnection); thisConnection. open (); OdbcDataAdapter thisAdapter = new OdbcDataAdapter (strSelect, thisConnection); DataSet thisDataSet = new DataSet (); try {thisAdapter. fill (thisDataSet);} catch (Exception e) {throw e;} finally {thisConnection. close () ;}return thisDataSet ;}


The following program uses the Timer to set and regularly store the local dbf file to the database table. this dbf file will be overwritten on a regular basis.


Class Connect {// define the connection String, connection object, command object private String connectionstr; private SqlConnection connection; private SqlCommand command; private DataSet dataset; public Connect () {connectionstr = "Server = 192.168.88.59; Initial Catalog = Test; User ID = sa; Password = sasa;"; connection = new SqlConnection (connectionstr); dataset = new DataSet (); command = connection. createCommand (); connection. open ();} public void CreateTimer () {Timer timer = new Timer (); timer. enabled = true; timer. interval = 60*1000; // Set timer for one minute. elapsed + = new ElapsedEventHandler (timer_Elapsed);} void timer_Elapsed (object sender, ElapsedEventArgs e) {Connect c = new Connect (); int minute = e. signalTime. minute; int iminute = 5; if (minute = iminute) // set the fifth Minute of every hour to execute c. insert ();} private void Insert () {// string filepath = "C ;//"; // import the dbf file to the table string creattb = "select * into tablename from OPENROWSET ('msdasql ', 'driver = Microsoft visual foxpro driver; sourcedb = c ://; sourceType = dbf', 'select * from rsz1031.dbf') "; SqlCommand mycommand = new SqlCommand (creattb, connection); mycommand. executeNonQuery ();}}


1. unable to initialize the data source object of the ole db access interface "msdasql" linked to the server "(null ";

The reason is that the system where the sql2005 database is installed does not have the VFPODBC driver, which is downloaded and installed at http://msdn.microsoft.com/en-us/vfoxpro/bb213233.aspx.

2. the SQL Server blocks access to the STATEMENT 'openrowset/OpenDatasource 'of the 'ad Hoc Distributed Queries' component;

The reason is that "openrowset and opendatasource support is not enabled in the" peripheral application configurator of the function ". you only need to enable the" peripheral application configurator of the function.

The preceding figure shows how to import the DBF file to _ MySQL. For more information, see PHP Chinese network (www.php1.cn )!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.