smritijoshi
(Smritijoshi2709)
January 10, 2024, 9:30am
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!
rlgandu
(Rajyalakshmi Gandu)
January 10, 2024, 9:31am
2
smritijoshi:
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!
@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
lrtetala
(Lakshman Reddy)
January 10, 2024, 9:32am
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,
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!!
smritijoshi
(Smritijoshi2709)
January 10, 2024, 9:41am
5
Hi @lrtetala
I’m getting below error:
Assign: The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.
smritijoshi
(Smritijoshi2709)
January 10, 2024, 9:47am
6
This is saying syntax error at line 1 while invoking code
smritijoshi
(Smritijoshi2709)
January 10, 2024, 9:52am
7
Hi @rlgandu
Thanks for trying. But I am getting multiple errors using this.
lrtetala
(Lakshman Reddy)
January 10, 2024, 10:33am
8
@smritijoshi
Can you share sample excel file
lrtetala
(Lakshman Reddy)
January 10, 2024, 11:30am
9
@smritijoshi
Can you try the following?
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:
Output:
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!!
smritijoshi
(Smritijoshi2709)
January 10, 2024, 11:57am
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:
lrtetala
(Lakshman Reddy)
January 10, 2024, 11:58am
11
@smritijoshi
Can you please confirm about the H column date is 00000000
smritijoshi
(Smritijoshi2709)
January 10, 2024, 12:10pm
12
Sorry. Please Consider it as blank cell
lrtetala
(Lakshman Reddy)
January 10, 2024, 12:14pm
13
@smritijoshi
can you try below code
(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:
Output:
@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()
smritijoshi
(Smritijoshi2709)
January 10, 2024, 12:32pm
15
Getting below error:
Assign: Cannot set column ‘Date_1’. The value violates the MaxLength limit of this column.