July 5, 2007 at 3:48 pm
Sounds like this may be an issue with the fact that the DBF file is named "User" - that's a reserved word in SQL Server. Can you try to rename the files called "User".* to "Users".*? That may make a difference.
-Pete
July 6, 2007 at 6:53 am
I have tried other tables also Allpati.db being one that is very important to me. Is there an easy way to determine which version of Paradox made the file without having paradox?
-Ed
July 6, 2007 at 9:25 am
a *.DB file is most likely not the same as a *.DBF file. It could be a DBase file. I don't remember the exact details for that, but I think that instead of putting "Paradox 5.x" in the area referenced above, you put "Dbase IV". You may want to give that a try.
Additionally, if the file is small, you may want to open it in Notepad and see if that gives you any hints about the program used to create the file. Sometimes programs will put a little note about company/version in a header somewhere.
(I have no idea why MS couldn't have built these into a drop-down instead of having to search forums and newsgroups for this information. :-P)
-Pete
July 6, 2007 at 10:26 am
I am fairly sure that the files are Paradox tables. I found a small piece of information "DBWINUS0" in one of the files and when searching on that it seemed to be a language driver for Paradox. I gave the dBase IV property a try also but that also did not work. I am not sure if I need special passwords for this or what. I've imported from FoxPro and other flat file databases in the past with no where near the problems I have from this.
-Ed
July 6, 2007 at 10:53 am
If you have SQL 2000 around, can you try to import them with DTS? That may give you some more clues. I can't think of much else off the top of my head. I definitely agree that importing from some of the semi-standard formats has been harder with SSIS.
It is possible that it's some form of DBISAM file as well - that gets really tricky because there are quite a few varieties of DBISAM, all slightly different. 😛
-Pete
August 3, 2007 at 5:47 pm
I'm trying to follow this procedure but get this error when working on the Data Flow task, after adding the OLEDB Source and double-clicking it, when I drop the combo to select a table I get the error:
TITLE: Microsoft JET Database Engine
------------------------------
Couldn't find installable ISAM.
------------------------------
BUTTONS:
&Retry
Cancel
------------------------------
In my case I want to move data from .DBF files to a SQL 2005 database.
August 21, 2007 at 4:58 pm
Is it possible that the DBF files are Foxpro files? That might be worth looking into as well. I can't definitely comment on what's required to read Paradox as I have it installed on my machine (for when I need it to open Paradox files).
-Pete
August 28, 2007 at 8:10 pm
Download & Install "Microsoft OLE DB Provider for Visual FoxPro 9.0" from...
It will Create a new Option in the list of Source Data Drivers--> "Microsoft OleDb Provider for Fox Pro"
Also if Importing .DBF File don't forget to Select "Free Table Directory" when you are setting up connection
to the Location of the your DBF Files...By default if only looks at *.DBC.
Cheers
Daljinder
October 25, 2007 at 2:55 pm
Hi Peter,
Is this code right to transfer the data from a dbf file to sql server 2005
If (FlAS.PostedFile.FileName.ToLower.EndsWith(".dbf")) Then
FlAS.PostedFile.SaveAs(location)
Try
'Connection string to a dbase file
Dim dbfConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source ={0};Extended Properties= dBase IV", location)
'create connection to the DBF file
Using connection As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(dbfConnectionString)
Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select * from AS.DBF", connection)
connection.Open()
'Create a dbDatareader to the dbf file
Using dr As Data.Common.DbDataReader = command.ExecuteReader()
Dim sqlConnectionString As String = System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION)
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION))
Dim query As String = "Truncate table ASDBF"
myConnection.Open()
Dim cmd As New SqlCommand(query, myConnection)
cmd.CommandType = CommandType.Text
cmd.ExecuteScalar()
myConnection.Close()
'bulk copy of sql server
Using BulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString)
BulkCopy.DestinationTableName = "ASDBF"
BulkCopy.WriteToServer(dr)
End Using
End Using
connection.Close()
End Using
Catch ex As Exception
Throw ex
End Try
End If
Regards
Karen
October 25, 2007 at 3:05 pm
Karen,
I'm afraid I really can't comment on the code. I used SSIS to verify my data and once I sourced my files as either DBase IV using the JET connection or as a FoxPro file using the FoxPro OLEDB provider, my problems were pretty much resolved. If you're not using a specific file, you may need to use something like "select * from [filename]" to query the file. You can then browse or preview the file to see if the data is visible.
The only possible problem I can think of is that both FoxPro and DBase use .DBF as a file extension. That's the main reason I mentioned that you may need to try both.
-Pete
October 25, 2007 at 3:09 pm
how would i know if they are dbase file... or foxpro file.... and i also wanted to clarify if i can use bulkcopy to insert data in to sql server..
Regards
KAren
October 25, 2007 at 3:44 pm
Not sure exactly how to tell if it's DBase or Fox other than trying. A Hex editor may reveal something as well (I think there are indicators in the header area of the file). Once you get the connect string and queries down, you should be able to bulk insert the data as long as the types line up correctly. That's probably the trickiest thing I've had to deal with (initially) when converting from/to other formats - especially with date data.
-Pete
October 26, 2007 at 6:43 am
Peter,
Thanks for your answer. I will download the Driver for fox pro and then try it. I have structure for the dbf files and everything in them are character fields so i have created the same structure in SQL server. When i tried to use the above code that i pasted i got this error
'C:\Documents and Settings\Karen\My Documents\Visual Studio 2005\WebSites\ASC1\pdf\client\AS.DBF' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
I tried giving neccessary permissions but it stops at connection.open() which is the connection string to the dbf files...
Regards
Karen
October 26, 2007 at 8:30 am
Peter,
Thanks for the suggestion. I downloaded the driver for visual fox pro and it now it works fine. Thanks a lot.
Regards
Karen
October 13, 2009 at 10:12 pm
This blog describes how to do it.
http://dataintegrity.wordpress.com/2009/10/02/converting-dbf-files/
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply