Search code examples
sql-serveremailbulkinsertend-of-line

SQL Server - Bulk insert without losing CR or LF characters

I am trying to import email communication into a database table using Bulk Insert but I can't seem to be able to preserve the CR and LF characters. Let's consider the following:

CREATE TABLE myTable (
    Email_Id int,
    Email_subject varchar(200) NULL,
    Email_Body TEXT NULL
)

The bulk insert statement has the following: 

codepage = '1250',
fieldterminator = '<3P4>',
rowterminator = '<3ND>',
datafiletype = 'char'

The file contains full emails (including CR and LF characters). I would like to import the data and include the CR and LF characters. I have read that BULK INSERT treats each entry as a single row but does that mean it strips out the CR and LF characters? If so, what can I use to import this CSV file? I don't have access to SSIS and I would prefer to use SQL code to do it.

Example data:

11324<3P4>Read this email because it's urgent<3P4>Haha John, 

I lied, the email was just to mess with you!

Your Nemesis,
Steve

P.S. I still hate you!
<3ND>
11355<3P4>THIS IS THE LAST STRAW<3P4>Steve, 

I have had it with you stupid jokes, this email is going to the manager.

Good day,
John
<3ND>
Solution

  • It should import with the carriage returns and linefeeds, even if you don't see them in some tools. We would import XSL this way and it would preserve all of the line formatting.