Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Very Urgent Pls!!!import the text file data to sqlserver using bcp or dts

    Hi,

    I tried to load the text file data into sql server 7.0 using bcp and dts.I succeeded with one text file(ab20031006.txt,abdata has 4fields and 14000 rows) but I could not with other file(bib20031006.txt,bibdata has 52 fields and 70000 rows).It would be appreciated any one solve this pb immediately.


    DTS:
    after DTS Import Wizard.I selected next then I got error

    Error Source:Microsoft Data Transforamation Services Flat File Rowset provider
    Error Description:Row Delimiter not found

    filtype: ANSI SKIP ROWS 0
    ROW DELIMITER:{CR}{LF} FIRST ROW HAS COLUMN NAMES(CHECKED)
    TEXT QUALIFIER:NONE


    WHEN I TRY WITH
    ROW DELIMITER:TAB

    I'M GETTING THIS ERROR
    Error Source:Microsoft Data Transforamation Services Flat File Rowset provider
    Error Description:TOO MANY COLUMNS FOUND IN THIS CURRENT ROW;NON-WHITESPACE CHARACTERS WERE FOUND
    AFTER THE LAST DEFINED COLUMN'S DATA.

    DTS IMPORT WIZARD:select source tables

    when i tried with fixed field and check box checked, the sqlserver enterprise manager is exited.

    COPY DATA FROM BIB20031006 TO [MASTER].[DBO].[BIB20031006] STEP
    ERROR ATA SOURCE FOR ROW NUMBER 9677.ERRORS ENCOUNTERED SO FAR IN THIS TASK:1
    GENERAL ERROR -2147209197(80043013).
    TOO MANY COLUMNS FOUND IN THIS CURRENT ROW;NON-WHITESPACE CHARACTERS WERE FOUND AFTER THE LAST DEFINED
    COLUMN'S DATA.

    bcp

    OLD ONE
    C:\MSSQL7\Binn>bcp master..BIBDATA in c:\idocfile\new\BIB20031006.TXT -f
    c:\MSSQL7\Binn\bib52.fmt -SJAVADEV2-PC-NJ -Usa -P
    SQLState = S1000, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]Attempt to read unknown version of BC
    P format file

    NEW ONE
    C:\MSSQL7\Binn>bcp master..BIBDATA in c:\medsite\idocfile\new\BIB20031006.txt -c
    -F2 -t\t -r\n -e c:\mssql7\binn\bib52.fmt -SJAVADEV2-PC-NJ -Usa -P

    Starting copy...

    0 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.): total 330

    C:\MSSQL7\Binn>bcp master..bibdata in c:\medsite\idocfile\new\bib20031006.txt -c
    -t\t -r\n -e c:\mssql7\binn\bib52.fmt -SJAVADEV2-PC-NJ -Usa -P

    Starting copy...
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

    0 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.): total 5878

    Bulk Insert command
    bulk insert master..bibdata from 'e:\medsite\bib20031006.txt' with (formatfile='c:\mssql7\binn\bib52.fmt')
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 1, column 1 (recordtype).
    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.

    With DTS
    Error at destination for row no 72313.Errors encountered so far in this task:1.
    Could not allocate space for object 'IBIB20031006' in database 'Northwind' because the 'PRIMARY'filegroup is full
    error at row 9766

    when I Select tab:
    Error Description:
    too many columns found in the current row;non-whitespace characters were found after the last defined column's data
    Hi experts,


    I tried to download a file from ftpserver to sqlserver.I properly downloaded one text file(AB20031006.txt,ABDATA table has 4fields and 13843 records) with bcp but I could not do with other text file(BIB20031006.txt,BIBDATA table has 52 fields and 73000 records).


    my bcp command is:
    C:\MSSQL7\Binn>
    bcp master..BIBDATA in c:\idocfile\new\BIB20031006.TXT -fc:\MSSQL7\Binn\bib52.fmt -SJAVADEV2-PC -Usa -P

    I failed in DTS
    After selecting the text file,I checked delimited check box then I clicked on next button I'm getting error like Error:Row Delimiter not found.
    filtype: ANSI SKIP ROWS 0
    ROW DELIMITER:{CR}{LF} FIRST ROW HAS COLUMN NAMES(CHECKED)
    TEXT QUALIFIER:NONE

    Thanks
    ven[!]

  2. #2
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hello Ven,

    I tried to rebuild your problem with the data file you attached in your earlier posting (http://www.dbforums.com/t955508.html) on a SQL Server 2000 box.

    I used a text file source and a connection to SQL Server as destination and it worked. The properties of the text file were set as follows:

    filtype: ANSI
    SKIP ROWS 0
    ROW DELIMITER:{CR}{LF}
    FIRST ROW HAS COLUMN NAMES(CHECKED)
    TEXT QUALIFIER:"

    When I click on Next the system recognizes the fields.

    Greetings,
    Carsten

Posting Permissions

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