import text file data to sqlserver using bcp,dts | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

import text file data to sqlserver using bcp,dts

Hi,<br /><br />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.<br /><br /><br />DTS:<br />after DTS Import Wizard.I selected next then I got error<br /><br />Error Source:Microsoft Data Transforamation Services Flat File Rowset provider<br />Error Description:Row Delimiter not found<br /><br />filtype: ANSISKIP ROWS 0<br />ROW DELIMITER:{CR}{LF} FIRST ROW HAS COLUMN NAMES(CHECKED)<br />TEXT QUALIFIER:NONE<br /><br /><br />WHEN I TRY WITH<br />ROW DELIMITER:TAB<br /><br />I’M GETTING THIS ERROR<br />Error Source:Microsoft Data Transforamation Services Flat File Rowset provider<br />Error Description:TOO MANY COLUMNS FOUND IN THIS CURRENT ROW;NON-WHITESPACE CHARACTERS WERE FOUND <br />AFTER THE LAST DEFINED COLUMN’S DATA.<br /><br />DTS IMPORT WIZARD<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />elect source tables <br /><br />when i tried with fixed field and check box checked, the sqlserver enterprise manager is exited.<br /><br />COPY DATA FROM BIB20031006 TO [MASTER].[DBO].[BIB20031006] STEP<br />ERROR ATA SOURCE FOR ROW NUMBER 9677.ERRORS ENCOUNTERED SO FAR IN THIS TASK:1<br />GENERAL ERROR -2147209197(80043013).<br />TOO MANY COLUMNS FOUND IN THIS CURRENT ROW;NON-WHITESPACE CHARACTERS WERE FOUND AFTER THE LAST DEFINED<br />COLUMN’S DATA.<br /><br />bcp<br /><br />OLD ONE<br />C:MSSQL7Binn&gt;bcp master..BIBDATA in c:idocfile
ewBIB20031006.TXT -f<br />c:MSSQL7Binnib52.fmt -SJAVADEV2-PC-NJ -Usa -P<br />SQLState = S1000, NativeError = 0<br />Error = [Microsoft][ODBC SQL Server Driver]Attempt to read unknown version of BC<br />P format file<br /><br />NEW ONE<br />C:MSSQL7Binn&gt;bcp master..BIBDATA in c:medsiteidocfile
ewBIB20031006.txt -c<br /> -F2 -t -r
-e c:mssql7innib52.fmt -SJAVADEV2-PC-NJ -Usa -P<br /><br />Starting copy…<br /><br />0 rows copied.<br />Network packet size (bytes): 4096<br />Clock Time (ms.): total 330<br /><br />C:MSSQL7Binn&gt;bcp master..bibdata in c:medsiteidocfile
ewib20031006.txt -c<br /> -t -r
-e c:mssql7innib52.fmt -SJAVADEV2-PC-NJ -Usa -P<br /><br />Starting copy…<br />SQLState = 22001, NativeError = 0<br />Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation<br /><br />0 rows copied.<br />Network packet size (bytes): 4096<br />Clock Time (ms.): total 5878<br /><br />Bulk Insert command<br />bulk insert master..bibdata from ‘e:medsiteib20031006.txt’ with (formatfile=’c:mssql7innib52.fmt’)<br />Server: Msg 4863, Level 16, State 1, Line 1<br />Bulk insert data conversion error (truncation) for row 1, column 1 (recordtype).<br />Server: Msg 7399, Level 16, State 1, Line 1<br />OLE DB provider ‘STREAM’ reported an error. The provider did not give any information about the error.<br />The statement has been terminated.<br /><br />With DTS<br />Error at destination for row no 72313.Errors encountered so far in this task:1.<br />Could not allocate space for object ‘IBIB20031006’ in database ‘Northwind’ because the ‘PRIMARY’filegroup is full<br />error at row 9766<br /><br />when I Select tab:<br />Error Description:<br />too many columns found in the current row;non-whitespace characters were found after the last defined column’s data<br />Hi experts,<br /><br /><br />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).<br /><br /><br />my bcp command is:<br />C:MSSQL7Binn&gt;<br />bcp master..BIBDATA in c:idocfile
ewBIB20031006.TXT -fc:MSSQL7Binnib52.fmt -SJAVADEV2-PC -Usa -P<br /><br />I failed in DTS<br />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.<br />filtype: ANSI SKIP ROWS 0<br />ROW DELIMITER:{CR}{LF} FIRST ROW HAS COLUMN NAMES(CHECKED)<br />TEXT QUALIFIER:NONE<br /><br />Thanks<br />ven[<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />!]
Did you check columns definition with text file?.
If yes, try to change tab to other delimiter to see if have same error.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
True, it sounds as the text file or table has mismatch of columns or extra characters or extra whitespace. Refer to thishttp://support.microsoft.com/defaul…port/kb/articles/Q300/1/81.ASP&NoWebContent=1 KBA for information.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

i encoutered the same error and it’s strange the problem solved if i put the records (the rows cause errors) to the end of the file.
the sql 2000 server already installed the service pack 3a but it still no luck on it. any tips and advice?
What is the compatbility level of database?
What is the format of format file used? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

The compatbility level of database is lvl 80
and the format of format file (i guess) is ASCII p.s. what is compatbility level of a database?
Compatability level sets certain database behaviours to be compatibile with the specified version of SQL Server. In your case (lvl 80) the database behaves as a true SQL Server 2000 database. If you’ve migrated the database from SQL Server 7.0, for example, you will typically leave the level at 70 till you resolve any compatability issues.
Thanks Chakri, and how about the format file? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>