Bulk insert fails when we use a format file

  • Hi all,

    I am having a bit of bother with Bulk Insert. Here are the details of what I am doing

    1. From command line I run "bcp {DBName}.dbo.tblUsers out c:\data.csv -S"{Server\Instance}" -U"{username}" -P"{password}" -w -t"," -r"/n"' "

    2. From SQL Query Analyser I run "

    BULK INSERT inrsSQL.dbo.tblUsersTest

       FROM 'c:\data.CSV'

       WITH

         ( 

     FORMATFILE = 'C:\bcp.fmt'

          )

    "

    This doesn't work. It produces the error message "

    Server: Msg 4866, Level 17, State 66, Line 1

    Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.

    Server: Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.

    OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:  The provider did not give any information about the error.].

    The statement has been terminated.

    "

    When I run

    BULK INSERT inrsSQL.dbo.tblUsersTest

       FROM 'c:\data.CSV'

       WITH

         ( 

            FIELDTERMINATOR = ',',

            ROWTERMINATOR = '\n'

          )

    I get the good old "212 rows affected" message.

    I don't understand why the format file method isn't working. The CSV and FMT files are produced from the bcp command so they should be compatible for the reverse operation with Bulk Insert.

    For completeness of info here is the FMT file contents:"

     8.0

    5

    1       SQLINT        1       4       ","                       1     ID           ""

    2       SQLNCHAR      0       0       ","                       2     Forename     SQL_Latin1_General_CP1_CI_AS

    3       SQLNCHAR      0       0       ","                       3     Surname      SQL_Latin1_General_CP1_CI_AS

    4       SQLNCHAR      0       0       ","                       4     Email        SQL_Latin1_General_CP1_CI_AS

    5       SQLNCHAR      0       0       "\r\n"                    5     Password     SQL_Latin1_General_CP1_CI_AS

    "

    In the table, column 1 is an integer "

    ID is an identity integer with increment of 1 and the rest are all nVarChar(150)

    Your help here would be greatly appreciated.

    Thanks

    Robert

  • Not sure about the error but because your file is character you cannot use SQLINT for field definitions although BCP will output this in a fmt file by default.

    Try this

    8.0

    5

    1  SQLCHAR  0  12   ","     1  ID        ""

    2  SQLCHAR  0  150  ","     2  Forename  SQL_Latin1_General_CP1_CI_AS

    3  SQLCHAR  0  150  ","     3  Surname   SQL_Latin1_General_CP1_CI_AS

    4  SQLCHAR  0  150  ","     4  Email     SQL_Latin1_General_CP1_CI_AS

    5  SQLCHAR  0  150  "\r\n"  5  Password  SQL_Latin1_General_CP1_CI_AS

    p.s. added after posting

    The input ID value will be ignored as the column in the dest table is identity

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Nice one bro. That worked a treat!

    I understand about the 150 values but why is the length of the int not 4?

    Ta muchly

    Robert

    PS (also added after posting )

    Actually, I looked again at your post and then noticed you changed the SQLINT to SQLCHAR. When I changed the format file I neglected to do so. It seems that changing the length of the fields was sufficient.

    Cheers

    Robert

  • Not sure how that got there I think BCP did it when I experimented with creating output fmt files

    After experimenting further I found that the lengths do not seem to matter, I managed to get it to work with all zero lengths

    Your error though was due to the prefix length of 1 on the first column (ID), by setting this to zero was all that was needed for your fmt file to work

    Because ID in your table is IDENTITY then it does not matter what the definition of the column is as it will be ignored. However if that column was not identity and was required (or any input int column) then you would have to use SQLCHAR otherwise you would get an incoorect value.

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply