We have a legacy application that is dumping large volumes of data to tab delimited files. Each file contains a single record type and all fields are fixed length.
These files can readily be imported into corresponding tables in our SQL server database using the BCP utility from the command line. We have a VB.Net program written in VS 2003 that imports these files using the SQLDMO.BulkCopy routine.
We are updating the system to use VS 2010 with SQL Server 2008 and according to the Microsoft documentation SQLDMO is no longer available.
I have searched on the internet and have rewritten the import routine to import the tab delimited files into a DataTable using the Microsoft.Jet.OLEDB.4.0 provider. The SqlClient.BulkCopy object is then used to import this DataTable. This issue I am having is that fields in the tab delimited file that are set to spaces are being treating as NULLs when imported into the DataTable. When the DataTable is processed by the SqlClient.BulkCopy the copy fails because the null values are rejected by the SQL table fields that are defined as NOT NULL.
Code being tested is shown below
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click Dim data As DataTable = RetrieveSourceData() CopyData(data) End Sub Private Function RetrieveSourceData() As DataTable Dim connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\temp\;Extended Properties='text;HDR=No;FMT=TabDelimited'" Dim sourcedata As New DataTable Using conn As New OleDb.OleDbConnection(connstring) conn.Open() Dim command As New OleDb.OleDbCommand("Select * from X1.CSV", conn) Dim adapter As New OleDb.OleDbDataAdapter(command) adapter.Fill(sourcedata) conn.Close() End Using Return sourcedata End Function Private Sub CopyData(SourceData As DataTable) Dim dbConnString As String = "Data Source=(local);Initial Catalog=XtractDB;User ID=xxxx;Password=yyyy;" Using bcp As New SqlClient.SqlBulkCopy(dbConnString) bcp.DestinationTableName = "X1" bcp.BatchSize = 1000 bcp.WriteToServer(SourceData) End Using End Sub
In order for the input file to be recognised as TabDelimited I have had to create a schema.ini file in the same directory as the input file. The contents are shown below
Is there any way I can force the fields with spaces not to be treated as NULL when creating the DataTable?
Is this the best approach for processing the Bulk Copy via a VB.Net program?
Save to: Default
Switch color theme
Select message background color...
Select message area width...
Adjust message text font size...
Disable auto links Enable acronyms Disable message header Enable auto quote Update the title of this thread... SQL Bulk Copy Thread #1544244 Message #1544244
SqlDMO is deprecated in Sql server 2012 but it's available till Sqlserver 2008 R2 version. SqlBulkCopy is available in all .net frameworks till 4.5 except 1.1.
You can use BULK INSERT (command line utility) to insert multiple records at once.
BULK INSERT Tablename FROM 'c:\csvtest.txt' WITH ( FIELDTERMINATOR = ',' ,ROWTERMINATOR = '\n' --,FIRSTROW = 2 --,MAXERRORS = 0 ) GO