locked
bcp format file for fixed length flat file RRS feed

  • Question

  • What is the format for a fixed length format file for bcp. I have a sample attached before which is for a tab delimited, but I was not able to figure out how to convert it to fixed length (or generated using bcp as fixed length).

    Eventually we will have a fixed length file with many columns and some of the input columns will be skipped during bcp import into sql tables, that should not be a problem using a format file right?

    Thank you

     

    10.0
    10
    1       SQLCHAR             0       5       "\t"     1     CntyCd                                                     SQL_Latin1_General_CP1_CI_AS
    2       SQLCHAR             0       12      "\t"     2     BatchDt                                                    ""
    3       SQLCHAR             0       12      "\t"     3     BatchSeq                                                   ""
    4       SQLCHAR             0       1       "\t"     4     AbsenteeIrisCd                                             SQL_Latin1_General_CP1_CI_AS
    5       SQLCHAR             0       3       "\t"     5     AcCd                                                       SQL_Latin1_General_CP1_CI_AS
    6       SQLCHAR             0       15      "\t"     6     AcctNbr                                                    SQL_Latin1_General_CP1_CI_AS
    7       SQLCHAR             0       41      "\t"     7     AdjGrossSqFtNbr                                            ""
    8       SQLCHAR             0       20      "\t"     8     AgricDistCntyCd                                            SQL_Latin1_General_CP1_CI_AS
    9       SQLCHAR             0       1       "\t"     9     ApnPortionLotInd                                           SQL_Latin1_General_CP1_CI_AS
    10      SQLCHAR             0       1       "\t"     10    ApnResearchInd                                             SQL_Latin1_General_CP1_CI_AS
    
    



    Gokhan Varol
    Monday, January 23, 2012 7:00 PM

Answers

  • Hi Gokhan,

    In your attached sample format file the "\t" is the field terminator . To change this from tab delimited to fixed length, just replace the "\t" with "". Then, the number listed in column 4 will take effect. Here is a excellent link to help understand a NON-XML format file : http://msdn.microsoft.com/en-us/library/ms191479(v=sql.90).aspx . As you already know that you can delimit date fields by either a character set or by a specifield field length, but you might also want to consider a combination of both. Here is a microsoft kb article on the proper way of using the field length column and the delimiter column ; http://support.microsoft.com/kb/67409

    "

    Q. What is the proper way to use the field length column and the
       delimiter column? How can they work with or against each other?
    
    A. Data can be field delimited either by a character set or by a
       specified fixed length. It can also be delimited using a
       combination of both character set and fixed length. However,
       caution must be taken. The BCP utility will take the first form of
       the delimiter it finds. For example, if the field is comma
       delimited and all elements in the field have at least 10
       characters, and the length is set at 8, BCP will copy only the
       first 8 characters of all elements in that field. This mistake will
       most likely result in an error message from BCP. It is easiest to
       use the default option of fixed-length terminators for native
       format files.

    "

    you can easily skip any column during bcp import by putting the value of 0 into the 6th column, which is the column order number, then the 7th column can continue to have the column name. On the following link, you will find an example of how to skip columns ;

    http://msdn.microsoft.com/en-us/library/ms187908.aspx

     


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.
    • Proposed as answer by Sanil Mhatre Monday, January 23, 2012 11:51 PM
    • Marked as answer by GV1973 Tuesday, January 24, 2012 2:23 AM
    Monday, January 23, 2012 11:51 PM