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
SQL table structure
Please help on above.
Thanks in Advance
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'");
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.