Search This Blog

Showing posts with label MSAccess to CSV. Show all posts
Showing posts with label MSAccess to CSV. Show all posts

Wednesday, August 3, 2011

Extraction of Pipe Delimited CSV Files from MSAccess

PreRequisites:
MS Access Back up file
Entries in Web.Config
<add name="MSAccessDB" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FileLocation where Backup file available;"/>

 <appSettings>
  <add key="TablesList" value="Table1,Table2,Table3,Table4"/>
 </appSettings>

using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;

       /// <summary>
        /// Extracting Pipe Delimited .CSV files from MSAccess
        /// </summary>
        /// <param name=""></param>
   private void ExtractCSVFromBackUp()
        {
            try
            {
                strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["MSAccessDB"].ToString();
                FilePath = @"File";
                TableNames = ConfigurationManager.AppSettings["TablesList"].ToString(); //List of tables to extract from MS Access, save this as Key/Value pair in .config file
                TablesList = TableNames.Split(new char[] { ',' });
                for (int tblCount = 0; tblCount < TablesList.Length; tblCount++)
                {
                    DataTable dt = new DataTable();
                    dt.TableName = TablesList[tblCount];
                    OleDbDataAdapter oDAAccessory = new OleDbDataAdapter("Select * from " + TablesList[tblCount], strConnection);
                    oDAAccessory.Fill(dt);
                   
                    // If table has Data then generate Pipe Delimited CSV file
                    if (dt.Rows.Count > 0)
                    {
                        string fileOut = string.Empty;
                        string strRow = string.Empty; // represents a full row 
                        fileOut = FilePath + TablesList[tblCount] + ".csv";
                        // Creates the CSV file as a stream, using the given encoding.                   
                        StreamWriter sw = new StreamWriter(fileOut, false, Encoding.ASCII);
                        // Reads the rows one by one from the DataTable transfers them to a string with the given
                        //separator character and writes it to the file.                                       
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            strRow = "";
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                if (dt.Columns[j].DataType.ToString() == "System.String" && dt.Rows[i][j].ToString() != "")
                                {
                                    strRow += "\"";
                                }
                                strRow += dt.Rows[i][j].ToString();
                                if (dt.Columns[j].DataType.ToString() == "System.String" && dt.Rows[i][j].ToString() != "")
                                {
                                    strRow += "|"";
                                }
                                if (j < dt.Columns.Count - 1)
                                {
                                    strRow += ",";
                                }
                            }
                            sw.WriteLine(strRow);
                        }
                        sw.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                LogMessageToFile("Exception in Admin Extrct  " + ex.Message.ToString());             
            }
        }