Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Posts
    51

    Angry Unanswered: bulk insert is drinving me mad!!!

    I am probably a putz, but the following is got me stumped. I have a text file called file001.txt that looks like this:

    1,2,3,4,5,6,7,8,9|1,2,3,4,5,6,7,8,9|

    When I use the following bulk insert statement everything works and I have no problems at all:

    BULK INSERT table001
    FROM 'C:\file001.txt'
    WITH
    (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '|'
    )

    But, if I try to do this it fails:

    declare @fdel varchar
    declare @rdel varchar
    set @fdel = ','
    set @rdel = '|'
    BULK INSERT table001
    FROM 'C:\file001.txt'
    WITH
    (
    FIELDTERMINATOR = @fdel,
    ROWTERMINATOR = @rdel
    )

    Server: Msg 4832, Level 16, State 1, Line 1
    Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
    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. The statement has been terminated.

    What the heck is happening? Please help!!!!
    When in doubt just ask your self,
    -WWSBD?-
    (what would Sponge Bob do?)

  2. #2
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    Are you sure that BULK INSERT allows variables to be used for terminators ?

    Try:
    declare @SqlStr varchar(8000)
    declare @fdel varchar
    declare @rdel varchar
    set @fdel = ','
    set @rdel = '|'
    set @SqlStr='
    BULK INSERT table001
    FROM 'C:\file001.txt'
    WITH
    (
    FIELDTERMINATOR = '+@fdel+',
    ROWTERMINATOR = '+@rdel+'
    )
    exec (SqlSr)

    ?
    --
    kukuk

  3. #3
    Join Date
    Sep 2002
    Posts
    51

    hey kukuk

    Good morning kukuk,
    That did not get it, but thanks. I farted around with it a little and got the following to work - and its closer to what I eventually want to accomplish anyway:

    declare @sql varchar(8000)
    select @sql = "BULK INSERT table001
    FROM 'C:\file001.txt'
    WITH
    (
    FIELDTERMINATOR = '" + (select fld_delimiter from datadef where description = 'BFH') + "',
    ROWTERMINATOR = '" + (select row_delimiter from datadef where description = 'BFH') + "'
    )"
    exec(@sql)

    While your response wasnt an excat fix, it did allow me to break out of the endless cycle of MADNESS that totally blinded me to a simple solution last night.
    -thanks again
    When in doubt just ask your self,
    -WWSBD?-
    (what would Sponge Bob do?)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •