Merge DateTime type column

[Manisha24 (MJ001)]

Hi,
I have on table with few data in that i have one datetime variable called published date, i want to merge this column into one single column.
i know to merge string type column but how to merge column of datetime type.
sample input:

excpected output:

I’m using linq query to merge string type column : (From row In dtOriginal.AsEnumerable()
Group row By BookID = row(“BookID”) Into Group
Select dtOriginal.Clone.Rows.Add(BookID, String.Join(“,”,Group.Select(Function(r) r(“BookName”).ToString())))).CopyToDataTable()

How do I use linq query for datetime column as well?

[vrdabberu (Varunraj Dabberu)]

Hi @Manisha24

Check the below LINQ expression:

(From row In dtOriginal.AsEnumerable()
Group row By BookID = row("BookID") Into Group
Select dtOriginal.Clone.Rows.Add(BookID, 
    String.Join(",", Group.Select(Function(r) r("BookName").ToString())), 
    String.Join(",", Group.Select(Function(r) Convert.ToDateTime(r("PublishesDate")).ToString("MM/dd/yyyy"))))
).CopyToDataTable()

Regards

[aravindbalineni123 (Aravind Balineni)]

Try This:

(From row In dtOriginal.AsEnumerable()
Group row By BookID = row(“BookID”) Into Group
Select dtOriginal.Clone.Rows.Add(BookID,
String.Join(“,”, Group.Select(Function(r) r(“BookName”).ToString())),
String.Join(“,”, Group.Select(Function(r) DateTime.ParseExact(r(“PublishesDate”).ToString,“dd-MM-yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”))), row(“Price”).ToString
).CopyToDataTable()

[Manisha24 (MJ001)]

Hi @vrdabberu ,

This query is not working when we have null value stored in published date column. Please help.

[Yoichi]

HI,

How about the following?

dt.AsEnumerable.GroupBy(Function(r) r("BookID").ToString).Select(Function(g) dt.Clone.LoadDataRow({g.Key,String.Join(",",g.Select(Function(r) r("BookName").ToString)),String.Join(",",g.Where(Function(r) not String.IsNullOrEmpty(r("PublishesDate").ToString)).Select(Function(r) r("PublishesDate").ToString)),String.Join(",",g.Select(Function(r) r("Price").ToString).Distinct())},False)).CopyToDataTable()

Sample
Sample20240906-4.zip (9.5 KB)

Regards,

[Manisha24 (MJ001)]

Hi @Yoichi, query is working fine when we have string type columns but in my case I have a datetime column “publishes date” and it contains some date and some null value I need a similar query which covers datetime column as well.

[vrdabberu (Varunraj Dabberu)]

Hi @Manisha24

Please check with the below query:

(From row In dtOriginal.AsEnumerable()
 Group row By BookID = row("BookID") Into Group
 Select dtOriginal.Clone.Rows.Add(
     BookID,
     String.Join(",", Group.Select(Function(r) r("BookName").ToString())),
     String.Join(",", Group.Select(Function(r) If(IsDBNull(r("PublishesDate")), String.Empty, Convert.ToDateTime(r("PublishesDate")).ToString("MM/dd/yyyy"))))
 )).CopyToDataTable()

Regards

[Yoichi]

Hi,

Can you share your input and expected output as file? it’s no problem if dummy data.

Regards,

[Manisha24 (MJ001)]

Hi @Yoichi ,
Input Sample
TestData.xlsx (8.5 KB)
expected output :

the column publishesDate is of the type Datetime and rest all are string type

[Yoichi]

HI,

Can you try the following sample?

dt.AsEnumerable.GroupBy(Function(r) r("BookID").ToString).Select(Function(g) dt.Clone.LoadDataRow({g.Key,String.Join(",",g.Where(Function(r) not String.IsNullOrEmpty(r("BookName").ToString)).Select(Function(r) r("BookName").ToString)),String.Join(",",g.Where(Function(r) not String.IsNullOrEmpty(r("PublishesDate").ToString)).Select(Function(r) CDate(r("PublishesDate").ToString).ToString("dd-MM-yyyy"))),String.Join(",",g.Where(Function(r) not String.IsNullOrEmpty(r("Price").ToString)).Select(Function(r) r("Price").ToString).Distinct())},False)).CopyToDataTable()

Sample
Sample20240906-4 (2).zip (15.8 KB)

Output

[Manisha24 (MJ001)]

hi @Yoichi ,

Assign: Conversion from string “null” to type ‘Date’ is not valid. This is the error I’m getting.
TestData.xlsx (8.4 KB)

[Yoichi]

Hi,

Do you have this error from the above sample? Or another input file?
If latter, the following expression may be better.

dt.AsEnumerable.GroupBy(Function(r) r("BookID").ToString).Select(Function(g) dt.Clone.LoadDataRow({g.Key,String.Join(",",g.Where(Function(r) DateTime.TryParse(r("BookName").ToString,Nothing)).Select(Function(r) r("BookName").ToString)),String.Join(",",g.Where(Function(r) r("PublishesDate") isnot Nothing AndAlso (not String.IsNullOrEmpty(r("PublishesDate").ToString))).Select(Function(r) CDate(r("PublishesDate").ToString).ToString("dd-MM-yyyy"))),String.Join(",",g.Where(Function(r) not String.IsNullOrEmpty(r("Price").ToString)).Select(Function(r) r("Price").ToString).Distinct())},False)).CopyToDataTable()

[Manisha24 (MJ001)]

@Yoichi
I’m getting that error when using this sample
TestData.xlsx (8.4 KB)
where i kept null in the publishes date column.

[Yoichi]

It’s my bad. I had modified at wrong place. The following expression will work.

dt.AsEnumerable.GroupBy(Function(r) r("BookID").ToString).Select(Function(g) dt.Clone.LoadDataRow({g.Key,String.Join(",",g.Where(Function(r) not String.IsNullOrEmpty(r("BookName").ToString)).Select(Function(r) r("BookName").ToString)),String.Join(",",g.Where(Function(r) r("PublishesDate") isnot Nothing AndAlso DateTime.TryParse(r("PublishesDate").ToString,Nothing)).Select(Function(r) CDate(r("PublishesDate").ToString).ToString("dd-MM-yyyy"))),String.Join(",",g.Where(Function(r) not String.IsNullOrEmpty(r("Price").ToString)).Select(Function(r) r("Price").ToString).Distinct())},False)).CopyToDataTable()

Sample
Sample20240906-4 (3).zip (22.0 KB)

Regards,

[Manisha24 (MJ001)]

@Yoichi , It worked now.
Thanks a lot for your patience!!