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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Using SqlBulkCopy with Line Feed & Carriage Return
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

annu84
Starting Member

3 Posts

Posted - 08/19/2013 :  22:08:07  Show Profile  Reply with Quote
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 - 08/19/2013 :  22:11:17  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000