Last post Mar 11, 2020 02:59 AM by NoobNoob
Mar 10, 2020 01:50 AM|NoobNoob|LINK
I have created program that converts Excel data to a CSV data. It moves excel data to a object datatable (I am using freespire.xls), and converts saves it in a CSV file. However, I need to delete a specific value from a column before saving it in a CSV file.
This is what the Excel data looks like:
I need to delete the time that is in the "Add Date Time" and "Status Date Time"columns.
Any Ideas on how to do it? This is my code for reference:
//moves excel data to datatable
Workbook book = new Workbook();
//imports excel data into datatable
DataTable excelTable = book.Worksheets.ExportDataTable();
Workbook data_book = new Workbook();
//worksheet.InsertDataTable(dataTable, columnHeaders, firstRow, firstColumn, transTypes);
data_book.Worksheets.InsertDataTable(excelTable, true, 1, 1);
// save to csv file
StringBuilder sb = new StringBuilder();
IEnumerable<string> columnNames = excelTable.Columns.Cast<DataColumn>().
Select(column => column.ColumnName);
foreach (DataRow row in excelTable.Rows)
IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
File.WriteAllText(@"C:\Users\NoobNoob\Documents\TEST FOLDER\test.csv", sb.ToString());
And last question. What is the best free third party library to export medium sized excel data to a DataTable? Excluded the interop and oleDb?
Mar 10, 2020 04:48 PM|smtaz|LINK
You can use the SetField method to modify the values in your DataTable.
I'm assuming your date columns are of string type. You could add this to your foreach statement;
var addDateTime = tableRow["Add Date Time"].ToString();
var index = addDateTime.IndexOf(" ");
row.SetField("Add Date Time", addDateTime.Replace(addDateTime.Substring(index), ""));
This would remove the time portion.
Mar 10, 2020 05:09 PM|Prathamesh Shende|LINK
it will not show you time in string
Mar 11, 2020 01:33 AM|NoobNoob|LINK
Thank you so much. your advise worked!
Mar 11, 2020 01:34 AM|NoobNoob|LINK
Thank you. I also tried this and it worked!
Mar 11, 2020 02:52 AM|yij sun|LINK
What is the best free third party library to export medium sized excel data to a DataTable? Excluded the interop and oleDb?
Accroding to your description, as far as I think,I suggest you could use ExcelDataReader library to export excel data to datatable.Besides,you could use the third party ExcelNumberFormat library for formatting purposes.
More details,you could refer to below articles:
Mar 11, 2020 02:59 AM|NoobNoob|LINK
Thank you for the tip, yij sun! I shall try and learn how to use exceldatareader.