Last post Nov 25, 2015 12:57 PM by Weibo Zhang
Nov 19, 2015 08:04 AM|Jack Hunt|LINK
I have one excel and I import it into data table.
Excel have field "Date"(MM/dd/yyyy format) and which will be stored into datatable as string as per local system format (where win service hosted).
Now I want to convert that column data into MM/dd/yyyy format using some bulk function(Because excel have too many records).
is there any way?
I have tried using following way but its throw an exception.
Date,101)")//Here Date is column which would be format as per local enviorment
Nov 19, 2015 09:43 PM|oned_gk|LINK
Try this after add DateNew Column
dt.Columns["DateNew"].Expression = "convert(varchar(10), [Date],101)";
Nov 19, 2015 10:28 PM|Weibo Zhang|LINK
Hi Jack Hunt,
I think you could convert the date values from the excel file to formatted string before storing them into DataTable. You could use OLEDB OleDbCommand string “SELECT FORMAT([DateCol], 'MM/dd/yyyy') from SheetName” to implement it or refer to the following
thread to do it. So that, you don’t need to do the extra work to modify the format of DataTable column.
I hope it’s useful to you.
Nov 20, 2015 01:06 AM|Jack Hunt|LINK
@Oned_gk : This convertion give me following exception. Is there any other way?
The expression contains undefined function call varchar().
@Weibo Zhang : I am using lumenworks CSV reader to import excel/csv so will there posible to convert column?
Nov 20, 2015 04:51 AM|Weibo Zhang|LINK
I think it’s not easy to achieve your need without using loop. You could just modify your CSV value type as string with the “MM/dd/yyyy” format or don’t modify the format before using it. When you using this new datatable, you could modify it just according
to your need, such as binding to GridView by using “Eval()” etc.
Besides, if you change your mind and accept the loop method, the following thread provides a simple solution to achieve the conversion and you could have a look.
Nov 20, 2015 06:27 AM|Jack Hunt|LINK
@Weibo Zhang : My csv and excel should always have date in MM/dd/yyyy format. But when i have fetch data from excel using windows service it converts my date as per local system. My service and DB both have different environment. At service enviorment date
format is dd-MM-yyyy and in excel date as MM/dd/yyyy (Formatted) so when i import it into datatable it converts to dd-MM-yyyy. Thats my problem.
Note : Service environment are not fixed.
Nov 20, 2015 07:15 AM|mgebhard|LINK
@Weibo Zhang : My csv and excel should always have date in MM/dd/yyyy format. But when i have fetch data from excel using windows service it converts my date as per local system.
This statement makes no sense. The only time a date format changes according to the environment is when using an actual DateTime type. The system will display the date according to the current system setting but the actual DateTime type is a date regardless
of the system.
My service and DB both have different environment. At service enviorment date format is dd-MM-yyyy and in excel date as MM/dd/yyyy (Formatted) so when i import it into datatable it converts to dd-MM-yyyy. Thats my problem.
This seems a bit strange as well. What is the type in the DateBase? If it is a DateTime type then you are good to go... just convert the type to whatever format you want when querying the data.
Nov 24, 2015 06:41 AM|Jack Hunt|LINK
My service read data from csv/excel and insert into database as string (nvarchar(max)). After this my Store Proc. execute and it will try to convert that string date into datetime.
Above is flow but if i have different enviorement then it will be conflicted.
Nov 25, 2015 12:57 PM|Weibo Zhang|LINK
I have created a demo refer to the following thread and download the lumenworks 2.0 from the link in this thread, it works on my client. The date type column values would be string in the new DataTable with retaining the format. You could download the new
dll and then use the following code to have a try.
using (var csv = new CachedCsvReader(new StreamReader(filePath), true))
DataTable Table = new DataTable();