|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
BCP .dbf file useing t-sqlI have attached the error, my command line code and my fmt file below. Should I be using tab delimited for a DBF? This is driving me crazy, I have been imported XLS and TXT files forever, but never a DBF thru code. TIA, Joe I keep getting the following error: [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP dat a-file The code I am using is: bcp policy..DWXP817W in D:\polyMGT\DWXP817W.dbf -T -SSQLD1 -fbcp.fmt My FMT file looks like: 8.0 13 1 SQLNCHAR 2 4 "" 1 TRANS SQL_Latin1_General_CP1_CI_AS 2 SQLNCHAR 2 2 "" 2 FINAUD SQL_Latin1_General_CP1_CI_AS 3 SQLNCHAR 2 6 "" 3 IFSEQ SQL_Latin1_General_CP1_CI_AS 4 SQLNCHAR 2 20 "" 4 POLICY SQL_Latin1_General_CP1_CI_AS 5 SQLNCHAR 2 6 "" 5 PROD SQL_Latin1_General_CP1_CI_AS 6 SQLFLT8 1 8 "" 6 PREMO "" 7 SQLFLT8 1 8 "" 7 PREMP "" 8 SQLNCHAR 2 4 "" 8 IFSCDE SQL_Latin1_General_CP1_CI_AS 9 SQLNCHAR 2 36 "" 9 PSTAT SQL_Latin1_General_CP1_CI_AS 10 SQLFLT8 1 8 "" 10 EFFDTE "" 11 SQLFLT8 1 8 "" 11 EDSDTE "" 12 SQLNCHAR 2 10 "" 12 AGENT SQL_Latin1_General_CP1_CI_AS 13 SQLFLT8 1 8 "\r\n" 13 RUNDTE "" Is this DBF file is a Dbase or Foxpro file?
If yes then, Export data from dbf files to text files, using csv format and then Use bcp to import data into SQL Server Thanks Hari SQL Server MVP Show quote "jaylou" <jay***@discussions.microsoft.com> wrote in message news:0F5403B6-B730-42A6-B7F0-DABDFFC9E700@microsoft.com... >I am trying o import a .DBF file into my SQL server 2000 database. > I have attached the error, my command line code and my fmt file below. > Should I be using tab delimited for a DBF? This is driving me crazy, I > have > been imported XLS and TXT files forever, but never a DBF thru code. > > TIA, > Joe > > > I keep getting the following error: > [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP dat > a-file > > The code I am using is: > bcp policy..DWXP817W in D:\polyMGT\DWXP817W.dbf -T -SSQLD1 -fbcp.fmt > > My FMT file looks like: > 8.0 > 13 > 1 SQLNCHAR 2 4 "" 1 > TRANS > SQL_Latin1_General_CP1_CI_AS > 2 SQLNCHAR 2 2 "" 2 > FINAUD > SQL_Latin1_General_CP1_CI_AS > 3 SQLNCHAR 2 6 "" 3 > IFSEQ > SQL_Latin1_General_CP1_CI_AS > 4 SQLNCHAR 2 20 "" 4 > POLICY > SQL_Latin1_General_CP1_CI_AS > 5 SQLNCHAR 2 6 "" 5 PROD > SQL_Latin1_General_CP1_CI_AS > 6 SQLFLT8 1 8 "" 6 > PREMO > "" > 7 SQLFLT8 1 8 "" 7 > PREMP > "" > 8 SQLNCHAR 2 4 "" 8 > IFSCDE > SQL_Latin1_General_CP1_CI_AS > 9 SQLNCHAR 2 36 "" 9 > PSTAT > SQL_Latin1_General_CP1_CI_AS > 10 SQLFLT8 1 8 "" 10 > EFFDTE > "" > 11 SQLFLT8 1 8 "" 11 > EDSDTE > "" > 12 SQLNCHAR 2 10 "" 12 > AGENT > SQL_Latin1_General_CP1_CI_AS > 13 SQLFLT8 1 8 "\r\n" 13 > RUNDTE > "" > BCP is for importing plain text files. To import from dbf files yuou
need to use DTS (SQL Server 2000) or SSIS (SQL Server 2005). Roy Harvey Beacon Falls, CT On Fri, 18 Aug 2006 06:01:02 -0700, jaylou <jay***@discussions.microsoft.com> wrote: Show quote >I am trying o import a .DBF file into my SQL server 2000 database. >I have attached the error, my command line code and my fmt file below. >Should I be using tab delimited for a DBF? This is driving me crazy, I have >been imported XLS and TXT files forever, but never a DBF thru code. > >TIA, >Joe > > >I keep getting the following error: >[Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP dat >a-file > >The code I am using is: >bcp policy..DWXP817W in D:\polyMGT\DWXP817W.dbf -T -SSQLD1 -fbcp.fmt > >My FMT file looks like: >8.0 >13 >1 SQLNCHAR 2 4 "" 1 TRANS > SQL_Latin1_General_CP1_CI_AS >2 SQLNCHAR 2 2 "" 2 FINAUD > SQL_Latin1_General_CP1_CI_AS >3 SQLNCHAR 2 6 "" 3 IFSEQ > SQL_Latin1_General_CP1_CI_AS >4 SQLNCHAR 2 20 "" 4 POLICY > SQL_Latin1_General_CP1_CI_AS >5 SQLNCHAR 2 6 "" 5 PROD > SQL_Latin1_General_CP1_CI_AS >6 SQLFLT8 1 8 "" 6 PREMO > "" >7 SQLFLT8 1 8 "" 7 PREMP > "" >8 SQLNCHAR 2 4 "" 8 IFSCDE > SQL_Latin1_General_CP1_CI_AS >9 SQLNCHAR 2 36 "" 9 PSTAT > SQL_Latin1_General_CP1_CI_AS >10 SQLFLT8 1 8 "" 10 EFFDTE > "" >11 SQLFLT8 1 8 "" 11 EDSDTE > "" >12 SQLNCHAR 2 10 "" 12 AGENT > SQL_Latin1_General_CP1_CI_AS >13 SQLFLT8 1 8 "\r\n" 13 RUNDTE > "" Roy Harvey (roy_har***@snet.net) writes:
> BCP is for importing plain text files. To be picky, it's perfectly possible to import binary files. In fact BCPassumes that all files it gets are binary, and text format is just a special case. But of course, when you import data from other systems, you will have to go by text format, as the binary format for things like datetime is likely to differ. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx On Fri, 18 Aug 2006 14:56:46 +0000 (UTC), Erland Sommarskog
<esq***@sommarskog.se> wrote: >Roy Harvey (roy_har***@snet.net) writes: Thanks for the correction, Erland. I have even used BCP to import>> BCP is for importing plain text files. > >To be picky, it's perfectly possible to import binary files. In fact BCP >assumes that all files it gets are binary, and text format is just a >special case. But of course, when you import data from other systems, you >will have to go by text format, as the binary format for things like >datetime is likely to differ. non-text files - though only ones created by BCP in "native" format. Roy Yes I know, but that would have to be a manual process. the file names may
vary at times. I get a daily file that I can import via DTS, but if thre are errors in the file they produce a file like this DW<MMDD>D1.DBF. do you know of a way to dynamically import the DBF files thru DTS? I was trying to figure a way to automate this process, in my proc I can get the name of all files in the directory into a temp table and loop thu all the names and use BCP. Thanks again. Joe Show quote "Roy Harvey" wrote: > BCP is for importing plain text files. To import from dbf files yuou > need to use DTS (SQL Server 2000) or SSIS (SQL Server 2005). > > Roy Harvey > Beacon Falls, CT > > On Fri, 18 Aug 2006 06:01:02 -0700, jaylou > <jay***@discussions.microsoft.com> wrote: > > >I am trying o import a .DBF file into my SQL server 2000 database. > >I have attached the error, my command line code and my fmt file below. > >Should I be using tab delimited for a DBF? This is driving me crazy, I have > >been imported XLS and TXT files forever, but never a DBF thru code. > > > >TIA, > >Joe > > > > > >I keep getting the following error: > >[Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP dat > >a-file > > > >The code I am using is: > >bcp policy..DWXP817W in D:\polyMGT\DWXP817W.dbf -T -SSQLD1 -fbcp.fmt > > > >My FMT file looks like: > >8.0 > >13 > >1 SQLNCHAR 2 4 "" 1 TRANS > > SQL_Latin1_General_CP1_CI_AS > >2 SQLNCHAR 2 2 "" 2 FINAUD > > SQL_Latin1_General_CP1_CI_AS > >3 SQLNCHAR 2 6 "" 3 IFSEQ > > SQL_Latin1_General_CP1_CI_AS > >4 SQLNCHAR 2 20 "" 4 POLICY > > SQL_Latin1_General_CP1_CI_AS > >5 SQLNCHAR 2 6 "" 5 PROD > > SQL_Latin1_General_CP1_CI_AS > >6 SQLFLT8 1 8 "" 6 PREMO > > "" > >7 SQLFLT8 1 8 "" 7 PREMP > > "" > >8 SQLNCHAR 2 4 "" 8 IFSCDE > > SQL_Latin1_General_CP1_CI_AS > >9 SQLNCHAR 2 36 "" 9 PSTAT > > SQL_Latin1_General_CP1_CI_AS > >10 SQLFLT8 1 8 "" 10 EFFDTE > > "" > >11 SQLFLT8 1 8 "" 11 EDSDTE > > "" > >12 SQLNCHAR 2 10 "" 12 AGENT > > SQL_Latin1_General_CP1_CI_AS > >13 SQLFLT8 1 8 "\r\n" 13 RUNDTE > > "" > Take a look at the link below to see how to deal with all the files in
a directory. http://www.sqldts.com/default.aspx?246 Roy Harvey Beacon Falls, CT On Fri, 18 Aug 2006 08:08:51 -0700, jaylou <jay***@discussions.microsoft.com> wrote: Show quote >Yes I know, but that would have to be a manual process. the file names may >vary at times. I get a daily file that I can import via DTS, but if thre are >errors in the file they produce a file like this DW<MMDD>D1.DBF. do you know >of a way to dynamically import the DBF files thru DTS? I was trying to >figure a way to automate this process, in my proc I can get the name of all >files in the directory into a temp table and loop thu all the names and use >BCP. > >Thanks again. >Joe Thanks,
I already knew how to loop thru text files, I needed a way to do this with DBF files. I now need to find out if I can get these files as a CSV or a txt format. Thanks again, Joe Show quote "Roy Harvey" wrote: > Take a look at the link below to see how to deal with all the files in > a directory. > > http://www.sqldts.com/default.aspx?246 > > Roy Harvey > Beacon Falls, CT > > On Fri, 18 Aug 2006 08:08:51 -0700, jaylou > <jay***@discussions.microsoft.com> wrote: > > >Yes I know, but that would have to be a manual process. the file names may > >vary at times. I get a daily file that I can import via DTS, but if thre are > >errors in the file they produce a file like this DW<MMDD>D1.DBF. do you know > >of a way to dynamically import the DBF files thru DTS? I was trying to > >figure a way to automate this process, in my proc I can get the name of all > >files in the directory into a temp table and loop thu all the names and use > >BCP. > > > >Thanks again. > >Joe > jaylou wrote:
> Thanks, Why can't you use the method described on sqldts.com? Sure, the code > I already knew how to loop thru text files, I needed a way to do this with > DBF files. > I now need to find out if I can get these files as a CSV or a txt format. > there is working with text files, but why can't you modify it to work with DBF files instead? The process should be the same, just with a different connection type in the DTS package.  
Other interesting topics
tough request
How to combine several long strings and then insert them to a text field Urgent: Help on query Copy SQL ResultSet to Variable User-defined Select list Parse Text Problem Probably very trivial SQL Query question.... Openquery Maximum Length 128 Same query ,different results ,why! PLEASE HELP with Query Orphaned Trigger... Problems using LinRegR2 Scramble Integer column? How can I pivot data on a range of integers? - Brain buster here! Run a query against multiple databases SQL Server - Function Table Call Select against calendar table Case don't run in a query Tracking Databases On a Server/Instance Paging by char parameter ORDER BY VIRTUAL TABLE CASE END bug? Calling an Oracle Stored Procedure from a Sql Server 2000 Stored Procedure using a Linked Server how to better manage jobs created by reporting services? Function for finding the median in T-SQL? Re: Copy SQL ResultSet to Variable or Table This! issue with sp_OACreate SQL Insert Statement How to stop sqlservr.exe........ Error when executing a distributed query on a linked server Tracking data changes SQL 2005, web services, error: Object reference not set to an inst Run a .vbs job Read Applying hierachy custom security with TSQL. insert into table Procedure expects parameter which was not supplied Help with reconciling data in two tables Love Hotline Love Hotline Can I use .NDF file recover my data? SUB QUERY SELECT Backup Job Fails SQL query to find repeat entries Record count after Insert Dynamic "ORDER BY" based on data in database how to use multiple criteria based on other criteria in Where clau Trigger and instr Controlling test input Group By Month trigger problem |
|||||||||||||||||||||||
