none
BULK INSERT with extra column delineators in last column. RRS feed

  • Question

  • Hi all,

    Interesting issue here (well, interesting to me!)

    I have a stored procedure that BULK INSERTS a csv file. The csv file is generated by a third party piece of software. The csv file has a certain number of columns delineated by commas (no text qualifiers) and the last column of the output has (yes, you guessed it) a variable number of commas in the string. Still, that's what we've got to work with. Just as an example of what I'm talking about, the file will be something like this:

    Col001,Col002,Col003<CR><LF>

    A,B,C<CR><LF>

    D,,F<CR><LF>

    G,H,I,x,y,z<CR><LF>

    ,,<CR><LF>

    J,K,L<CR><LF>

    So, I have a last column of varchar(8000) NULL and I BULK INSERT using comma as column delineator and \n as row delineator. And this works! Or at least it works for:

    SQL2005 (E[nterprise],S[tandard],[D[eveloper],[e]X[press]) on Server 2003 (x32 & x64 bit)

    SQL2008 (E[nterprise],S[tandard],[D[eveloper],[e]X[press]) on Server 2003 (x32 & x64 bit)

    SQL2008 (E[nterprise],S[tandard],[D[eveloper],[e]X[press]) on Server 2008 (x32 & x64 bit)

    SQL2008 (E[nterprise],S[tandard],[D[eveloper],[e]X[press]) on Server 2008R2 (x32 & x64 bit)

    SQL2008R2 (E[nterprise],S[tandard],[D[eveloper],[e]X[press]) on Server 2008 (x32 & x64 bit)

    SQL2008R2 (S[tandard],[D[eveloper],[e]X[press]) on Server 2008R2 (x32 & x64 bit)

    And then this week I tried and failed to use it on:

    SQL2008R2 (E[nterprise]) on Server 2012 (x64 bit).

    Hits the first extra comma and bombs out.

    Now, ideally I'd have the same code running in all environments.

    So, questions:

    Is there a setting I'm missing (sp_configure gives same results as all other instances) that could cause the different behaviour?

    Is this unique to SQL Server 2008R2 Enterprise?

    Is this unique to SQL Server 2008R2 Enterprise running on Server 2012?

    As I say, I'd rather keep the code consistent if I can. Also, I'd rather avoid format files, if possible. I also need those values from the final column as a further procedure downstream strips those values out and normalises them using the commas to split them.

    Any ideas how to fix it?

    Cheers,

    J


    JCEH

    Tuesday, February 11, 2014 4:10 PM

Answers

  • Cracked it.

    Was a duff output from third party software. <CR><LF> missing from a row.

    Well. That's a relief. Of course, what caused that to go wrong but frankly, not my problem!


    JCEH

    • Marked as answer by JCEH Tuesday, February 11, 2014 4:58 PM
    Tuesday, February 11, 2014 4:57 PM

All replies

  • I can answer 1 question. It isn't SQL.

    Copying the file to another server (Win2008R2x64) running SQL2008R2D and same problem! So, something different about the Win2012x64 file, even though the file was generated by the same third party software.

    Weird!


    JCEH

    Tuesday, February 11, 2014 4:23 PM
  • Cracked it.

    Was a duff output from third party software. <CR><LF> missing from a row.

    Well. That's a relief. Of course, what caused that to go wrong but frankly, not my problem!


    JCEH

    • Marked as answer by JCEH Tuesday, February 11, 2014 4:58 PM
    Tuesday, February 11, 2014 4:57 PM