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
book.LoadFromFile(excelFilePath);
DataTable excelTable = book.Worksheets[0].ExportDataTable();
Workbook data_book = new Workbook();
//worksheet.InsertDataTable(dataTable, columnHeaders, firstRow, firstColumn, transTypes);
data_book.Worksheets[0].InsertDataTable(excelTable, true, 1, 1);
//data_book.SaveToFile(resultFilePath, ExcelVersion.Version2010);
// save to csv file
StringBuilder sb = new StringBuilder();
IEnumerable<string> columnNames = excelTable.Columns.Cast<DataColumn>().
Select(column => column.ColumnName);
sb.AppendLine(string.Join("|", columnNames));
foreach (DataRow row in excelTable.Rows)
{
IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
sb.AppendLine(string.Join("|", fields));
}
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?
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), ""));
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.
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
12 Points
28 Posts
How To Delete a Specific Value in a Column
Mar 10, 2020 01:50 AM|NoobNoob|LINK
Hi,
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 book.LoadFromFile(excelFilePath); DataTable excelTable = book.Worksheets[0].ExportDataTable(); Workbook data_book = new Workbook(); //worksheet.InsertDataTable(dataTable, columnHeaders, firstRow, firstColumn, transTypes); data_book.Worksheets[0].InsertDataTable(excelTable, true, 1, 1); //data_book.SaveToFile(resultFilePath, ExcelVersion.Version2010); // save to csv file StringBuilder sb = new StringBuilder(); IEnumerable<string> columnNames = excelTable.Columns.Cast<DataColumn>(). Select(column => column.ColumnName); sb.AppendLine(string.Join("|", columnNames)); foreach (DataRow row in excelTable.Rows) { IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString()); sb.AppendLine(string.Join("|", fields)); } 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?
Thank you.
Regards,
NoobNoob
Member
60 Points
40 Posts
Re: How To Delete a Specific Value in a Column
Mar 10, 2020 04:48 PM|smtaz|LINK
You can use the SetField method to modify the values in your DataTable.
https://docs.microsoft.com/en-us/dotnet/api/system.data.datarowextensions.setfield?view=netframework-4.8
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.
Member
75 Points
161 Posts
Re: How To Delete a Specific Value in a Column
Mar 10, 2020 05:09 PM|Prathamesh Shende|LINK
dont delete
use String.Format("{0:MM-dd-yyyy}",AddDateTime);
it will not show you time in string
Member
12 Points
28 Posts
Re: How To Delete a Specific Value in a Column
Mar 11, 2020 01:33 AM|NoobNoob|LINK
Thank you so much. your advise worked!
Regards,
NoobNoob
Member
12 Points
28 Posts
Re: How To Delete a Specific Value in a Column
Mar 11, 2020 01:34 AM|NoobNoob|LINK
Thank you. I also tried this and it worked!
Regards,
NoobNoob
Contributor
3730 Points
1424 Posts
Re: How To Delete a Specific Value in a Column
Mar 11, 2020 02:52 AM|yij sun|LINK
Hi NoobNoob,
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:
https://github.com/ExcelDataReader/ExcelDataReader
https://stackoverflow.com/questions/27634477/using-exceldatareader-to-read-excel-data-starting-from-a-particular-cell/46022604
Best regards,
Yijing Sun
Member
12 Points
28 Posts
Re: How To Delete a Specific Value in a Column
Mar 11, 2020 02:59 AM|NoobNoob|LINK
Thank you for the tip, yij sun! I shall try and learn how to use exceldatareader.
Regards,
NoobNoob