Home Forums SQL Server 2005 T-SQL (SS2K5) Bulk Insert - suddenly getting issues with EOL chracters? RE: Bulk Insert - suddenly getting issues with EOL chracters?

  • Stuart

    Many thanks for your reply. I have been doing investigation into this, and I'm getting some inconsistent findings.

    If I open the file in Excel or Notepad++ then it looks fine, each data row is represented as I would expect to see it and there is apparently a LF character at the end of each row. If, however, I open the file in normal Notepad, I get a very different result. If I turn off word wrap, I get what looks like a continuous string of data without any segregation of data rows. All that I get is a wrap at the 1024-character limit that Notepad seems to inflict on any long string. I presume this continuous-string behaviour is because Notepad doesn't recognse whatever character is being used as an end-of-line character?

    If I turn on word wrap, two issues manifest themselves. Firstly, some data fields containing text are split across two lines, even though Notepad hasn't reached it's natural 1024-character wrapping limit. An example would be:

    Field1,Field2,Field 3 which is a character description that should be

    displayed on one row but is split across two,Field4,Field5,..............

    The second issue is that not every new data row actually starts at the beginning of a line in the file - some of them just continue exactly after the end of the previous data row, something like this:

    Here,is,the,first,2000,character,long,data,row

    and,this,is,a,word,wrap

    Here,is,the,second,2000,character,long,data,row

    and,this,is,a,word,wrap

    Here,is,the,third,2000,character,long,data,row

    and,this,is,a,word,wrapAnd,this,is,actually,the,fourth,data,row,but

    it,continues,where,the,last,one,left,off

    They're both inconsistent issues as well - neither occur every nth row, and they don't always occur together. There can be any number of "good" data rows and then one issue, followed by two good rows and two bad ones.

    I'm just putting this down to some weird formatting coming out of the source system rom where the file is being generated. I have found out that it's not coming directly from SAP as I had thought, there's another piece of software generating this output, so I presume it's doing something odd when it generates the file. I'm going to try and find another way to create the file in the first place, to avoid the issue. I'd be interested though to hear any thoughts you might have anyway.

    Thanks for your help