How can I extract an MDB file's table contents to text in C#?

Question

A project I'm working on contains an MDB (acecss database) file. I'd like to export the contents of the tables to text, but am having a hard time finding a way to do it easily using C#. Is there a faster way than using OLEDB and queries?

Update: Ideally I'd like to not have to statically name each table (there are hundreds) and I have to use .NET 2.0 or below.

How is an OleDbDataReader not fast? A straight ("SELECT * FROM [Table]") query is very fast, only limited by your disk read and / or network speed. — Joshua Honig, Mar 28 '12 at 14:32
Does this need to be done in code? There is an option to export a table to a text file in Access. — James Johnson, Mar 28 '12 at 14:34

score 2 · Answer 1 · answered Mar 28 '12 at 14:32

2

No obvious way comes to mind. Just write something that iterates through the tables and spits out the data in whatever text format you want (.csv, tab delimited, etc).

You could always write it in VBA inside of Access, but I don't know if that would make it faster or slower.

answered Mar 28 '12 at 14:32

RQDQ

15,461
2
32
59

The only problem I see is that there are hundreds of tables. Ideally I'd like to not have to statically name the tables and have it instead just do them all. – Darkenor Mar 28 '12 at 15:08
I assumed you would get the tables programatically: http://stackoverflow.com/questions/201282/how-to-get-table-names-from-access – RQDQ Mar 28 '12 at 15:14

James Johnson · Accepted Answer · 2012-03-29T01:51:54.977

There might be a more efficient way, but you could populate the data into a DataTable, and then export to a text file:

Getting data into the DataTable:

string connString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\\marcelo.accdb";

DataTable results = new DataTable();

using(OleDbConnection conn = new OleDbConnection(connString))
{
    OleDbCommand cmd = new OleDbCommand("SELECT * FROM Clientes", conn);
    conn.Open();
    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
    adapter.Fill(results);
}

Exporting the DataTable to CSV:

EDIT I haven't tested this, but something like this should work for .NET 2.0.

//initialize the strinbuilder
StringBuilder sb = new StringBuilder();    

//append the columns to the header row
string[] columns = new string[dt.Columns.Count - 1];
for (int i = 0; i < dt.Columns.Count; i++)
    columns[i] = dt.Columns[i].ColumnName;
sb.AppendLine(string.Join(",", columns));          

foreach (DataRow row in dt.Rows)
{
    //append the data for each row in the table
    string[] fields = new string[row.ItemArray.Length];
    for (int x = 0; x < myDataRow.ItemArray.Length; x++)        
        arr[x] = row[x].ToString();                
    sb.AppendLine(string.Join(",", fields));
}

File.WriteAllText("test.csv", sb.ToString());

@RQDQ: That is good advice for OP. This was just a quick example to demonstrate the concept. — James Johnson, Mar 28 '12 at 14:42
Good idea - but I should have mentioned it's .NET 2.0 specific. I see link in the column names code. :( — Darkenor, Mar 28 '12 at 15:08

score 1 · Answer 3 · answered Mar 28 '12 at 14:41

If you want to go the Interop route, you can do it in a single command with the Access TransferText method:

using Access = Microsoft.Office.Interop.Access;
using System.Runtime.InteropServices;

static void ExportToCsv(string databasePath, string tableName, string csvFile) {
    Access.Application app = new Access.Application();
    app.OpenCurrentDatabase(databasePath);
    Access.DoCmd doCmd = app.DoCmd;
    doCmd.TransferText(Access.AcTextTransferType.acExportDelim, Type.Missing, tableName, csvFile, true);
    app.CloseCurrentDatabase();
    Marshal.FinalReleaseComObject(doCmd);
    doCmd = null; 
    app.Quit();
    Marshal.FinalReleaseComObject(app);
    app = null;
}

Wow - had no idea this existed. Nice! – RQDQ Mar 28 '12 at 14:42 — RQDQ, Mar 28 '12 at 14:42

Fionnuala · Answer 4 · 2012-03-28T16:24:24.343

I do not know C#, but here is another idea, but quite rough. It uses Microsoft.Office.Interop.Access.Dao

 DBEngine dbEng = new DBEngine();
 Workspace ws = dbEng.CreateWorkspace("", "admin", "", 
    WorkspaceTypeEnum.dbUseJet);
 Database db = ws.OpenDatabase("z:\\docs\\test.accdb", false, false, "");

 foreach (TableDef tdf in db.TableDefs)
 {
     string tablename=tdf.Name;
     if (tablename.Substring(0,4) != "MSys")
     {
         string sSQL = "SELECT * INTO [Text;FMT=Delimited;HDR=Yes;DATABASE=Z:\\Docs].[out_" 
            + tablename + ".csv] FROM " + tablename;
         db.Execute(sSQL);
     }
 }

How can I extract an MDB file's table contents to text in C#?

4 Answers4

Linked