Convert XML file to DBF

Question

Hi,

I'm trying to convert an XML file to DBF. Right now I do this manually, opening the XML file in Excel 2003 and saving it as a DBF 4 file type. This is a boring and time consuming process, made worse by the fact that neither Office 2007, nor Office 2008 for Mac allow saving as DBF any more.

I'd like a way to automate this, if possible. Do you know any way of actually doing this? I don't even know what programming language to use for this...

Answer 1

Use a XmlReader, format your data and use OleDb to save to a dbf file. Here you can find a connectionstring for dbf: www.connectionstrings.com

Answer 2

Visual FoxPro has a command XMLTOCURSOR() that converts XML to a Visual FoxPro cursor. This cursor can be transformed into a disk-based DBF using the COPY TO command.

One caveat, the complexity of the XML might force a different approach with XMLAdapter, especially if there is hierarchical (nested) XML. The XMLAdapter in Visual FoxPro 9 has enhancements to deal with this format.

Rick Schummer

Answer 3

If opening the XML file in Excel 2003 and saving it as a DBF 4 file type works you what than may you should just emulate this process via a program. There are at least two approaches:

1) VBA running whithin Excel:

The other approach is using VBA which runs within Excel and automates the stuff you do manually. To get a feeling for it. You could run the Macro recorder and do the transformation once manually again. Afterwards you see the VBA code which emulates the things you are doing. With little tweaking you should be able to automate the whole process.

2) External Application: Basically you create an excel object in a separate Windows Application which encapsulates Excel and you do the stuff you would do manually via this object remotely.

Example code using VS 2008, C# and .NET:

....

using System;
using Microsoft.Office.Interop.Excel;

...

Microsoft.Office.Interop.Excel.Application oExcelApplication = new 
Microsoft.Office.Interop.Excel.Application();

...

oExcelApplication.Workbooks.Open(....);

....

and save the file in DBF4 format.

I hope this helps.