title: Importing data using SQLBulkCopy
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>We are updating the system to use VS 2010 with SQL Server 2008 and according to the Microsoft documentation SQLDMO is no longer available.</p>
  
  <p>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.</p>
  
  <p>Code being tested is shown below</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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</p>
  
  <pre><code>[X1.CSV]
  Format=TabDelimited
  </code></pre>
  
  <p>Is there any way I can force the fields with spaces not to be treated as NULL when creating the DataTable? </p>
  
  <p>Is this the best approach for processing the Bulk Copy via a VB.Net program?</p>
  
  <p>TIA,</p>
  
  <p>Andy  </p>
  
  <p>Save to:   Default     </p>
  
  <p>Switch color theme<br>
  Select message background color...<br>
  Select message area width...<br>
  Adjust message text font size...<br>
  Disable auto links 
  Enable acronyms 
  Disable message header 
  Enable auto quote 
  Update the title of this thread... 
  <B>SQL Bulk Copy</B> Thread #1544244 Message #1544244 </p>