CODEWITHSUNDEEP
c#ms-access
Darkenor
Darkenor

Reputation: 4449

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

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.

Upvotes: 0

Views: 4356

Answers (4)

James Johnson
James Johnson

Reputation: 46057

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());

Upvotes: 2

Fionnuala
Fionnuala

Reputation: 91366

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);
     }
 }

Upvotes: 1

Joshua Honig
Joshua Honig

Reputation: 13215

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;
}

Upvotes: 1

RQDQ
RQDQ

Reputation: 15569

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.

Upvotes: 2

Related Questions