[フレーム]

Converting CSV/MCX Files to DataTable and Bulk Insert into SQL Server in C#

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

Key Points

  • StreamReader reads the entire file.

  • Rows are split by newline \n and columns by comma ,.

  • Each row is added to the DataTable dynamically.

  • A Count column tracks the row index.

2. Filtering Data

After loading the MCX data, you can filter rows using a DataView:

DataTable FilteredData = MCXdata;
DataView dvView = FilteredData.DefaultView;
dvView.RowFilter = "Count > " + TotalRowCount; // Example: filter by Count column
DataTable dtFiltered = dvView.ToTable();
FinalTable = dtFiltered;

Notes:

  • RowFilter supports expressions similar to SQL WHERE.

  • dvView.ToTable() returns a filtered copy of the DataTable.

3. Bulk Insert into SQL Server

Using SqlBulkCopy, large datasets can be inserted efficiently:

if (FinalTable.Rows.Count > 0)
{
 using (SqlConnection con = new SqlConnection("Data Source=173.47.478.2;Initial Catalog=AS78955;User ID=sa;Password=N@yyui#DB&12$%"))
 {
 con.Open();
 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
 {
 bulkCopy.BulkCopyTimeout = 1000000; // Large timeout for big files
 bulkCopy.DestinationTableName = "DTUMCX";
 // Map columns
 for (int i = 1; i <= 37; i++)
 bulkCopy.ColumnMappings.Add("column" + i, "column" + i);
 
 bulkCopy.ColumnMappings.Add("Count", "Count");
 bulkCopy.WriteToServer(FinalTable);
 }
 con.Close();
 obj.WriteProcessLogFile("Bulk inserted Successfully. Total Rows - " + FinalTable.Rows.Count);
 }
}
(or)
if (FinalTable.Rows.Count > 0)
 {
 SqlConnection con = new SqlConnection("Data Source=173.47.478.2;Initial Catalog=AS78955;User ID=sa;Password=N@yyui#DB&12$%");
 con.Open();
 SqlBulkCopy bulkCopy = new SqlBulkCopy(con);
 bulkCopy.BulkCopyTimeout = 1000000;
 bulkCopy.DestinationTableName = "DTUMCX";
 string TotalCount = FinalTable.Rows.Count.ToString();
 try
 {
 bulkCopy.ColumnMappings.Add("column1", "column1");
 bulkCopy.ColumnMappings.Add("column2", "column2");
 bulkCopy.ColumnMappings.Add("column3", "column3");
 bulkCopy.ColumnMappings.Add("column4", "column4");
 bulkCopy.ColumnMappings.Add("column5", "column5");
 bulkCopy.ColumnMappings.Add("column6", "column6");
 bulkCopy.ColumnMappings.Add("column7", "column7");
 bulkCopy.ColumnMappings.Add("column8", "column8");
 bulkCopy.ColumnMappings.Add("column9", "column9");
 bulkCopy.ColumnMappings.Add("column10", "column10");
 bulkCopy.ColumnMappings.Add("column1", "column11");
 bulkCopy.ColumnMappings.Add("column12", "column12");
 bulkCopy.ColumnMappings.Add("column13", "column13");
 bulkCopy.ColumnMappings.Add("column14", "column14");
 bulkCopy.ColumnMappings.Add("column15", "column15");
 bulkCopy.ColumnMappings.Add("column16", "column16");
 bulkCopy.ColumnMappings.Add("column17", "column17");
 bulkCopy.ColumnMappings.Add("column18", "column18");
 bulkCopy.ColumnMappings.Add("column19", "column19");
 bulkCopy.ColumnMappings.Add("column20", "column20");
 bulkCopy.ColumnMappings.Add("column21", "column21");
 bulkCopy.ColumnMappings.Add("column22", "column22");
 bulkCopy.ColumnMappings.Add("column23", "column23");
 bulkCopy.ColumnMappings.Add("column24", "column24");
 bulkCopy.ColumnMappings.Add("column25", "column25");
 bulkCopy.ColumnMappings.Add("column26", "column26");
 bulkCopy.ColumnMappings.Add("column27", "column27");
 bulkCopy.ColumnMappings.Add("column28", "column28");
 bulkCopy.ColumnMappings.Add("column29", "column29");
 bulkCopy.ColumnMappings.Add("column30", "column30");
 bulkCopy.ColumnMappings.Add("column31", "column31");
 bulkCopy.ColumnMappings.Add("column32", "column32");
 bulkCopy.ColumnMappings.Add("column33", "column33");
 bulkCopy.ColumnMappings.Add("column34", "column34");
 bulkCopy.ColumnMappings.Add("column35", "column35");
 bulkCopy.ColumnMappings.Add("column36", "column36");
 bulkCopy.ColumnMappings.Add("column37", "column37");
 bulkCopy.WriteToServer(FinalTable);
 con.Close();
 obj.WriteProcessLogFile("Bulk inserted SuccessFully.Total Rows - " + TotalCount);
 }

Key Points

  • SqlBulkCopy is optimized for inserting large volumes of data.

  • Column mappings ensure DataTable columns match SQL table columns.

  • BulkCopyTimeout can be increased for very large files.

4. Error Handling and Logging

  • try-catch-finally ensures errors are logged and resources are released.

  • obj.WriteFailureLogFile logs missing or malformed rows.

  • obj.WriteErrorLogFile logs exceptions during conversion.

5. Advantages of this Approach

  1. Efficiency: Handles large MCX files efficiently.

  2. Maintainability: Adding or removing columns is straightforward.

  3. Filtering: Easy to filter rows dynamically before insert.

  4. Logging: Helps track processing errors and missing data.

  5. Automation: Can be scheduled to process daily market data files automatically.

6. Example Workflow

  1. Load MCX CSV file using convertdatatableMCX.

  2. Filter rows based on a condition (e.g., Count > TotalRowCount).

  3. Bulk insert the filtered data into DTUMCX SQL Server table.

  4. Log success or failure messages for auditing.

Conclusion

This approach is ideal for financial applications dealing with large MCX or stock market datasets. By combining DataTable conversion, DataView filtering, and SqlBulkCopy, you can achieve efficient, reliable, and maintainable data processing pipelines in C#.

People also reading
Membership not found

AltStyle によって変換されたページ (->オリジナル) /