Import csv with incorrect data to sql server by SqlBulkCopy

• caisersouze

  SSC Enthusiast

  Points: 178

  More actions

  March 4, 2019 at 10:28 am

  #379544

  I have to try to import delimited files via c# to sql server database using .net framework SqlBulkCopy. I use StreamReader for read data from flat file and the DataTable for write to db by SqlBulkCopy. Unfortunately within the various fields delimited by the ";" we do not always have correct data, so we need to make corrections to the values we read, such as removing single quotes or replacing non-existent dates with real dates, or replace "," with "." for float number:

  string[] line = reader.ReadLine().Split(';');
line[0] = line[0].Trim().Replace("'", "");
….
…
line[n] = line[n].Trim().Replace(",", ".");
DataTable.Row.Add(line);

  I'm noticing though that in doing the replace and other operations on string arrays the performances are really really bad. Can anyone suggest the most efficient way to do this kind of load?

• frederico_fonseca

  SSCoach

  Points: 16225

  More actions

  March 4, 2019 at 11:59 am

  #2023467

  if you are already coding in C# then look at using the available .net to process csv files - after that its easier to deal with the transformation of dates and other contents.

  Microsoft.VisualBasic.FileIO class - don't get mislead by the name - this is .net
  https://docs.microsoft.com/en-us/dotnet/api/microsoft.visualbasic.fileio.textfieldparser?view=netframework-4.7.2
  This will deal with properly defined CSV files - as long as "columns" containing a doublequite, delimiter or cr+lf are correctly escape'd it will read them and split into the desired array.
  and a good example of building a data table which can then be used for bulkcopy and a good example of building a data table which can then be used for bulkcopy https://stackoverflow.com/questions/20759302/upload-csv-file-to-sql-server

  
Using MyReader As New Microsoft.VisualBasic.
   FileIO.TextFieldParser(
   "C:\TestFolder\test.txt")
MyReader.TextFieldType = FileIO.FieldType.Delimited
MyReader.SetDelimiters(";") -- semi-column delimiter
Dim currentRow As String()
While Not MyReader.EndOfData
Try
 currentRow = MyReader.ReadFields()
 Dim currentField As String
 For Each currentField In currentRow
  MsgBox(currentField)
 Next
Catch ex As Microsoft.VisualBasic.
  FileIO.MalformedLineException
 MsgBox("Line " & ex.Message &
 "is not valid and will be skipped.")
End Try
End While
End Using


  Edit: Remove "double posting of code"