In financial applications, especially for MCX or stock market data, you often need to process large CSV/MCX files, filter records, and insert them efficiently into a database. This article explains a step-by-step approach using C#.
1. Reading CSV/MCX File into DataTable
The method convertdatatableMCX reads a CSV file and converts it into a DataTable:
public DataTable convertdatatable(string Filepath)
{
DataTable FinalTable = new DataTable();
// Define columns
for (int i = 1; i <= 37; i++)
FinalTable.Columns.Add("column" + i, typeof(string));
FinalTable.Columns.Add("Count", typeof(int));
StreamReader sr = new StreamReader(Filepath);
try
{
string Fulltext = sr.ReadToEnd();
string[] rows = Fulltext.Split('\n');
if (rows.Length > 1)
{
for (int i = 1; i < rows.Length; i++)
{
string[] rowValues = rows[i].Split(',');
if (rowValues.Length > 1)
{
DataRow dr = FinalTable.NewRow();
for (int j = 0; j < 37; j++)
dr[j] = rowValues.Length > j ? rowValues[j].Trim() : "";
dr[37] = i; // Track row number
FinalTable.Rows.Add(dr);
}
else
{
obj.WriteFailureLogFile("No Data in File");
}
}
}
else
{
obj.WriteFailureLogFile("No Data in File");
}
}
catch (Exception ex)
{
obj.WriteErrorLogFile(ex.ToString(),"Converting");
}
finally
{
sr.Close();
sr.Dispose();
}
return FinalTable;
}
(or)
public DataTable convertdatatable(string Filepath)
{
#region table
DataTable FinalTable = new DataTable();
FinalTable.Columns.Add("column1", typeof(String));
FinalTable.Columns.Add("column2", typeof(String));
FinalTable.Columns.Add("column3", typeof(String));
FinalTable.Columns.Add("column4", typeof(String));
FinalTable.Columns.Add("column5", typeof(String));
FinalTable.Columns.Add("column6", typeof(String));
FinalTable.Columns.Add("column7", typeof(String));
FinalTable.Columns.Add("column8", typeof(String));
FinalTable.Columns.Add("column9", typeof(String));
FinalTable.Columns.Add("column10", typeof(String));
FinalTable.Columns.Add("column11", typeof(String));
FinalTable.Columns.Add("column12", typeof(String));
FinalTable.Columns.Add("column13", typeof(String));
FinalTable.Columns.Add("column14", typeof(String));
FinalTable.Columns.Add("column15", typeof(String));
FinalTable.Columns.Add("column16", typeof(String));
FinalTable.Columns.Add("column17", typeof(String));
FinalTable.Columns.Add("column18", typeof(String));
FinalTable.Columns.Add("column19", typeof(String));
FinalTable.Columns.Add("column20", typeof(String));
FinalTable.Columns.Add("column21", typeof(String));
FinalTable.Columns.Add("column22", typeof(String));
FinalTable.Columns.Add("column23", typeof(String));
FinalTable.Columns.Add("column24", typeof(String));
FinalTable.Columns.Add("column25", typeof(String));
FinalTable.Columns.Add("column26", typeof(String));
FinalTable.Columns.Add("column27", typeof(String));
FinalTable.Columns.Add("column28", typeof(String));
FinalTable.Columns.Add("column29", typeof(String));
FinalTable.Columns.Add("column30", typeof(String));
FinalTable.Columns.Add("column31", typeof(String));
FinalTable.Columns.Add("column32", typeof(String));
FinalTable.Columns.Add("column33", typeof(String));
FinalTable.Columns.Add("column34", typeof(String));
FinalTable.Columns.Add("column35", typeof(String));
FinalTable.Columns.Add("column36", typeof(String));
FinalTable.Columns.Add("column37", typeof(String));
FinalTable.Columns.Add("Count", typeof(int));
#endregion
StreamReader sr = new StreamReader(Filepath);
try
{
string filepath = Filepath;
string Fulltext;
Fulltext = sr.ReadToEnd().ToString();
string[] rows = Fulltext.Split('\n');
if (rows.Count() > 1)
{
for (int i = 1; i < rows.Count(); i++)
{
string[] rowValues = rows[i].Split(',');
string column1 = "", column2 = "", column3 = "", column4 = "", column5 = "", column6 = "", column7 = "", column8 = "", column9 = "", column10 = "", column11 = "", column12 = "",
column13 = "", column14 = "", column15 = "", column16 = "", column17 = "", column18 = "", column19 = "", column20 = "", column21 = "", column22 = "", column23 = "", column24 = "",
column25 = "", column26 = "", column27 = "", column28 = "", column29 = "", column30 = "", column31 = "", column32 = "", column33 = "", column34 = "", column35 = "", column36 = "",
column37 = "";
if (rowValues.Length > 1)
{
#region assin
column1 = rowValues[0].ToString().Trim();
column2 = rowValues[1].ToString().Trim();
column3 = rowValues[2].ToString().Trim();
column4 = rowValues[3].ToString().Trim();
column5 = rowValues[4].ToString().Trim();
column6 = rowValues[5].ToString().Trim();
column7 = rowValues[6].ToString().Trim();
column8 = rowValues[7].ToString().Trim();
column9 = rowValues[8].ToString().Trim();
column10 = rowValues[9].ToString().Trim();
column11 = rowValues[10].ToString().Trim();
column12 = rowValues[11].ToString().Trim();
column13 = rowValues[12].ToString().Trim();
column14 = rowValues[13].ToString().Trim();
column15 = rowValues[14].ToString().Trim();
column16 = rowValues[15].ToString().Trim();
column17 = rowValues[16].ToString().Trim();
column18 = rowValues[17].ToString().Trim();
column19 = rowValues[18].ToString().Trim();
column20 = rowValues[19].ToString().Trim();
column21 = rowValues[20].ToString().Trim();
column22 = rowValues[21].ToString().Trim();
column23 = rowValues[22].ToString().Trim();
column24 = rowValues[23].ToString().Trim();
column25 = rowValues[24].ToString().Trim();
column26 = rowValues[25].ToString().Trim();
column27 = rowValues[26].ToString().Trim();
column28 = rowValues[27].ToString().Trim();
column29 = rowValues[28].ToString().Trim();
column30 = rowValues[29].ToString().Trim();
column31 = rowValues[30].ToString().Trim();
column32 = rowValues[31].ToString().Trim();
column33 = rowValues[32].ToString().Trim();
column34 = rowValues[33].ToString().Trim();
column35 = rowValues[34].ToString().Trim();
column36 = rowValues[35].ToString().Trim();
column37 = rowValues[36].ToString().Trim();
#endregion
//Add
DataRow dr = FinalTable.NewRow();
#region adddata
dr[0] = column1;
dr[1] = column2;
dr[2] = column3;
dr[3] = column4;
dr[4] = column5;
dr[5] = column6;
dr[6] = column7;
dr[7] = column8;
dr[8] = column9;
dr[9] = column10;
dr[10] = column11;
dr[11] = column12;
dr[12] = column13;
dr[13] = column14;
dr[14] = column15;
dr[15] = column16;
dr[16] = column17;
dr[17] = column18;
dr[18] = column19;
dr[19] = column20;
dr[20] = column21;
dr[21] = column22;
dr[22] = column23;
dr[23] = column24;
dr[24] = column25;
dr[25] = column26;
dr[26] = column27;
dr[27] = column28;
dr[28] = column29;
dr[29] = column30;
dr[30] = column31;
dr[31] = column32;
dr[32] = column33;
dr[33] = column34;
dr[34] = column35;
dr[35] = column36;
dr[36] = column37;
dr[37] = i;
#endregion
FinalTable.Rows.Add(dr);
}
else
{
obj.WriteFailureLogFile("No Data in File");
}
}
}
else
{
obj.WriteFailureLogFile("No Data in File");
}
}
catch (Exception ex)
{
obj.WriteErrorLogFile(ex.ToString(),"Converting");
}
finally
{
sr.Close();
sr.Dispose();
}
return FinalTable;
}
Join the conversation! Your thoughts help the community grow.