Import from .DBF

Question

Import from .DBF

ouertani neked

SSC-Addicted

Points: 406
More actions
May 3, 2004 at 7:02 am

#111074

Hi everyBody,
I need to import data from DBF files il my Sql database using Tsql stored procedure
thanks for any suggestion

Viewing 8 posts - 1 through 7 (of 7 total)

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

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

Hi,

Is there any specific reason why you want to use T-SQL Stored Procedures instead of the DTS import data functionality from the SQL Server Enterprise Manager.

Thanks

Prasad Bhogadi
www.inforaise.com

ouertani neked SSC-Addicted Points: 406 More actions · Answer 2

thank you vm,

the process of migration I'm working on must run as one piece(one call for the whole process).

I've tried DTS but it's not what I really need, it do the work if I include it as VB code, I'm testing this option but it's still not the best way.

The BULK INSERT instrution looks to be what i'm looking for but so far it brings errors.

jgoodwin SSChasing Mays Points: 648 More actions · Answer 3

There are several methods you could use (I usually use a .vbs), but here is one that uses all T-SQL. Basically create a linked server, and then use openquery to get at the data.

DECLARE @LinkedServer varchar(128)

DECLARE @FoxProDriver varchar(1024)

SET @LinkedServer = 'DBFImport' -- !!!WARNING!!! If this name changes, the

-- linked server reference in the OPENQUERY

-- statement must change as well.

--Add a linked server to import the DBF data.

-- First, drop any existing linked servers so an up-to-date link can be created.

-- Execution will continue on the error that the linked server does not exist.

EXEC sp_dropserver @LinkedServer, 'droplogins'

-- Add a linked server to the dbf file. This is a dBaseIII format using

-- FoxPro 2.5 indexes.

SET @FoxProDriver = 'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB='

+ @DBFPathandTableName

+ ';SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;'

EXEC sp_addlinkedserver @LinkedServer,

'',

'MSDASQL',

NULL,

@FoxProDriver

-- Since this dBaseIII file requires no password or login, a linked server

-- login must be added to access any tables.

EXEC sp_addlinkedsrvlogin @LinkedServer, FALSE, NULL, NULL, NULL

DECLARE @ExecSQL varchar(100)

BEGIN

--Execute the query on the EDC table.

SET @ExecSQL = 'SELECT * FROM OPENQUERY(DBFImport, ''SELECT * FROM DBFTableName'')'

EXEC(@ExecSQL)

END

cmore SSCrazy Points: 2452 More actions · Answer 4

BCP is the fastest way to get the data from a file into SQL. Although DTS can be pretty fast also, it is bad about giving inspecific errors and failing because of data inconsistencies in the .dbf store.

I've had to do a few here and there in the past. Because I was more familiar with FoxPro at the time, I chose to clean the data in foxpro before importing, then I used DTS. Recently I have switched to using some openqueries. BCPing the data in and just dealing with the errors manually would be ideal, but that may not fit with your design criteria.

Dates have to be parsed (limitations on date spread in SQL)

If it's a non-null .dbf (i.e. fox 2.6), then it can get funny. i.e. dates that are 'blank' in foxpro are translated by OLEDB to be like 1853 or some wierd thing. Forget the exact date.

Decimal place problems can make the import blow. Foxpro 2.6 tables with a column defined as numeric(4,2) will actually let you store numbers bigger than 99.99. SQL dislikes that. Also, fox will sometimes store ***** in the place of the number if it was an overflow. SQL doesn't like that either.

Jonathan Chretien SSC Enthusiast Points: 139 More actions · Answer 5

Hi.

I tried the procedure that has been given over and it did not work.

On the @DBFPathandTableName variable I put the path and the path with the filename without success.

SELECT * FROM OPENQUERY(DBFIMPORT,"SELECT * FROM PACKAGE")

When I excute the openquery, I always get an error.

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'MSDASQL' reported an error.

[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]File 'package.dbf' does not exists.]

Do you have an idea ??

I need this kind of procedure because I want that a user without administration right can be able to upload the file.

Thanks

Jonathan

cmore SSCrazy Points: 2452 More actions · Answer 6

Using a filtered query that returns one record and you know works in fox, such as "select field1 from package where packageid = 123", work with it manually in Query Analyzer until you get it working. Specifying the fields returned ensures you don't have to deal with field conversion issues right away... you only have to deal with making sure you can access the data, which is your current problem.

The most common reason for it not being able to find the table is because people tend to forget that the query is coming from the SQL Server box, not the client.

If your table is on FileServer01 on the D: drive in ServerFolder, from that box the path would be d:\ServerFolder\Package.dbf

From SQLServer01 you would have to map the drive or use a UNC path and set up the ODBC DSN to point to the proper place. I.e. \\FileServer01\DShare\ServerFolder\Package.dbf.

It can also return this error if you have security issues, but unless you have a wacky network setup this is usually uncommon.

Jonathan Chretien SSC Enthusiast Points: 139 More actions · Answer 7

Forget it.

I made a silly thing.

With your reply, you helped me to find my error.

Sorry guy.