Excel to DBF

[jimoo]

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

 

[Mike Lewis]

This is perfectly possible - and not at all unusual.

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

[tt]SELECT MyDDF
APPEND FROM MyExcel TYPE XL5[/tt]

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:

[tt]IMPORT FROM MyExcel NAME MyNewDBF TYPE XL8[/tt]

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

 

[GriffMG]

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 [Smile]ing

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. ​

 

[Olaf Doschke]

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
Or as a later version in his blog:

http://praisachion.blogspot.de/2017/08/importfromxlsx-40.html

Bye, Olaf.

 

[mjcmkrsr]

Hi,
Please check Ion Praisach's page

[link ]

http://praisachion.blogspot.lu/2015/08/importfromxlsx-11.html

[/url]

hth

MK