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