Using SqlBulkCopy with Line Feed and Carriage Return

[anusia]

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 when its imported to mssql.Curently it is inserted as a long string .

Example below .

One of the cell has this content

this is 1st line
this is 2nd line

When i use SqlBulkCopy to import excel data to mssql.The above is inserted as "this is 1st linethis is 2nd line "

How can i retain the "Carriage Return/Line Feed" from the excel cell ? Please help.Thank you.

[anusia]

to add: Iam using vb.net for the above .

[anusia]

Below is the codes for what i have now

   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

[Rabbit]

It probably transferred just fine, it's simply that the SSMS result set doesn't print multi-line characters to the screen. If you want to make sure it's there, just substring the character that the CR or LF is supposed to be at and return the ASCII value.

[anusia]

Thanx Rabbit.your right.. the SSMS result set doesn't print multi-line characters to the screen.I used the below query to to check if it was actually there in DB .


SELECT di_borrower
FROM SL_DataInfo
WHERE (di_borrower LIKE '%' + CHAR(10)+ '%')

AND HAPPY ME !! IT WAS THERE IN DB ! So the sqlbulkinsert did insert correctly after all.

Then just to share my solution, what i did to fix it was.

#1 - replace the data with </br>

update dbo.SL_DataInfo_Temp set
di_borrower = replace(cast(di_borrower AS nvarchar(250)), char(10), '<br />')

#2 - Change the gridview column HTML Encode = False.

This made the gridview display the Carriage Return correctly .

Thanx for the input Rabbit ! :)

[Rabbit]

Glad you got it working, good luck with the rest of your project.