×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel to DBF

Excel to DBF

Excel to DBF

(OP)
I have not used Foxpro for several years and a friend has asked me a question that I don't recall for sure. He is trying to use Foxpro exclusively to convert an excel file to DBF. In the past, they have used Access as an interim but are trying to get away from that for a few reasons. If my memory is correct, it is difficult to convert an excel file to DBF without a program.

Any suggestions?

Thanks in advance, see his email question below in bold/italic:


Our plan is to generate an excel file and use FOXPRO simply to convert the excel file to a DBF. We can use ACCESSDB to save it as a DBF but hard to keep the table properties same as the original dbf we are used to sending to fiscals.

Jim

RE: Excel to DBF

This is perfectly possible - and not at all unusual.

If the DBF file already exists, use APPEND FROM to import Excel data into it:

SELECT MyDDF
APPEND FROM MyExcel TYPE XL5


Each row in the worksheet will be copied to a record in the table, with columns mapped to fields on a one-to-one bases.

Alternaively, if the DBF doesn't yet exist, use the import command to create it and import the data in one go:

IMPORT FROM MyExcel NAME MyNewDBF TYPE XL8

You can also do an import interactively, using the Import Wizard (on the Tools menu).

In both cases, you can use the SHEET clause to specify the worksheet within the workbook.

Keep in mind that the import will probably not be perfect. Depending on your data types, there might be some issues with how certain fields are imported, especially data and datetime fields. But the above should get you started.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Excel to DBF

If you know the structure of the Excel file, it is usually pretty easy to import it.

Alternatively, if you have access (not Access) to 'Office Automation' you could open Excel, open the data
and save it as a .dbf file.

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: Excel to DBF

Possible complications, especially as in Mikes simple example of APPEND are modern XLSX formats. As XL5 hints, this is limited to old binary excel formats and also only covers sheet 1 of such an XLS file and also only, if it has the structure of a dbf, i.e. columns with the same data type and either a header line or not.

So in detail, it all depends on the type of files. Access has adapted to later versions of Office better than VFP and if you use it successfully your excel files should at least be of the necessary structure.

What you can also use in VFP is the OLEDB Provider or ODBC drivers excel offers for database-wise access to sheets and VFPs capabilities to use OLEDB Providers and ODBC driver access to data.

Functions like Excel2DBF and DBF2Excel ar floating around the net, for example here in thread184-1759033: ImportFromXlsx 1.3
Or as a later version in his blog: http://praisachion.blogspot.de/2017/08/importfromx...

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close