0

I am using SqlBulkCopy to import Excel data to mssql. I have many rows of data in Excel with 2 columns which the content has Line Feed and Carriage Return (I understand from reading online below is true)

Chr(10) == vbLF == Line Feed (LF)
Chr(13) == vbCR == Carriage Return (CR)
Chr(13) & Chr(10) == vbCrLf == Carriage Return/Line Feed

The SqlBulkCopy is working perfectly but I want to retain the Carriage Return/Line Feed which is in the Excel cell when its imported to mssql. Currently it is inserted as a long string.

Example: One of the cells has the contents below [multiple rows in single cell].

1st line in cell 1
2nd line in cell 1

When I use SqlBulkCopy to import Excel data to mssql, the above status column is inserted as

"1st line in cell 12nd line in cell 1"

How can I retain the "Carriage Return/Line Feed" from the Excel cell so that it will insert in database as below?

1st line in cell 1
2nd line in cell 1

Below is the code I have.

Dim conectionstring As String = ""
If strExt.ToLower() = ".xls" Then
    conectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel & ";Extended Properties=Excel 8.0"
ElseIf strExt.ToLower() = ".xlsx" Then
    conectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Excel & ";Extended Properties=Excel 12.0"
End If
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(conectionstring)
ExcelConnection.Open()
Dim expr As String = "SELECT * FROM [Sheet1$] where not U_Id is null"
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()

SQLconn.ConnectionString = ConnString
SQLconn.Open()

Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)

    bulkCopy.DestinationTableName = "SL_DataInfo_Temp"
    bulkCopy.ColumnMappings.Add("U_Id", "di_id")
    bulkCopy.ColumnMappings.Add("File_Ref", "di_fileRef")
    bulkCopy.ColumnMappings.Add("Date", "di_date")
    bulkCopy.ColumnMappings.Add("Status", "di_status")

    objDR = objCmdSelect.ExecuteReader

    If objDR.HasRows Then ''And objDR.FieldCount >= 13 Then
        bulkCopy.WriteToServer(objDR)
        ExcelConnection.Close()
        SQLconn.Close()
    End If 
End Using
Adi Inbar
  • 12,097
  • 13
  • 56
  • 69
user2698313
  • 1
  • Have you tried changing the bulkcopy delimiters? It can't work with the default linefeed row delimiter, because that would be a new new row/new record at every crlf – david Aug 20 '13 at 03:25
  • Thanx Adli Inbar for your reply. But how do i set the delimiters ? I couldnt find any from this link as well [link](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy_methods.aspx) – user2698313 Aug 20 '13 at 04:27
  • Ive solved it ! SSMS result set doesn't print multi-line characters to the screen.I check if it was actually there in DB . SELECT di_borrower FROM SL_DataInfo WHERE (di_borrower LIKE '%' + CHAR(10)+ '%') IT WAS THERE IN DB ! So the sqlbulkinsert did insert correctly after all. What i did to fix it was. #1 - replace the data with update dbo.SL_DataInfo_Temp set di_borrower = replace(cast(di_borrower AS nvarchar(250)), char(10), '
    ') #2 - Change the gridview column HTML Encode = False. This made the gridview display the Carriage Return.     – user2698313 Aug 20 '13 at 15:32

0 Answers0