Import DBF File into SQL Server using vb.net

Hi,
I need to import dbf file into sql server. This dbf file is about 300 MB
with a 250,000 records. The import process needs to happen once a month.
I can create a DTS package and schedule it but the problem is that the
structure of DBF file (i.e. no. of columns) changes.
So, my solution was to:
1.) Read the schema of dbf file.
2.) Truncate and drop the table if it exist.
3.) Create table accordingly to the schema read in step 1.
4.) Output the dbf file to an xml file. (This step fails).
5.) Read the xml file. (never reaches this step).
6.) Create an index for a column that is going to be used for searching
records. (Hopefully will reach to this step).
Following is my code for step 4:
Dim strSql, strMsg As String
Dim myConnection As New OleDbConnection(m_strDBFConnection)
myConnection.Open()
Dim myCommand As New OleDbCommand(strSql, myConnection)
myCommand.CommandText = "Select Parcel.* From Parcel"
Dim myAdapter As New OleDbDataAdapter
myAdapter.SelectCommand = myCommand
Dim myDS As New DataSet
myAdapter.Fill(myDS) 'Take a long time here. In sql server it
take about 2 minutes to read th parcel table.
myDS.WriteXml("C:\parcel.xml")
I am using SQL 2K and .NET 2003.
If you need more explanation please let me know.
Thanks.
P.S.: I hope this is the right group for my query.Hi SQL Newbie,
Have you considered using a linked server? Then you can drop the table each
month and use Select ... Into SomeTable.
Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
To set up the linked server you can use:
Use Master
Go
EXEC master.dbo.sp_addlinkedserver
@.server = N'MyLinkedServer',
@.srvproduct=N'Visual FoxPro 9',
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\MyPath\MyDirectory\"',
@.provstr=N'VFPOLEDB.1'
To select data you can use this as your command text:
Drop Table Parcel
- followed by -
Select * Into Parcel From MyLinkedServer...Parcel -- 3 dots are required.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"SQL Newbie" <SQLNewbie@.discussions.microsoft.com> wrote in message
news:28349691-2D45-43FE-9B19-0DB798CB0D6F@.microsoft.com...
> Hi,
> I need to import dbf file into sql server. This dbf file is about 300 MB
> with a 250,000 records. The import process needs to happen once a month.
> I can create a DTS package and schedule it but the problem is that the
> structure of DBF file (i.e. no. of columns) changes.
> So, my solution was to:
> 1.) Read the schema of dbf file.
> 2.) Truncate and drop the table if it exist.
> 3.) Create table accordingly to the schema read in step 1.
> 4.) Output the dbf file to an xml file. (This step fails).
> 5.) Read the xml file. (never reaches this step).
> 6.) Create an index for a column that is going to be used for searching
> records. (Hopefully will reach to this step).
> Following is my code for step 4:
> Dim strSql, strMsg As String
> Dim myConnection As New OleDbConnection(m_strDBFConnection)
> myConnection.Open()
> Dim myCommand As New OleDbCommand(strSql, myConnection)
> myCommand.CommandText = "Select Parcel.* From Parcel"
> Dim myAdapter As New OleDbDataAdapter
> myAdapter.SelectCommand = myCommand
> Dim myDS As New DataSet
> myAdapter.Fill(myDS) 'Take a long time here. In sql server it
> take about 2 minutes to read th parcel table.
> myDS.WriteXml("C:\parcel.xml")
> I am using SQL 2K and .NET 2003.
> If you need more explanation please let me know.
> Thanks.
> P.S.: I hope this is the right group for my query.|||Hello Cindy,
The main reason for me doing all these was to let the customer decide when
he wants newer data in the database. My goal was to automate the whole
process and let customer do it with just a button click. The button click
would do the steps I mentioned in my initial post i.e.
> 1.) Read the schema of dbf file.
> 2.) Truncate and drop the table if it exist.
> 3.) Create table accordingly to the schema read in step 1.
> 4.) Output the dbf file to an xml file. (This step fails).
> 5.) Read the xml file. (never reaches this step).
> 6.) Create an index for a column that is going to be used for searching
> records. (Hopefully will reach to this step).
I cannot use linkedserver as I will have to drop it each month and I want to
let customer handle this.
But just now I got an idea. Please let me know what you think about it. It
is as follows:
Whenever the customer wants to new data I will just drop the existing linked
server and create a new one using sp_addlinkedserver.
Then, use this newly linked server for my applications.
Thanks for your response.
"Cindy Winegarden" wrote:

> Hi SQL Newbie,
> Have you considered using a linked server? Then you can drop the table eac
h
> month and use Select ... Into SomeTable.
> Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
> downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
> To set up the linked server you can use:
>
> Use Master
> Go
> EXEC master.dbo.sp_addlinkedserver
> @.server = N'MyLinkedServer',
> @.srvproduct=N'Visual FoxPro 9',
> @.provider=N'VFPOLEDB',
> @.datasrc=N'"C:\MyPath\MyDirectory\"',
> @.provstr=N'VFPOLEDB.1'
> To select data you can use this as your command text:
> Drop Table Parcel
> - followed by -
> Select * Into Parcel From MyLinkedServer...Parcel -- 3 dots are required.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
> "SQL Newbie" <SQLNewbie@.discussions.microsoft.com> wrote in message
> news:28349691-2D45-43FE-9B19-0DB798CB0D6F@.microsoft.com...
>
>|||Hi Newbie,
The linked server is not the same as linking a table in Microsoft Access.
It's basically a permanent OLE DB connection to a data directory where the
DBFs are located. The linked server does not need to know the names or
structures of the tables it is linked to. Each time it's used it will look
in the directory it points to to find the table referenced in the T-SQL
Select (etc.) command.
Why don't you just write an SQL stored procedure to select from the source
table into an SQL Server table directly? It will create the SQL table for
you without your needing to know the table schema up front. Part of the
stored procedure can be to index whichever column you need to work with.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"SQL Newbie" <SQLNewbie@.discussions.microsoft.com> wrote in message
news:5FD8DCF4-80C2-4A1D-923A-60A22F1676AF@.microsoft.com...
> Hello Cindy,
> The main reason for me doing all these was to let the customer decide when
> he wants newer data in the database. My goal was to automate the whole
> process and let customer do it with just a button click. The button click
> would do the steps I mentioned in my initial post i.e.
> I cannot use linkedserver as I will have to drop it each month and I want
> to
> let customer handle this.
> But just now I got an idea. Please let me know what you think about it. It
> is as follows:
> Whenever the customer wants to new data I will just drop the existing
> linked
> server and create a new one using sp_addlinkedserver.
> Then, use this newly linked server for my applications.|||Hello Cindy,
Your solution takes me back to my original post where I wanted to write the
dbf file to xml file and then read back into a table in SQL Server. If I am
not able to write the dbf file to xml file than the only solution for me
would be to iterate through the quarter million record and insert them
individually. I am looking for a more efficient way to import dbf file into
sql server either using stored procedures or vb.net or combination of both.
Your help is very much appreciated.
Thanks & Regards.
"Cindy Winegarden" wrote:

> Hi Newbie,
> The linked server is not the same as linking a table in Microsoft Access.
> It's basically a permanent OLE DB connection to a data directory where the
> DBFs are located. The linked server does not need to know the names or
> structures of the tables it is linked to. Each time it's used it will look
> in the directory it points to to find the table referenced in the T-SQL
> Select (etc.) command.
> Why don't you just write an SQL stored procedure to select from the source
> table into an SQL Server table directly? It will create the SQL table for
> you without your needing to know the table schema up front. Part of the
> stored procedure can be to index whichever column you need to work with.
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
> "SQL Newbie" <SQLNewbie@.discussions.microsoft.com> wrote in message
> news:5FD8DCF4-80C2-4A1D-923A-60A22F1676AF@.microsoft.com...
>
>|||Hi Newbie,
I think you're missing something when you read my posts. There is NO need
for XML.
I assume you will get the name of the DBF to import from a textbox on your
VB app's form. In your VB app you will execute SQL that calls a stored
procedure with a parameter of the name of the DBF.
I'm assuming the DBFs will always be in the same directory, so create a
Linked Server on the server and point it to the directory where the DBFs
will be kept. Assuming you have installed the VFP OLE DB data provider, you
can follow this example:
-- ----
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFPTest',
@.srvproduct=N'Your description here',
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\Temp\"',
@.provstr=N'VFPOLEDB.1'
-- ----
Write an SQL stored procedure that takes the name of the new file as a
parameter.
-- ----
Create Procedure dbo.spImportData
@.TableName VarChar(10)
As
Declare @.SelectCommand VarChar(100)
-- Delete existing table
If Exists
(Select * From Sys.Objects Where Object_ID =
Object_ID(N'dbo.VFPImport') And Type In (N'U'))
Drop Table dbo.VFPImport
-- Import new data
Select @.Selectcommand =
'Select * Into dbo.VFPImport From VFPTest...' + @.TableName
Exec (@.Selectcommand)
Go
-- ----
Here's some VB code to test it all out:
-- ----
Imports System
Imports System.Data
Imports System.Data.OleDb
Module Module1
Sub Main()
Try
'-- Make some VFP data to play with
Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()
Dim cmd1 As New OleDbCommand( _
"Create Table TestImport (Field1 C(10))", cn1)
Dim cmd2 As New OleDbCommand( _
"Insert Into TestImport Values ('HelloWorld')", cn1)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
'-- Connect to the SQL Server to run your stored proc
Dim cn2 As New SqlClient.SqlConnection( _
"Data Source = C-K-Winegarden;Initial Catalog = Test;" + _
"Integrated Security = True;")
cn2.Open()
'-- Assume you'll be getting tableName from a TextBox on a form
Dim tableName As New String("TestImport")
Dim cmd3 As New SqlClient.SqlCommand( _
"Exec dbo.spImportData @.TableName = " + tableName, cn2)
cmd3.ExecuteNonQuery()
cn1.Close()
cn2.Close()
Catch e As Exception
MsgBox(e.ToString())
End Try
End Sub
End Module
-- ----
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"SQL Newbie" <SQLNewbie@.discussions.microsoft.com> wrote in message
news:013AF82C-60AC-45E0-927B-5EBFB321BE69@.microsoft.com...
> Hello Cindy,
> Your solution takes me back to my original post where I wanted to write
> the
> dbf file to xml file and then read back into a table in SQL Server. If I
> am
> not able to write the dbf file to xml file than the only solution for me
> would be to iterate through the quarter million record and insert them
> individually. I am looking for a more efficient way to import dbf file
> into
> sql server either using stored procedures or vb.net or combination of
> both.