Export Access Data to Text or XML

[jasonp45]

Hi. I want to programmatically export from an Access table to either text or XML (I'd like to be able to do both). I'm using VB.NET on Windows XP. Any code appreciated.

 

[chiph]

The concept is pretty easy -- write some SQL to extract the data you're interested in, load it into a DataSet object, and then use the GetXml() method to extract it in XML format. Works for a reasonable amount of data (< 2000 rows).

For large amounts of data, you'll have to write more code -- I would suggest that you write a query to extract the primary keys of the data you want exported, then loop thru the list, running a query for each to pull the full record back. You can then use the GetXml() method again, and append it to your export file.

In order to give you the ability to output either XML or a text file, you would run it thru one of two XSLT transformations. Here's a link to one that converts XML to a CSV format:

http://cygwin.com/ml/xsl-list/2001-03/msg00785.html

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[jasonp45]

Thanks. I guess I should have provided more details...what I'm trying to do is pump out up to a million rows so I can put it into SQL Server. I'm considering using text so I can avoid DTS and instead use BCP or mass insert. So I'm wondering if there is some analogous fast text-dumping feature in Access.

 

[chiph]

Sorry, I'm not an Access expert. I would think MS-Access (the product) might have something for this.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first