Post reply

BCP when a NTEXT column contains carriage return

  • June 13, 2012 at 10:30 am

    #262855

    Hello, I am trying to use BCP to export a table to a flat, delimited file then import the data to an identical table on another server. The table contains email data. The email body is in an NTEXT column. Emails contain newline and carriage return characters and those are throwing off my format file.

    Can anyone give me any pointers on how to code a BCP format file to handle a column that can contain newline / carriage returns? I am using a non-XML format file but will use XML if that would make this work...

    thanks!

  • June 13, 2012 at 12:05 pm

    #1500575

    i use a special delimiter that I know will not exist in theactual data;

    here's my classic example,w hich i use to export html a lot...that always contains a ton of CrLf stuff.

    in this example my delimiters are four characters in length;

    --using a super special 4 char row delimiter to be sure it doesn't exist in the data

    --flags explanation:

    -- -c = character data

    -- -t"[||]" = field terminator

    -- -r"[~~]" = row terminator

    -- -T' = Trusted connection

    --out

    EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT EMAILTEMPLATESID, EMAILBODY FROM BFONRA.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID" queryout c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

    --in

    EXECUTE master.dbo.xp_cmdshell 'bcp BFONRA.dbo.EMAILTEMPLATES in c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

    --in via bulk insert

    BULK INSERT EMAILTEMPLATES FROM 'c:\Data\bcpExample.txt'

    WITH (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = '[||]',

    ROWTERMINATOR = '[~~]',

    FIRSTROW = 1

    )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 13, 2012 at 12:14 pm

    #1500581

    Thanks. I will be trying that. And that would not require a format file would it?

  • June 13, 2012 at 12:28 pm

    #1500593

    yes that is correct; because i'm using a specific delimiter, no format file is needed.

    format files are useful when you have stuff like fixed width files or quote delimited strings, where you are "working around" someone elses poorly formatted export.

    with a BCP OUT with custom delimiters, that's not an issue.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 18, 2012 at 8:43 am

    #1502259

    Thanks again for the suggestion. I now have all errors ironed out but that leads to another question. When I run the IN command to import the data I get no errors but it says "0 rows copied". There is definitely data in my flat file. I added -e and a error file but it comes back empty.

    I wonder why no data is loading. I have triple checked all my syntax and parms...

  • June 18, 2012 at 8:46 am

    #1502260

    weird that you are getting no rows; my example is syntactically correct and runs fine for me.

    can you paste your exact bcp commnad you are using?

    did you try via BULK INSERT? did the bulk insert also not import anything?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 18, 2012 at 9:00 am

    #1502276

    I have not tried BULK INSERT yet. You can see, my export query requires a SELECT * and a join so I must do two imports to handle the two tables. I am also using the attached format files to handle two tables' worth of data in one flat file. Server names are hidden...

    Export:

    bcp "SELECT * FROM egarchivedb.dbo.egpl_casemgmt_activity a left outer join egarchivedb.dbo.egml_email_data_alt d ON d.activity_id = a.activity_id WHERE a.when_created >= dateadd(month, -6, getutcdate()) and a.department_id in (1014,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025)" queryout U:\ArchivePurge\BCP_Mortgage_email_bodies.txt -w -T -S xxxxxxxxxxxxxx\INST1,1433 -t"[||]" -r"[~~]"

    Import 1:

    bcp egarchivedb.dbo.egpl_casemgmt_activity in R:\ArchivePurge\BCP_Mortgage_email_bodies.txt -f R:\ArchivePurge\FormatFiles\format_for_EGPL_CASEMGMT_ACTIVITY.fmt -T -S xxxxxxxxxxxxxxx\INST1,1433 -t"[||]" -r"[~~]" -e R:\ArchivePurge\errorfile.txt

    Import 2:

    bcp egarchivedb.dbo.egml_email_data_alt in R:\ArchivePurge\BCP_Mortgage_email_bodies.txt -f R:\ArchivePurge\FormatFiles\format_for_EGML_EMAIL_DATA_ALT.fmt -T -S xxxxxxxxxxxxxxxx\INST1,1433 -t"[||]" -r"[~~]"

  • June 18, 2012 at 9:04 am

    #1502278

    you are using a format file out of habit, is that it?

    can you run the same command without the format file?

    ie

    bcp egarchivedb.dbo.egpl_casemgmt_activity in R:\ArchivePurge\BCP_Mortgage_email_bodies.txt -T -S xxxxxxxxxxxxxxx\INST1,1433 -t"[||]" -r"[~~]" -e R:\ArchivePurge\errorfile.txt

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 18, 2012 at 9:10 am

    #1502286

    I thought I needed the format files because my data file has columns from two tables. I need to tell import #1 to handle fields 1-54 and ignore the last 5. Import #2 must handle fields 55-59 and ignore the first 54. Can I do that without format files?

  • June 19, 2012 at 7:27 am

    #1502823

    I have a resolution. I was able to get the import to work with the SSIS import wizard. Thanks for the help.

  • September 21, 2015 at 7:27 am

    #1828072

    Here is the solution with BCP command......

    !!bcp "dbo.TableName" in "D:\testData.txt" -c -Sservername -T -t"|" -r 0x0A

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply