problem with bcp format file

From: Tom L (tlanger_n05p4m_dls.net)
Date: 05/14/04

  • Next message: George Copeland: "Re: SQL Server/ADO/XP problem "query cannot be executed"" 
    Date: Fri, 14 May 2004 17:29:58 -0500

    My format file is:
    7.0
    11
    1 SQLCHAR 0 20 "|" 1 datCreated SQL_Latin1_General_Cp437_BIN
    2 SQLCHAR 0 80 "|" 2 txtEmail SQL_Latin1_General_Cp437_BIN
    3 SQLCHAR 0 50 "|" 3 txtName SQL_Latin1_General_Cp437_BIN
    4 SQLCHAR 0 50 "|" 4 txtAddress SQL_Latin1_General_Cp437_BIN
    5 SQLCHAR 0 50 "|" 5 txtAddress2 SQL_Latin1_General_Cp437_BIN
    6 SQLCHAR 0 50 "|" 6 txtCity SQL_Latin1_General_Cp437_BIN
    7 SQLCHAR 0 50 "|" 7 txtState SQL_Latin1_General_Cp437_BIN
    8 SQLCHAR 0 10 "|" 8 txtZip SQL_Latin1_General_Cp437_BIN
    9 SQLCHAR 0 2 "|" 9 txtCountry SQL_Latin1_General_Cp437_BIN
    10 SQLCHAR 0 20 "|" 10 txtPhone SQL_Latin1_General_Cp437_BIN
    11 SQLCHAR 0 15 "\r\n" 11 txtIP SQL_Latin1_General_Cp437_BIN

    sample data file: (enter after the ip address to indicate end of record..
    pipe used to indicate field.)

    5/14/2004
    3:01:22PM|asdf@asdf.com|Thomas+Laer|123+asdf+Drive|Suite+%23102|Lake+in+the+
    Hills|IL|20123|US|60343123312|20.0.5.146
    5/14/2004
    3:16:16PM|mrasdm@kjjndg.com|Thomas+Langer|123+Appleton|Suite+%23412|lake+in+
    the+Hills|IL|12346|us|5123123123|21.0.5.46
    5/14/2004
    3:26:32PM|masdf@sasdfasdy.com|Thomasasdf|123+afsd+DRIVE|Suite+123|Lake+in+th
    e+HIILLS|Il|10606|US|1231231231|6.80.5.6

    In query analyzer, I run:
    BULK INSERT tmpMembers FROM 'c:\2004051415.txt' WITH (FORMATFILE =
    'c:\ee2\memberdata.fmt')

    And it says:
    Server: Msg 4823, Level 16, State 1, Line 1
    Could not bulk insert. Invalid column number in format file
    'c:\ee2\memberdata.fmt'.

    And I say:
    Please help.. This SIMPLE problem is confusing the hell out of me, as I've
    use format files a handful of other times w/o problem. The table tmpMembers
    is the same table structure as it shows in the fmt file, same order and
    everything. The version should be good, as this is on a SQL 7 box. On SQL
    2k, using version 8.0, and with 9 fields in it, this worked fine - but I
    added 2 fields, and moved it on a sql7 box, and here I am <scratching head>

    Thanks!

  • Next message: George Copeland: "Re: SQL Server/ADO/XP problem "query cannot be executed""

    Relevant Pages

    • Re: Cannot fetch a row from OLE DB provider "BULK" for linked serv
      ... Bulk load: An unexpected end of file was encountered in the data file. ... Anyway, there was an error in the format file, at least in regards to ... This is probably when you need to look at SQL Server Integration Services, ...
      (microsoft.public.sqlserver.programming)
    • Re: CSV import failures in 2005.
      ... getitng string truncations and unexpected end of file. ... It could help to use BCP from SQL 2005 to avoid compatibility issues. ... probably better to generate the format file on the target server, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
      (microsoft.public.sqlserver.programming)
    • Re: BCP in having I/O error
      ... for the collation. ... In sysbase the bcp in works fine using the fromat file however, when i convert the format file for sql compatibility it get the error ...
      (microsoft.public.sqlserver.server)
    • Using SQL script with format file
      ... fields than the FMP table has. ... I have tested my script in a SQL ... I am assuming that my format file is incorrect. ... have a sample format file for SQL script that works with FMP that I ...
      (comp.databases.filemaker)
    • Re: Appending Data via BCP
      ... I posted an example of a format file. ... of the file you are importing. ... Links for SQL Server Books Online: ...
      (microsoft.public.sqlserver.tools)
    Loading