Remove character and format date in DataTable

Help Studio
, ,
1

Hi,

I want to remove the USD from excel column and format the date to to one format MMDDYYYY . Can I do this in single link query

image
image1363×311 42.5 KB

Error in Regex Expression
2

Hi @marina.dutta

Could you try the below linq expression,

(From row In Input_dt
Let TotalContractValue = If(System.Text.RegularExpressions.Regex.IsMatch(row("Total Contract Value").toString,"USD\s*\$[\d,.]+"),System.Text.RegularExpressions.Regex.Replace(row("Total Contract Value").toString,"USD\s*",""),row("Total Contract Value").toString)
Let EndDate = If(System.Text.RegularExpressions.Regex.IsMatch(row("EndDate").toString,"[A-Za-z]{3}\s?\d{2}\,\s?\d{4}"),DateTime.ParseExact(row("EndDate").toString,"MMM dd, yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("MMddyyyy"),row("EndDate".toString))
Select Input_dt.Clone().Rows.Add({row("QuoteNum"),row("StartDate"),row("PaymentTerm"),TotalContractValue,row("Primary Co Scheduling"),EndDate})
                                      ).Copytodatatable()

Note: Change the column names according to your Excel Columns.

Hope it helps!!

3

@mkankatala

Before that I am getting an empty datatable error. I tagged u in the link

4

Have you tried the above linQ expression is it working fine for you… @marina.dutta

5

Hey @marina.dutta

try this code:

For Each row As DataRow In YourDataTable.Rows
    If row("YourCurrencyColumn").ToString.Contains("USD") Then
        row("YourCurrencyColumn") = row("YourCurrencyColumn").ToString.Replace("USD", "").Trim()
    End If

    Dim parsedDate As DateTime
    If DateTime.TryParseExact(row("YourDateColumn").ToString, "MMM dd, yyyy", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then
        row("YourDateColumn") = parsedDate.ToString("MMddyyyy")
    End If
Next
6

Hi @marina.dutta

Try this

Dim dt = DataTable.FromDataRows(dtExcelData.Rows.Cast(Of DataRow)().AsEnumerable().Select(Function(row)
Dim formattedDate = DateTime.Parse(row.Field(Of String)(“QuoteNum StartDate”)).ToString(“MMddyyyy”)
Dim formattedValue = Decimal.Parse(row.Field(Of String)(“PaymentToTotal Contract Value”).Replace("$ ", “”))
Return New With {
Key .QuoteNum_StartDate = formattedDate,
Key .PaymentToTotal_Contract_Value = formattedValue
}
End Function).ToList())

7

@mkankatala

If I need to format Startdate also same thing I should do as Let EndDate ?

8

Yes @marina.dutta

Same as EndDate create one more with StartDate. I have modified the above linQ Expression, Check the below one,

(From row In Input_dt
Let TotalContractValue = If(System.Text.RegularExpressions.Regex.IsMatch(row("Total Contract Value").toString,"USD\s*\$[\d,.]+"),System.Text.RegularExpressions.Regex.Replace(row("Total Contract Value").toString,"USD\s*",""),row("Total Contract Value").toString)
Let StartDate = If(System.Text.RegularExpressions.Regex.IsMatch(row("StartDate").toString,"[A-Za-z]{3}\s?\d{2}\,\s?\d{4}"),DateTime.ParseExact(row("StartDate").toString,"MMM dd, yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("MMddyyyy"),row("StartDate".toString))
Let EndDate = If(System.Text.RegularExpressions.Regex.IsMatch(row("EndDate").toString,"[A-Za-z]{3}\s?\d{2}\,\s?\d{4}"),DateTime.ParseExact(row("EndDate").toString,"MMM dd, yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("MMddyyyy"),row("EndDate".toString))
Select Input_dt.Clone().Rows.Add({row("QuoteNum"),StartDate,row("PaymentTerm"),TotalContractValue,row("Primary Co Scheduling"),EndDate})
                                      ).Copytodatatable()

Hope you understand!!

9

@mkankatala

USD and Date format did not change

image

10

Could you share your input file, then I’ll check and let you know… @marina.dutta

11

@mkankatala
Salesforce_Output.xlsx (6.8 KB)

Results.xlsx (8.5 KB)

Basically my intention here is to compare the two excel column values and find any match or unmatch values. If there are any unmatch values of columns between two excel sheets I need to get those column values and paste in the third excel sheet of Result Excelsheet. for example

The QuotenNUM columns are matching, then I need to compare the StartDate and End Date Column of Two Excel Sheet and find if any unmatch value in Date column then paste that value in third excel sheet. (Forthat I need to format the dates in both sheet into a single format).

Third ,similarly For TotalContractValue Column I can ignore the USD and try to find the match values between two excel sheet. (Dollar sign can be there or we can ignore ).

Like this I need to compare Column values of both excel sheet and find the unmatch and paste those unmatch values with column names in Third sheet of Results.

Please help on this

12

@mkankatala