none
skipping header in bcp

    Pergunta

  • Hi,

    I am using bcp in our application to import data files and to skip header and footer rows i am using -F and -L switches. This is working for tab delimited files but not for fixed length files because the header row is just of 10 to 20 characters where as the detail record spans for few hundred lines. Doing some research lead me to the conclusion that -F and-L switches in bcp are not intended to skip header and footers as it looks at the field terminators.

    But i am like too far ahead in the game and want to find is there anything that i can do to make it work instead of changing the entire program?Any help is much appreciated. Is it possible to skip header and footer using format files?

    Thanks

    Mahesh

    sexta-feira, 5 de agosto de 2011 02:43

Respostas

  • This is correct. -F is only a mean to skip records in the input file. The BCP terminology is a bit unfortunate. They talk about "rows" when they really mean "records". Once the data is in a table, you can talk about rows, but as long it's a file, it's better to talk about records.

    And more specifically "row" here has nothing to with lines in a file. BCP does not read a text file - it reads a binary stream which is assumed to have a homogeneous layout.

    So when the file format does not align with this paradigm, you cannot use BCP as-is.

    One option is to write your own program that uses the bulk-copy API in ODBC where you can pass data in variables. That is, you take responsibility to read the file yourself. This may also be possible with the SqlBulkCopy class in .Net.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marcado como Resposta KJian_ sexta-feira, 12 de agosto de 2011 09:03
    sexta-feira, 5 de agosto de 2011 07:50

Todas as Respostas

  • So you tried -F 2 and it didn't work? 

     

    Is the header row terminated the same way as the rest of the row? -F only looks at the row terminator I believe.

     


    sexta-feira, 5 de agosto de 2011 02:51
  • yes i used -F2 to skip header row. Also the row delimiter is same in both header and detail record. When i make the header almsot equivalent to the length of the detail record just by padding spaces.., skipping works fine but iam not supposed to do that.
    Thanks


    Mahesh
    sexta-feira, 5 de agosto de 2011 03:33
  • How about you use the SQLCMD utility with the -h option. I dont think either BULK INSERT or the BCP support skipping the headers.

     

     


    Nothing is Permanent... even Knowledge....
    My Blog
    sexta-feira, 5 de agosto de 2011 04:13
  • > How about you use the SQLCMD utility with the -h option. I dont think either BULK INSERT or the BCP support skipping the headers.

    And SQLCMD does not support loading data at all!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    sexta-feira, 5 de agosto de 2011 07:34
  • This is correct. -F is only a mean to skip records in the input file. The BCP terminology is a bit unfortunate. They talk about "rows" when they really mean "records". Once the data is in a table, you can talk about rows, but as long it's a file, it's better to talk about records.

    And more specifically "row" here has nothing to with lines in a file. BCP does not read a text file - it reads a binary stream which is assumed to have a homogeneous layout.

    So when the file format does not align with this paradigm, you cannot use BCP as-is.

    One option is to write your own program that uses the bulk-copy API in ODBC where you can pass data in variables. That is, you take responsibility to read the file yourself. This may also be possible with the SqlBulkCopy class in .Net.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marcado como Resposta KJian_ sexta-feira, 12 de agosto de 2011 09:03
    sexta-feira, 5 de agosto de 2011 07:50
  • > How about you use the SQLCMD utility with the -h option. I dont think either BULK INSERT or the BCP support skipping the headers.

    And SQLCMD does not support loading data at all!


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

    arrrrrrrrrrghhhhh... Yes you are correct... I read the question as export rather than import and thought he can use a simple select statement with the SQLCMD....

    Yes you are correct we cannot import data with the SQLCMD....


    Nothing is Permanent... even Knowledge....
    My Blog
    sexta-feira, 5 de agosto de 2011 07:50
  • Hi,

    I am not sure whether this will work or not but you can create a format file by specifying fixed length as the number of whitespaces + the data field and last row terminated as \r\n,

    Then you can use the bulk insert as

    bulk insert <table> from '<path of data file>'

    with

    (

    firstrow=2,

    formatfile='<path of formatfile>'

    )


    Thanks and regards, Rishabh , Microsoft Community Contributor
    sexta-feira, 5 de agosto de 2011 07:50
  • Consider using the SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    Sky is the limit with SSIS!


    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: SQL Programming & Database Design Using Microsoft SQL Server 2012


    quarta-feira, 10 de agosto de 2011 14:03
    Moderador