Check row value and then change the date format using linq

Help Studio
1

Hello there,

I need help with one point.

I have a data table which can have blank rows as well. I want to check if the row value is blank and if not then change the date format using linq.

I have tried below:

(From row IO_DT_Data.AsEnumerable
Where Not String.IsNullOrEmpty(row(“Date”).ToString)
row(“Date”)=DateTime.ParseExact(row(“Date”).ToString.Trim, “yyyyMMdd”, System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”))).CopyToDatatable

The above snippet is not working. Can anyone help with this. Thanks in advance!

2

@smritijoshi

(From row In IO_DT_Data.AsEnumerable
Where Not String.IsNullOrEmpty(row(“Date”).ToString.Trim)
Select row.Field(Of DateTime)(“Date”).ToString(“MM/dd/yyyy”)
).CopyToDataTable

3

Hi @smritijoshi

Can you try the below code

IO_DT_Data = (From row In IO_DT_Data.AsEnumerable()
              Where Not String.IsNullOrEmpty(row("Date").ToString.Trim)
              Select IO_DT_Data.Clone().Rows.Add(row.ItemArray.Take(row.Table.Columns.Count - 1).ToArray() _
                                                  .Concat(New Object() {DateTime.ParseExact(row("Date").ToString.Trim, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy")}).ToArray())
             ).CopyToDataTable()

Regards,

4

Hi @smritijoshi

(From row In dtInput.AsEnumerable Where Not String.IsNullOrEmpty(row("Date").ToString)Select dtInput.Clone.Rows.Add(row("Date"),DateTime.ParseExact(row("Date").ToString.Trim, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy"))).CopyToDataTable()

Hope it helps!!

5

Hi @lrtetala

I’m getting below error:
Assign: The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.

6

This is saying syntax error at line 1 while invoking code

7

Hi @rlgandu

Thanks for trying. But I am getting multiple errors using this.
image

8

@smritijoshi

Can you share sample excel file

9

@smritijoshi

Can you try the following?

image
image900×581 29.5 KB

Code:

(From row In inputDataTable.AsEnumerable
 Select inputDataTable.Clone().Rows.Add(
    row("S.No").ToString,If(String.IsNullOrEmpty(row("Date").ToString), "", DateTime.ParseExact(row("Date").ToString.Trim, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy"))
 )
).CopyToDataTable()

Input:

image
image363×506 4.41 KB

Output:

image

Sequence.xaml (13.7 KB)

If you have only date column then try below code

(From row In inputDataTable.AsEnumerable
 Select inputDataTable.Clone().Rows.Add(
    If(String.IsNullOrEmpty(row("Date").ToString), "", DateTime.ParseExact(row("Date").ToString.Trim, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy"))
 )
).CopyToDataTable()

Cheers!!

10

Okay I will try this.

I have 3 columns for which I have to convert it to req date format: Date_1, Date_2 & Date_3

Meanwhile sample input:
image

11

@smritijoshi

Can you please confirm about the H column date is 00000000

12

Sorry. Please Consider it as blank cell

13

@smritijoshi

can you try below code

image
image900×581 29.5 KB

(From row In inputDataTable.AsEnumerable
 Select inputDataTable.Clone().Rows.Add(
    row("Middle Name"),
    row("Last Name"),
    row("First Name"),
    row("Title"),
    row("Label"),
    row("Sample"),
    If(String.IsNullOrEmpty(row("Date_1").ToString), "", DateTime.ParseExact(row("Date_1").ToString.Trim, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy")),
    If(String.IsNullOrEmpty(row("Date_2").ToString), "", DateTime.ParseExact(row("Date_2").ToString.Trim, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy")),
    row("City"),
    If(String.IsNullOrEmpty(row("Date_3").ToString), "", DateTime.ParseExact(row("Date_3").ToString.Trim, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy")),
    row("Status")
 )
).CopyToDataTable()

Input:

image
image944×179 4.56 KB

Output:

image
image969×205 4.89 KB

14

@smritijoshi

Try this

(From row In inputDataTable.AsEnumerable()
Select inputDataTable.Clone().Rows.Add(
If(Not String.IsNullOrEmpty(row.Field(Of String)("Date")) AndAlso Not row.Field(Of String)("Date").Trim().Equals("00000000"),
DateTime.ParseExact(row.Field(Of String)("Date").Trim(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy"),""),
If(Not String.IsNullOrEmpty(row.Field(Of String)("Date1")) AndAlso Not row.Field(Of String)("Date1").Trim().Equals("00000000"),
DateTime.ParseExact(row.Field(Of String)("Date1").Trim(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy"),""),
If(Not String.IsNullOrEmpty(row.Field(Of String)("Date2")) AndAlso Not row.Field(Of String)("Date2").Trim().Equals("00000000"),
DateTime.ParseExact(row.Field(Of String)("Date2").Trim(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy"),"")
)
).CopyToDataTable()
15

Getting below error:
Assign: Cannot set column ‘Date_1’. The value violates the MaxLength limit of this column.