Solved

VS C# dbf files

Posted on 2014-12-18
9
774 Views
Last Modified: 2014-12-24
I have a collection of dbf files that I wanted to import into SQL. Using the import task in SQL every time I I point to any of these files, I am able to see the table names but when I zero in on any one file I get the message that file is not of the correct type. ("External table is not in the expected format"). I have tried downloading dbf file tools and they all come back with similar messages. I am wondering that perhaps whatever is creating these files is doing something with the file headers to make them invalid. My thought right now it to create a VS C# application in which I will then parse each record and extract out what I need. What should I use in the connection string for these files that will allow me to parse each character and determine where one field ends and the next begins as well as the end of record? Would it be better to specify a dbf type file? Is there a connection string parameter that can be used to specify that? How do I read dbf files using VS C#?
0
Comment
Question by:rwheeler23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 

Expert Comment

by:James Shinevar
ID: 40507511
What is the source of these dbf files?  Are they from FoxPro?  What version of SQL Server are you looking to import them to?  Is it 32 or 64 bit?  Sorry to drop a lot of questions, I'm actually in the middle of a FoxPro to SQL Server conversion right now.
0
 

Author Comment

by:rwheeler23
ID: 40507700
We are using SQL 2012. I have been given these dbf's that are generated by a third party product with no support. I threw every dbf reader I could find and none of them could read them. The ISV product can read them. That is why I know want to write a VS C# program just so I go character by character to see what is in the file. I did find one file called Dbase Runtime Edition.
0
 

Assisted Solution

by:James Shinevar
James Shinevar earned 200 total points
ID: 40507740
hmmm... so they are probably dbase files then.  I hate to plug a solution that requires a purchase, but this is what I ran into: we had FoxPro dbf files and SQL Server 2014 x64.  There is no "driver" or direct solution to migrate FoxPro to SQL Server 2014 x64.  If I had a SQL Server 2005 32bit server, my FoxPro drivers would have allowed me to make an ODBC connection and import the tables.  Since I didn't have that as an option, I used a solution from DBConvert https://dbconvert.com/download.php .   I downloaded the trial and when I saw it could convert within the limitations of the trial program (meaning not all the rows), I purchased the solution.  Perhaps one of their solutions will work for you.  It's worth a try, even while looking for a free alternative.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:James Shinevar
ID: 40507744
I have also read that it may be possible to import the dbf to Access and then import your access database to SQL Server, that might be a way to try.
0
 

Author Comment

by:rwheeler23
ID: 40507760
That is true, but I need to automate the transfer as it will be occurring daily. My first desire would have been to create an SSIS package but that is off the table now.
0
 
LVL 2

Expert Comment

by:GEOFS
ID: 40509314
Can you open the DBF's in Excel?
0
 

Author Comment

by:rwheeler23
ID: 40510368
No. I get the same message. External file is not of the correct format. I did find something called Dbase 9. This is the only file I found that could read these files.
0
 
LVL 2

Accepted Solution

by:
GEOFS earned 300 total points
ID: 40510756
Going back to your original plan to build a program to parse the file, the DBF header record contains, among other things, the names, types, lengths, etc. of the fields in the table.  The structure of the header record is fully described in the VFP documentation.
0
 

Author Closing Comment

by:rwheeler23
ID: 40517027
I will use an export tool that is able to read these files. Thanks for your help.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question