var csvfilelist = Directory.GetFiles("FilePath", "*.csv");
static DataTable GetDataTableFromCsv(string path, bool isFirstRowHeader)
{
DataTable dataTable = new DataTable();
try
{
string header = isFirstRowHeader ? "Yes" : "No";
string pathOnly = Path.GetDirectoryName(path);
string fileName = Path.GetFileName(path);
string sql = @"SELECT * FROM [" + fileName + "]";
using (OleDbConnection connection = new OleDbConnection(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
";Extended Properties=\"Text;HDR=" + header + "\""))
using (OleDbCommand command = new OleDbCommand(sql, connection))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
dataTable.Locale = CultureInfo.CurrentCulture;
adapter.Fill(dataTable);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return dataTable;
}
public static DataTable ExcelFileToDatatable(string path, string Sheetname)
{
DataTable dataTable = null;
try
{
string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\";", path);
string query = String.Format("SELECT * from [{0}$]", Sheetname);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
dataTable = dataSet.Tables[0];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
}
return dataTable;
}
public static DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable)
{
DataTable returnTable = new DataTable();
try
{
var differences =
FirstDataTable.AsEnumerable().Except(SecondDataTable.AsEnumerable(),
DataRowComparer.Default);
returnTable = differences.Any() ? differences.CopyToDataTable() : new DataTable();
}
catch { }
return returnTable;
}
public static int DeleteCSVRow(string filepath, string RemoveRowlist, int ColumnIndex)
{
int returnresult = 0;
try
{
using (TextReader reader = new StreamReader(filepath))
{
using (TextWriter writer = new StreamWriter(filepath + ".temp"))
{
string line;
while ((line = reader.ReadLine()) != null)
{
string[] linerow = line.Split(',');
if (!linerow[ColumnIndex].Contains(RemoveRowlist))
{
writer.WriteLine(line);
}
else
{
returnresult++;
}
}
}
}
if (returnresult > 0)
File.Delete(filepath);
System.IO.File.Move(filepath + ".temp", filepath);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return returnresult;
}
No comments:
Post a Comment