Creating .csv file from an Access table using C#

Question

I am trying to write a program which creates a .csv file from an Access table. I'm not sure how to do this and everything I've found while researching this is how to do the opposite, creating an Access table from a .csv file.

So far, I have created a windows forms application that allows the user to select a data path to the access directory (.mdb) and then after pushing the "Go" button, a list of the tables in the directory is shown in a listbox.

What I need to do next, which I have no idea how to, is allow the user to select one of the tables, which would then create a .csv file from the selected table. Here is my code so far:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.Common;

namespace TranslatorHelper
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
    private void btnPath_Click(object sender, EventArgs e)
    {
        string dialogText = "";
        // Show the dialog and get result.
        DialogResult result = openFileDialog1.ShowDialog();
        if (result == DialogResult.OK) // Test result.
        {
            dialogText = openFileDialog1.ToString();
            dialogText = dialogText.Replace("System.Windows.Forms.OpenFileDialog: Title: , FileName: ", "");
            txtDataPath.Text = dialogText;
        }
    }

    private void btnGetTables_Click(object sender, EventArgs e)
    {
        // Microsoft Access provider factory
        DbProviderFactory factory =
        DbProviderFactories.GetFactory("System.Data.OleDb");

        DataTable userTables = null;

        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+txtDataPath.Text;

            // We only want user tables, not system tables
            string[] restrictions = new string[4];
            restrictions[3] = "Table";

            connection.Open();

            // Get list of user tables
            userTables = connection.GetSchema("Tables", restrictions);
        }

    // Add list of table names to listBox
    for (int i = 0; i < userTables.Rows.Count; i++)
    lstTables.Items.Add(userTables.Rows[i][2].ToString());

    }

    private void lstTables_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

Please offer any advice you can, anything is appreciated, I'm really stuck here.

DonBoitnott · Accepted Answer · 2013-05-24 10:54:36Z

3

Perhaps I can shove you in the proper direction:

You have the start, which is the DbConnection. You can then create a DbCommand from that:

DbCommand command = connection.CreateCommand();

From there, you can assign text, such as:

command.CommandText = "SELECT * FROM " + tableName;  //you might have to wrap the table name with quotes or square brackets!

Then you can get a reader:

DbDataReader reader = command.ExecuteReader(CommandBehavior.Default);

Once you have the reader, you've got the data, meaning you can then:

DataTable dt = new DataTable("MyNewTable");
dt.Load(reader);
foreach (DataRow row in dt.Rows)
{
    foreach (DataColumn dc in row.Columns)
    {
        if (!String.IsNullOrEmpty(sb.ToString()))
            sb.Append(",");
        sb.Append(row[dc.ColumnName].ToString());
    }
    sb.Append("\n");
}

To get the ListBox selection, you could use:

lstTables.SelectedItem.ToString()

That should give you the item, or in your case a table name. Don't forget that certain words are reserved in SQL (and it also depends on which SQL it is), so you might have to be careful using the table name directly in the SQL string for the CommandText parameter.

Also, I would see no need to use a SelectedIndexChanged event handler unless you need to do something immediately upon the user making a selection. If the behavior you want from the user is to make a selection and then click a button to start a process, then it's unnecessary to handle the change.

edited May 24, 2013 at 10:54

answered May 23, 2013 at 19:42

DonBoitnott

11.1k7 gold badges53 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Justin Over a year ago

He would then need to iterate through the data reader and output the csv there or use it to build something else to store/save the data.

DonBoitnott Over a year ago

@Justin That's right. Figured if that's not enough, I can expand on it. But once you have the data, it should be much simpler. Then you just loop the records and StringBuilder your way to fame!

Gord Thompson Over a year ago

For the actual creating of the CSV file I have seen a few other posts here recommending FileHelpers, a free and open source .NET library for import/export to text files.

DonBoitnott Over a year ago

Added a bit more, just in case.

Mikkel Bang Over a year ago

How do I know which table was selected from my listbox while still inside the button method?

|

Collectives™ on Stack Overflow

Creating .csv file from an Access table using C#

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related