Search This Blog

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

No comments:

Post a Comment