3

I import data from a CSV file into a datatable.

Existing vendors send period as '201901' in one column. A new vendor sends period as 2 columns like year '2019' and month 'JAN'.

I want to combine the 2 columns into '201901' so that the rest of the code logic works as earlier.

When I looked at the documentation for Datatable.Columns.Add , I see all the examples are just numeric expressions only, nothing related to string or dates in the column expressions.

Here is my Code.. These are from CSV file so when imported to datatable, these columns have 2019 and JAN data. 

DT.Columns.Add("MAT_YEAR", typeof(string));  

DT.Columns.Add("MAT_MONTH", typeof(string));

I want to get the computed value based on above 2 columns

DT.Columns.Add("MONTHNUM", typeof(int),"DateTime.ParseExact(MAT_MONTH,\"MMM\",null).Month");

DT.Columns.Add("MAT_PERIOD", typeof(string), "MAT_YEAR + Convert.ToString(MONTHNUM)");

I am getting error on DateTime.Parse

How can I create computed columns in Datatable based on string or dates?

PaulG
  • 13,871
  • 9
  • 56
  • 78
Sak
  • 87
  • 6
  • The capabilities of the expression engine of ADO.NET is pretty limited. You can't include arbitraty .NET code inside an expression. Here is what you can do: [Expression Syntax](https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression). – Theodor Zoulias May 30 '19 at 15:44

1 Answers1

0

Perhaps try the following.

DT.Columns.Add("MAT_YEAR", typeof(string));
DT.Columns.Add("MAT_MONTH", typeof(string));

DT.Columns.Add("MONTHNUM", typeof(string),"DateTime.ParseExact(\"MAT_MONTH + \"01 2000\", \"MMM dd yyyy\", CultureInfo.InvariantCulture).Month.ToString(\"00\");
DT.Columns.Add("MAT_PERIOD", typeof(string), "MAT_YEAR + MONTHNUM");

All credit to Charles May, completely correct

DubDub
  • 1,277
  • 1
  • 10
  • 24
  • Also, if anyone knows of an inbuilt method to do this, pleased inform me. I'm sure there is one, however I can't remember/ find it. – DubDub May 30 '19 at 13:32
  • This didn't work.. I get exception.. The Expression contains undefined function call ReturnMonthIndex() – Sak May 30 '19 at 14:10
  • Couldn't you just `return DateTime.ParseExact(month + " 01 2000", "MMM dd yyyy", CultureInfo.InvariantCulture).Month.ToStrint("00");` after all, any day or year will still produce the same month number. – Charles May May 30 '19 at 14:11
  • I've updated the answer to incorporate CharlesMay's solution. @CharlesMay You definitely can, and makes a lot more sense, thanks. – DubDub May 30 '19 at 14:24
  • This too didn't work. Getting Exception : The expression contains undefined function call DateTime.ParseExact.. Same as the one i got first time.. Looks like issue using Functions in Datatable expression. I am thinking now to populate the column by using foreach row after data is populated in the datatable. – Sak May 30 '19 at 15:07