Hai,
I have one column NTD/NTE data is 1/12/2024 (MDY) format i want 12/1/2024 (DMY). change whole column without foreach because i have so many rows . foreach affect performance.
Regards
@MD_Farhan1
Read range the Excel
In read range Enable Preserve Format
YourDataTable.AsEnumerable().ToList().ForEach(Sub(row) row(“YourDateColumn”) = DateTime.ParseExact(row(“YourDateColumn”).ToString, “M/d/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“d/M/yyyy”))
Input:
Output:
Hi @MD_Farhan1
Can you try this query:
(From row In dt1.AsEnumerable()
Let originalDate = DateTime.ParseExact(row.Field(Of String)("YourOriginalDateColumn"), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture)
Select transformedRow = dt1.Clone().Rows.Add(row.ItemArray.Concat({originalDate.ToString("dd/MM/yyyy")}).ToArray())
).CopyToDataTable()
Make sure to change the datatable variable.
Regards
Hi @MD_Farhan1
Try this since your date format is yyyy-MM-dd it throwed you an error.
(From row In dt1.AsEnumerable()
Let originalDate = DateTime.ParseExact(row.Field(Of String)("YourOriginalDateColumn"), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture)
Select transformedRow = dt1.Clone().Rows.Add(row.ItemArray.Concat({originalDate.ToString("dd/MM/yyyy")}).ToArray())
).CopyToDataTable()
Regards
PLease try my approach It works
What argument you give for invoke code
HI @MD_Farhan1
Bro could you please share the input excel with the specific dates as per your original date format in your excel.
Regards
I got Error
i want particular column. not all column
1 Like
@MD_Farhan1
Sequence9.zip (2.0 KB)
Please give the Format correctly give the exact format as it in Excel.Enable Preserve format in Read Range
Hi @MD_Farhan1
Use the below code in Invoke Code bro:
' Assuming dt is your DataTable and "NTD/NTE" is the column name
For Each row As DataRow In dt.Rows
Dim originalDate As DateTime = DateTime.ParseExact(row("NTD/NTE").ToString(), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture)
row("NTD/NTE") = originalDate.ToString("dd/MM/yyyy")
Next
Invoked arguments:
Regards
1 Like
Considering the sample input as below:
Make sure to enable Preserve Format option in Read Range.
Code to be used in Invoke Code:
' Assuming dt is your DataTable and "NTD/NTE" is the column name
For Each row As DataRow In dt.Rows
Dim originalDate As DateTime = DateTime.ParseExact(row("NTD/NTE").ToString(), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture)
row("NTD/NTE") = originalDate.ToString("dd/MM/yyyy")
Next
Invoked arguments:
Output:
Workflow:
Rergards
1 Like