locked
Bulk Insert Row Terminator issues RRS feed

  • Question

  • Hello,

    I have a CSV file that I am trying to bulk load into a temp table. The data in the file is all jumbled together, as in, there does not appear to be a row terminator. However, I do see a bunch of little rectangular boxes that I assume are the row terminators. When I run the bulk insert, the data is treated as one string. For example... If I have 10 columns in the table, the 10 columns will be populated, but the remainder of the data is dumped into the last column.

    The guy who produced these files is claiming that I am doing something wrong, which I don't believe.

    Here are the row terminators I have used so far that haven't worked. \r, \r\n, \n, \n\r, CR\LF, \n

    Any help would be appreciated...

    Dave


    Dave SQL Developer
    Friday, November 5, 2010 2:27 PM

Answers

  • Guess I am too late :) ... if anybody is still looking for an answer

    ROWTERMINATOR = '0x0a'

    • Proposed as answer by Ali Razeghi Tuesday, May 1, 2012 5:50 AM
    • Marked as answer by Naomi N Tuesday, May 1, 2012 11:54 AM
    Friday, February 10, 2012 4:15 PM
  • Hello, Dave

    I believe your CSV file has only a LF at the end of each line (it probably comes from a Unix or Mac computer), instead of a CR and a LF (like it is usual on a Windows computer). In this case the correct row terminator would be \n. However, Books Online topic "bcp utility" Books Online topic Specifying Field and Row Terminators says:

    "When you use bcp interactively and specify \n (newline) as the row terminator, bcp automatically prefixes it with a \r (carriage return) character, which results in a row terminator of \r\n."

    Therefore you should save the format file using BCP, then edit the format file manually and replace \r\n with a \n and then use the modified format file (specifying the -f parameter) to import the CSV file.

    Razvan

    • Marked as answer by Ai-hua Qiu Friday, November 12, 2010 8:12 AM
    Saturday, November 6, 2010 8:28 AM
  • Google "Hex Editor". Here is one of the free downloads, but unfortunately doesn't tell, which OS versions it supports

    http://www.physics.ohio-state.edu/~prewett/hexedit/


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Ai-hua Qiu Friday, November 12, 2010 8:12 AM
    Friday, November 5, 2010 6:44 PM
  • > I have never used a hex editor, so I do not know. Do you have a link I can use?

    There are plenty of shareware text editors out there. About all them supports viewing files in hex. For instance, Textpad, http://www.textpad.com/

    In fact, if you download and install TextPad, and the open the file in TextPad with default settings, and then right-click and select Properties, you will get some information about character set and file format. (You can select Binary from the Open dialog.)

    In your initial post, you mention rectancular boxes. In which program do you see them?

    If you can put the file on a web site somewhere, we can download it to take a look.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by Ai-hua Qiu Friday, November 12, 2010 8:12 AM
    Friday, November 5, 2010 10:28 PM
  • Any progress?

    Open the file in Notepad, check for line structure and save it again.

    If bcp, BULK INSERT, and SSIS Import/Export Wizard still fail, check out the ultimate ETL solution - Command Prompt TYPE command upload to table -  at the following page:

    http://sqlusa.com/bestpractices2005/notepad/


    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

    • Marked as answer by Ai-hua Qiu Friday, November 12, 2010 8:12 AM
    • Edited by Kalman Toth Wednesday, November 8, 2017 10:34 PM
    Thursday, November 11, 2010 7:07 AM

All replies

  • Have a look at the hex codes for the terminator (ascii or unicode). Yoyu can do this by loading into a hex editor or by using ascii or unicode to look at the imported data.

    If the source is utf-8 then you can't use bulk insert as MS removed support for it - has to be ssis.

    Another option is to see if SSIS recognises the terminator if you open the file.

    Friday, November 5, 2010 2:48 PM
  • Can someone provide me with a list of row terminators that can be used in Bulk Insert????

     

    Thanks


    Dave SQL Developer
    Friday, November 5, 2010 3:16 PM
  • Almost anything. You can use the hex codes to specify the terminator.

    The issue is usually whether it can handle the code pages for the data.

    Friday, November 5, 2010 3:18 PM
  • Do you have a link for this???? I have never heard of this before....

    I ran the file through Import/Export wizard in SSIS. SSIS sees the row terminator as {CR}{LF}. What is that translated in Bulk Insert?

    Dave SQL Developer
    Friday, November 5, 2010 3:23 PM
  • It's the default. char(13)+char(10)

    Could be that there is something else messing it up. What does ssis say about the codepage?

    I suspect it's ascii as it has a crlf as terminator.

     

    • Proposed as answer by cyjohns Sunday, August 21, 2016 3:12 PM
    Friday, November 5, 2010 3:29 PM
  • To specify the rowterminator in hex

    declare @s varchar(4000)

    select @s = 'bulk insert mytbl from ''c:\myfile'' with (rowterminator=' + char(13)+char(10) + ')'

    exec (@s)

     

    I'm pretty sure you can specify the hex codes directly without resorting to dynamic sql but I can never remember how it's done.

    Friday, November 5, 2010 3:32 PM
  • To be honest, I am surprised that no one seems to know this. 

    SSIS sees this row delimiter as {CR}{LF} in the import export wizard, so the csv file is correct. What is the translation of that row delimiter in a Bulk Insert statement?

    Code Snippet:

    BULK INSERT [#TEMP_TABLE]
            FROM 'C:\FileName.csv'
               WITH
                  (
                     FIELDTERMINATOR = ',',
                     ROWTERMINATOR = '\r\n',
                     FIRSTROW=2
                  )


    Dave SQL Developer
    Friday, November 5, 2010 3:33 PM
  • Think most people know that is the default - it's in bol - but you said it doesn't work in your case.
    Friday, November 5, 2010 3:45 PM
  • If it's the default why is it not working? I have even tried this not specifying the row terminator...

    Dave SQL Developer
    Friday, November 5, 2010 3:46 PM
  • If it's the default why is it not working? I have even tried this not specifying the row terminator...

    Dave SQL Developer


    suspect it's due to to something in the file that is invalid (or bulk insert thinks it's invalid), it's not an ascii file or the the terminator is not really 1310.

    Have you looked at the data in a hex editor?

    Friday, November 5, 2010 5:24 PM
  • I have never used a hex editor, so I do not know. Do you have a link I can use?

     

    Thanks for all your responses


    Dave SQL Developer
    Friday, November 5, 2010 6:29 PM
  • Google "Hex Editor". Here is one of the free downloads, but unfortunately doesn't tell, which OS versions it supports

    http://www.physics.ohio-state.edu/~prewett/hexedit/


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Ai-hua Qiu Friday, November 12, 2010 8:12 AM
    Friday, November 5, 2010 6:44 PM
  • > I have never used a hex editor, so I do not know. Do you have a link I can use?

    There are plenty of shareware text editors out there. About all them supports viewing files in hex. For instance, Textpad, http://www.textpad.com/

    In fact, if you download and install TextPad, and the open the file in TextPad with default settings, and then right-click and select Properties, you will get some information about character set and file format. (You can select Binary from the Open dialog.)

    In your initial post, you mention rectancular boxes. In which program do you see them?

    If you can put the file on a web site somewhere, we can download it to take a look.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by Ai-hua Qiu Friday, November 12, 2010 8:12 AM
    Friday, November 5, 2010 10:28 PM
  • Hello, Dave

    I believe your CSV file has only a LF at the end of each line (it probably comes from a Unix or Mac computer), instead of a CR and a LF (like it is usual on a Windows computer). In this case the correct row terminator would be \n. However, Books Online topic "bcp utility" Books Online topic Specifying Field and Row Terminators says:

    "When you use bcp interactively and specify \n (newline) as the row terminator, bcp automatically prefixes it with a \r (carriage return) character, which results in a row terminator of \r\n."

    Therefore you should save the format file using BCP, then edit the format file manually and replace \r\n with a \n and then use the modified format file (specifying the -f parameter) to import the CSV file.

    Razvan

    • Marked as answer by Ai-hua Qiu Friday, November 12, 2010 8:12 AM
    Saturday, November 6, 2010 8:28 AM
  • Any progress?

    Open the file in Notepad, check for line structure and save it again.

    If bcp, BULK INSERT, and SSIS Import/Export Wizard still fail, check out the ultimate ETL solution - Command Prompt TYPE command upload to table -  at the following page:

    http://sqlusa.com/bestpractices2005/notepad/


    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

    • Marked as answer by Ai-hua Qiu Friday, November 12, 2010 8:12 AM
    • Edited by Kalman Toth Wednesday, November 8, 2017 10:34 PM
    Thursday, November 11, 2010 7:07 AM
  • Guess I am too late :) ... if anybody is still looking for an answer

    ROWTERMINATOR = '0x0a'

    • Proposed as answer by Ali Razeghi Tuesday, May 1, 2012 5:50 AM
    • Marked as answer by Naomi N Tuesday, May 1, 2012 11:54 AM
    Friday, February 10, 2012 4:15 PM
  • Thank you Comma 126!  That was exactly what I needed after a 13 hour day at the office :)

    Support my SQL MCM & Internal Training blog on AliRazeghi.Com.  Have a tough I.T. job and you need to talk to someone with no obligation?  Contact my certified associates and I at Rosonco.com!

    If you find a question answered please click 'mark as answer' or 'vote as helpful'.  This will help other users find answers quickly.  Thanks for visiting!

    Tuesday, May 1, 2012 5:49 AM
  • HI,

    I'm still looking for a solution, My file is in Unicode and without a formatfile your solution or char(10) works perfectly.

    But I need a formatfile in my solution.

    With <FIELD ID="24" xsi:type="CharTerm" TERMINATOR='\n\0' MAX_LENGTH="15"  />

    always having:

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 ().

    Any help would be appreciated...

    Regards,

    Friday, August 3, 2012 3:25 AM
  • I suggest that you start a new thread. In that post, it would help if you included:

    1) CREATE TABLE statement for your target table.
    2) Your format file.
    3) The SQL statement you are using to read the file.
    4) A few sample lines of the data.

    The last point is a little tricky. If you just post some lines into the post, you may lose bytes that causes your problem. So if you have a possibility to put the sample file on a link somewhere, that's even better, but if that is a hassle for you put the sample data in the file first. It may still be sufficient.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, August 3, 2012 7:21 AM
  • I was having a similar issues with bulk insert using \r\n. As it turns out, I did need to switch it to just \n. 
    Thursday, August 30, 2012 3:33 PM
  • Hey Comma,

    ROWTERMINATOR = '0x0a' wrked for my req, But what does it mean?


    Please have look on the comment

    Wednesday, January 30, 2013 11:13 AM
  • Character 10 (0x0a in hex) is line feed, also denoted as \n. The problem is that if you specify \n as ROWTERMINATOR, SQL Server takes this to mean \r\n, which is the standard line ending on Windows. (\n is the end-of-line character on Unix.)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, January 30, 2013 11:20 AM
  • Thanks ernald.

    Please have look on the comment

    Wednesday, January 30, 2013 11:36 AM
  • Great!
    Wednesday, July 31, 2013 4:06 PM
  • was not too late...it solved my problem :-)

    Thanks !

    Tuesday, August 6, 2013 9:41 AM
  • Many thanks,  my insert works like a charm now, it was oddly just truncated the first character of every row,

    except the first before adding changing from \N to '0x0a'   

    Thursday, April 3, 2014 11:05 PM
  • Not for me! You are a GENIUS !! I tried everything, I was about to ask for a new file or attempt to edit the existing one.

    NICE ANSWER!!!

    Wednesday, June 24, 2015 6:35 PM
  • It's very strange but it's working... Thank you very much.
    Monday, January 11, 2016 10:58 AM
  • Thanks a lot (Y) :D .. I was searching for this for about 3 hours ! :D
    Tuesday, February 9, 2016 9:00 AM
  • Congrats!!! As far as I saw, this is f. non documented! 

    Thank you!


    Lucas Ponzo - Architect/Analyst/DBA/Developer

    Wednesday, March 9, 2016 9:44 AM
  • this solution worked for me.  thx

    It's easy to sign documents with Dousign

    Friday, August 19, 2016 5:56 PM
  • Hi Guys

    I know it is an old post, but I come to face the same problem when generating format file using -t \n option with BCP utility.

    Here is how to escape this behaviour of BCP:

    bcp <<sometable>> format nul -T -c -f <<sometable>>_c.fmt -S <<someserver>> -T -d <<somedatabase>> -r 0x0a -t;

    NB:
    there is no single/double quotes in the litteral 0x0a
    <<sometable>> , <<someserver>> and <<somedatabase>>  are place holders of what they speak for.

    Hope this helps.

    Wednesday, January 18, 2017 2:31 PM
  • You are a god send, thank you so very much !!! I'm very pleased that a post from 5 years ago can help me.
    Tuesday, February 7, 2017 3:59 PM
  • Hi Dave , did you get the solution. Iam also struggling with the same issue. my bulk insert query is not working with the text file having CRLF as row delimeter.
    Wednesday, June 19, 2019 5:15 PM
  • Hi APT_0202,

    You can try to use Notepad++ editor to check your text file line break character(s).

    Please see its screen shot below:

    Wednesday, June 19, 2019 7:14 PM
  • Hi Dave , did you get the solution. Iam also struggling with the same issue. my bulk insert query is not working with the text file having CRLF as row delimeter.

    Don't piggyback to old threads, but start a new thread describing your problem from start to end. While the symptoms may be the same, your actual problem and solution may be different.

    For problems with bulk import, it helps if you upload a sample file which exhibits the problem somewhere and post the link. (Need to see the exact bits, so you cannot include it in a post.) We also need to see your command and any format file that you use.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, June 19, 2019 10:00 PM