Bulk insert into SQL table from CSV using C#

bulkinsert c# sql sqlbulkcopy sql-server

Question

I need to bulk insert data into SQL from a csv file. In SQL I use the command:

bulk insert InputTestData
from 'D:\Project\UnitTestProjct\RGTestingToolTestProject\NUnitTestProject\RGTestToolDB\InputTestData.csv'
with
(   CODEPAGE ='RAW',
    rowterminator='\n',
    fieldterminator = '\t'
)

The above command is working and inserts data when using SQL query analyzer, but, throws an error when executed from C# using the code given below :

 StringBuilder builder = new StringBuilder();
                                    builder.Append("bulk insert " + objectName + " from ");
                                    builder.Append("'" + ResourceFilePath + Path.DirectorySeparatorChar + objectPath + "'");
                                    builder.Append(" with");
                                    builder.Append(" (");                                       
                                    builder.Append(" rowterminator='\n',");
                                    builder.Append(" fieldterminator = '\t'");
                                    builder.Append(" )");

                                    SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, builder.ToString());

It throws the error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 8 (IsBaselineTest).

The query generated by C# code is also working fine in SQL query analyzer :

bulk insert InputTestData from 'D:\Project\UnitTestProjct\RGTestingToolTestProject\NUnitTestProject\\RGTestToolDB\InputTestData.csv' with ( rowterminator='
', fieldterminator = '  ',CODEPAGE ='RAW' )

Please let me know if you have required CSV and SQL table structure

Please help on above.

Thanks in Advance

Accepted Answer

I'm pretty sure you have to escape the backslash's for the line and field terminators, since it looks like the string already converted them to the actual characters 0x10 and 0x09. I would think it has to be

builder.Append(" rowterminator='\\n',"); 
builder.Append(" fieldterminator = '\\t'");

Popular Answer

i just completed a similar task using SqlBulkCopy as suggested by some comment.

my source is a XML document but the situation is pretty similar.

the solution i developed is a web interface to upload the data, some code to put it in a datatable and then a call to a SqlBulkCopy to insert into a temporary table.

the last step is a query that insert the data into the final table handling duplicates and performing some validation.

here is some code (is vb.net actually):

' create in memory datatable
' my choice has been to have the same datatype for all fields
Dim sapCustomer As DataTable = New DataTable("customer")
For Each SAPFieldName As String In SAPColMapping
  sapCustomer.Columns.Add(New DataColumn(SAPFieldName, GetType(System.String)))
Next

' fill previous table using xml data
For Each SapRow As XmlNode In RowList.SelectNodes("ROW")
  ... more code here to translate xml into datatable...
Next

' create temporary table on sql server
Using dbCmd As SqlCommand = New SqlCommand
  dbCmd.Connection = dbConn
  dbCmd.Transaction = dbTran
  dbCmd.CommandType = CommandType.Text
  dbCmd.CommandText = "create table #tempTable (your fields here)"
  dbCmd.ExecuteNonQuery
End Using

' fill temp table
Using sbc As SqlBulkCopy = New SqlBulkCopy(dbConn, SqlBulkCopyOptions.Default, dbTran)
  sbc.BatchSize = 1000
  ' no explicit mapping between source and destination fields
  ' because both tables have the very same field names
  sbc.DestinationTableName = "#tempTable"
  sbc.WriteToServer(sapCustomer)
End Using

' handle the steps needed to copy/move the data to the final destination
Using dbCmd As SqlCommand = New SqlCommand
  dbCmd.Connection = dbConn
  dbCmd.Transaction = dbTran
  dbCmd.CommandType = CommandType.Text
  dbCmd.CommandText = "insert into finaltable select field1, field2 from #tempTable"
  dbCmd.ExecuteNonQuery
End Using

the above code succesfully handles xml documents 50mb+ with 50k records in 1 min.

the heavy task is the copy of the data from xml to datatable: a bunch of secs for upload, 40 sec for xml -> datatable and 2 sec for the database processing.

if your data is in a more 'datatable friendly' format you may achieve better performances easily.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why