Import Dbase to MySql

  • Thread starter Thread starter Tibor Odehnal
  • Start date Start date
T

Tibor Odehnal

Hi,

I'd like to Import Dbase Files to a MySql-Database.
There are 6 Databases with 8000 Records.
Theu should be Importet every 2 Weeks.

I've played around with Datatables using newrows.
also direct SQL-Statements via executenonquery.

Everything work fine but one File will took 5 minutes to import.
Thats much to long.

Could someone help?

Thank U
Tibor
 
Hi Tibor,

I would do it this way:
Use dbase dataadapter, with AcceptChangesDuringFill set to false ,(oledb) to
Fill the tables and use mysql's adapter to do Update.
 
YO!
and this would take 23sek/500records ?!?!?!

I've also created 100 INSERT statements via StringBuilder ans sent it to
MySql with executenonquery. Same effekt ?!

I've useed the libs from ByteFX and the Mysql-ODBC Driver

Tibor
 
HI Tibor,

So the bottleneck is mysql server/provider?
Seems a bit odd the time required to me.
Can you show us some code?
 
this is one resolution out of 5 i've tried.
I've used the RowImport Method.
I've also tried to add the Rows manually with
dtMySql("lonx")=dtDbase("lonx") and then Update.
Funny thing beside: I've uploaded a textfile with the Records via phpMyAdmin
an it took 0,16 sec!!!!!!!

Dim dBaseConnection As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\bin;Extended Properties=dBase IV")
Dim MySqlConnection As New
MySqlClient.MySqlConnection("Server=mailufterl.odi.local;Database=RGA;User
ID=RGA;Password=RGA")
Dim dbaseAdapter As New OleDb.OleDbDataAdapter("SELECT * FROM
location", dBaseConnection)
Dim MySqlAdapter As New MySqlClient.MySqlDataAdapter("SELECT
lkey,lonx,laty FROM location", MySqlConnection)
Dim dtDbase As DataTable
Dim dtMySql As DataTable
Dim dsDbase As New DataSet
Dim dsMySql As New DataSet

Dim cRow As DataRow
Dim cmdB As New MySqlClient.MySqlCommandBuilder(MySqlAdapter)

dbaseAdapter.AcceptChangesDuringFill = False
dbaseAdapter.Fill(dsDbase, "location")

dtDbase = dsDbase.Tables("location")
dtDbase.Columns("key").ColumnName = "lkey"
dtDbase.Columns("lony").ColumnName = "lonx"
dtDbase.Columns("latx").ColumnName = "laty"
MySqlAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
MySqlAdapter.AcceptChangesDuringFill = False
MySqlAdapter.FillSchema(dsMySql, SchemaType.Mapped, "location")


dtMySql = dsMySql.Tables("location")
For Each cRow In dtDbase.Rows
dtMySql.ImportRow(cRow)
Next
MySqlConnection.Open()
MySqlAdapter.Update(dtMySql)
MySqlConnection.Close()
 
Hello. My name is Reggie Burnett and I am the author of the ByteFX
provider.
First, if you are using version 0.74 of ByteFX then that is part of the
problem. 0.74 has a bug that causes slow execution. 0.75 is much faster.

Also, I would not load data into a dataset and then import it over into
anotoher table to do an update. I would setup a select command against the
DBase files, setup an insert command against the MySql table, each using
parameters, and then iterate over all the records doing an insert on each
one. Something like this:

reader = dbaseCmd.ExecuteReader();
while (reader.Read())
{
mysqlCmd.Parameters[@parm1].value = reader[0];
mysqlCmd.Parameters[@parm2].value = reader[1];
...... for each column do the same.............
mysqlCmd.ExecuteNonQuery();
}

This is exactly what the dataadapter.update method is doing under the hood
but you are avoiding some overhead.

Reggie
 
Back
Top