Post reply

bcp and bulk insert

  • December 16, 2008 at 8:44 pm

    #216599

    Hi Folks,

    I am trying to load a file using bcp

    command is bcp test..INCOMINGBUFFER2 in "c:\file.txt" -T

    -c -t "|"

    when I try I get the error unexpected EOF encountered in BCP data-file.

    BULK INSERT INCOMINGBUFFER2

    FROM 'c:\bcpBEF.tmp'

    WITH (FIELDTERMINATOR='|')

    GO

    When I try it in BULK insert it works with some warnings

    Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.

    Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.

    the file is:

    2887707650|09/25/2008 14:16:46|1026|2887707812|2887706119|http|mail|/helpdesk/rep/new.asp||80|149||3530|88685|204||3|LDAP|mondial-assistance.au.local|OU=Customer Service,OU=IT&T,OU=Corporate,DC=mondial-assistance,DC=au,DC=local|Josh Ridgway

    the table schema is

    CREATE TABLE [dbo].[INCOMINGBUFFER2](

    [SOURCE_SERVER_IP] [bigint] NULL,

    [DATE_TIME] [datetime] NOT NULL,

    [DISPOSITION_CODE] [smallint] NULL,

    [SOURCE_IP] [bigint] NULL,

    [DESTINATION_IP] [bigint] NULL,

    [PROTOCOL_NAME] [nvarchar](255) NULL,

    [nvarchar](255) NULL,

    [URL_FILE_PATH] [nvarchar](1000) NULL,

    [URL_FILE_EXT] [nvarchar](255) NULL,

    [PORT] [int] NULL,

    [CATEGORY] [int] NULL,

    [FILE_TYPE_NAME] [nvarchar](255) NULL,

    [BYTES_SENT] [nvarchar](15) NULL,

    [BYTES_RECEIVED] [nvarchar](15) NULL,

    [DURATION] [int] NULL,

    [KEYWORD] [nvarchar](255) NULL,

    [HITS] [int] NULL,

    [USER_PATH_NAMESPACE] [nvarchar](50) NULL,

    [USER_PATH_CATALOG_SERVER] [nvarchar](255) NULL,

    [USER_PATH_DOMAIN] [nvarchar](255) NULL,

    [USER_PATH_LOGIN_NAME] [nvarchar](255) NULL

    ) ON [PRIMARY]

    any ideas why bcp is not working would be greatly appreciated!

  • December 21, 2008 at 8:55 am

    #915855

    The one time I had that same error, it was because my delimiter value was contained in one of my varchar columns.

    Try a ridiculous delimiter, like

    bcp test..INCOMINGBUFFER2 in "c:\file.txt" -T -c -t "|\~!"

    Regards,

    Michelle Ufford
    SQLFool.com - Adventures in SQL Tuning

  • December 21, 2008 at 8:56 am

    #915856

    Of course, you could always query your column to see if the delimiter exists there, too. 🙂

    Regards,

    Michelle Ufford
    SQLFool.com - Adventures in SQL Tuning

  • December 21, 2008 at 2:53 pm

    #915907

    Is it actually a unicode file you're trying to import? IF it is, try changing -c to -w in BCP and adding the [font="Arial Black"]DATAFILETYPE = 'widechar'[/font] to the Bulk Insert.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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