Nrthugu

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

[X1.CSV]

Format=TabDelimited

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?

TIA,

Andy

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.

Refer this link for verification

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