Using SqlBulkCopy with Line Feed & Carriage Return

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

Using SqlBulkCopy with Line Feed & Carriage Return

Author

Topic

annu84
Starting Member

3 Posts

Posted - 2013-08-19 : 22:08:07

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.Currently it is inserted as a long string .

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

U_Id File_Ref Date Status
---------------------------------------------------------
23 WED/RFE/12/32 20/8/2013 1st line in cell 1
2nd line in cell 1
---------------------------------------------------------
24 WED/RFE/12/33 21/8/2013 status1
status1
---------------------------------------------------------

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

annu84
Starting Member

3 Posts

Posted - 2013-08-19 : 22:11:17

sorry guys , the example in my post didt display the correct format .Pls refer below

Example below .
One of the cells has multiple rows in single cell.

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

I want this to be inserted in mssql as 2 rows and not 1 long string.Pls help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources