3

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>
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
alexstr
  • 35
  • 3

1 Answers1

2

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.

Joe
  • 41,484
  • 20
  • 104
  • 125
  • Joe is right. You may not see them in the grid view within Management Studio but you should see them if you switch to the text view or copy the data from that field and view it in something like text-pad or an email client, etc. – Chris Townsend Nov 29 '11 at 20:21
  • I have copied the text in Notepad++ and I have seen no CR or LF, I will try to export the results but I expect the same result. – alexstr Nov 29 '11 at 22:52
  • How are you getting the text out for comparison? Some of the tools will strip the line endings. – Joe Nov 29 '11 at 23:57
  • I have exported the data in to a csv file and it preserves the CR ad LF characters. For some reason when selecting the cell and copying it directly from the SQL Management Studio strips out CR and LF characters. – alexstr Nov 30 '11 at 08:07