Re: problem with bcp format file

From: WangKhar (Wangkhar_at_yahoo.com)
Date: 05/18/04 

Date: 18 May 2004 02:19:54 -0700

Your data has 12 fields.
> 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
Add another to your format file.

Wang.

"Tom L" <tlanger_n05p4m_dls.net> wrote in message news:<9sadnZ7kRL8Q1TjdRVn-hg@dls.net>...
> 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!

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