Need Linq query

Hi Everyone,

My data table has about 22000+ record…i want to loop through each row and update value in column logo…we’ll get two values in logo column either number or string…i want to check if it is number then check length of number if length is 1 it should add “000” as a prefix for example if it is 9 output of row should be 0009 if length is 2 then it should add two zero for example if logo is “29” then output should be “0029”.if length is 3 it should add only one zero as a prefix for example - if logo is 168 then output should be “0168”
If length is 4 we need to move ro next row and if it is string we need to move to next row

Input Datatable sample

Logo
9
123
22
4567
ABCD
ZA90

Output datatable should be

Logo
0009
0123
0022
4566
ABCD
ZA90

Can someone help with writing linq query for same ?

Thanks in Advance

2 Likes

Hi @Deepak_Chawla
Try this:

updatedDataTable = (From row In yourDataTable.AsEnumerable()
                   Let logoValue = If(Integer.TryParse(row.Field(Of String)("Logo"), 0),
                                      Integer.Parse(row.Field(Of String)("Logo")),
                                      -1)
                   Let updatedLogo = If(logoValue >= 0,
                                       If(logoValue < 10, "000" + logoValue.ToString(),
                                       If(logoValue < 100, "00" + logoValue.ToString(),
                                       If(logoValue < 1000, "0" + logoValue.ToString(),
                                       logoValue.ToString()))), row.Field(Of String)("Logo"))
                   Select updatedLogo).CopyToDataTable()

Hope it helps

What is logo value and updated logo ?

Hi @Deepak_Chawla
The below explanation will clarify all your queries.

  1. From row In yourDataTable.AsEnumerable(): This part of the query is creating a sequence of rows from your DataTable named yourDataTable. It allows you to iterate through each row in the DataTable.

  2. Let logoValue = If(Integer.TryParse(row.Field(Of String)("Logo"), 0), Integer.Parse(row.Field(Of String)("Logo")), -1): Here, it defines a new variable logoValue. It attempts to parse the “Logo” column value of each row as an integer using Integer.TryParse. If the parsing is successful, it stores the parsed integer value in logoValue. If parsing fails, it assigns -1 to logoValue.

  3. Let updatedLogo = If(logoValue >= 0, If(logoValue < 10, "000" + logoValue.ToString(), If(logoValue < 100, "00" + logoValue.ToString(), If(logoValue < 1000, "0" + logoValue.ToString(), logoValue.ToString()))), row.Field(Of String)("Logo")): This part calculates the updatedLogo based on the value of logoValue. If logoValue is greater than or equal to 0 (indicating it’s a numeric value), it formats it by adding leading zeros based on the length of the numeric value.

    • If logoValue is less than 10, it adds three zeros.
    • If it’s between 10 and 99, it adds two zeros.
    • If it’s between 100 and 999, it adds one zero.
    • If it’s 1000 or more, it doesn’t add any zeros.

    If logoValue is negative (indicating it’s not a numeric value), it leaves updatedLogo as the original value from the “Logo” column.

  4. Select updatedLogo: This part selects the calculated updatedLogo value for each row in the DataTable.

  5. .CopyToDataTable(): Finally, it converts the sequence of updatedLogo values into a new DataTable, which is assigned to the updatedDataTable variable.

Assumption: As shown above a 1 col only datatable

Assign Activity
dtCleansed = dtOrig.Clone

Assign Activity
dtCleansed =

(From d in dtOrig.AsEnumerable
Let lc = d(0).toString.Trim.PadLeft(4,"0"c)
Let ra = new Object(){kc}
Select r = dtCleansed.Rows.Add(ra)).CopyToDataTable

Hey @Deepak_Chawla ,

Below is the linq

(From row In dt.AsEnumerable() Let logo = row("Logo").ToString().Trim() Let length = logo.Length Let updatedLogo = If(Integer.TryParse(logo, 0),If(length < 4, Integer.Parse(logo).ToString("D4"), logo),logo)Select dt.Clone.Rows.Add(updatedLogo)).CopyToDataTable()

Below is the output screenshot
image

Below is the workflow
Datatable_Add.zip (10.4 KB)

Hope it helps you out!

working fine…Thank you very much :slight_smile:

If you can explain linq that would be great :slightly_smiling_face:

What is D4 ?

Hey @Deepak_Chawla , let me explain u

"D" stands for “Decimal.” it specifies that the number should be formatted as a decimal number.
"4" specifies the minimum width of the string. If the numberr has fewer digits than the specified length (4 in your case), zeros are added to the prefix to make up the difference.

in ur example, when you have an integer with a value of 9, using “D4” formatting will result in “0009”, adding three leading zeros to make it a 4-character wide string.

Hope it helps you out!

Number formats Docu

Linq Learning

1 Like

Hi @Vikas_M , i have to select all columns from the same date basis on logo…how can i incorporate other columns for selection in existing query?

Seems like need to add something in updatedlogo

@Vikas_M could you please tell me ?

Hi @Vikas_M

I am getting Correct logo but i want to select other input columns by using query given by you previously…seems like need to add something in updatedlogo in existing query

(From row In dt.AsEnumerable() Let logo = row(“Logo”).ToString().Trim() Let length = logo.Length Let updatedLogo = If(Integer.TryParse(logo, 0),If(length < 4, Integer.Parse(logo).ToString(“D4”), logo),logo)Select dt.Clone.Rows.Add(updatedLogo)).CopyToDataTable()

Could you please tell me how can i get below output ?

PFB

Input Datatable sample

Logo Date_Opened CompanyName
9 06/11/2023 A
123 07/12/2023 B
22 04/16/2023 C
4567 03/10/2023 D
ABCD 06/11/2023 E
ZA90 04/21/2023 F

Output datatable should be

Logo Date_Opened CompanyName
0009 06/11/2023 A
0123 07/12/2023 B
0022 04/16/2023 C
4567 03/10/2023 D
ABCD 06/11/2023 E
ZA90 04/21/2023 F

Assign Activity
dtCleansed = dtOrig.Clone

Assign Activity
dtCleansed =

(From d in dtOrig.AsEnumerable
Let lc = d("Logo").toString.Trim.PadLeft(4,"0"c)
Let ra = d.ItemArray.Skip(1).Prepend(lc).ToArray
Select r = dtCleansed.Rows.Add(ra)).CopyToDataTable

Its about DataTable reconstruction down to ItemArray reconstruction for using LINQ within an Update Case

HI @ppr

Above query is working fine for number and character length >=4… it is adding “0” for character too if char length is <=3…i want “0” to be added only for Number…could you please tell me what needs to be updated ?

Input data
image

Output data

image

Regards,
Deepak

(From d in dtOrig.AsEnumerable
Let chk = d("Logo").toString.Trim.IsNumeric
Let lc = If(chk, d("Logo").toString.Trim.PadLeft(4,"0"c), d("Logo").toString())
Let ra = d.ItemArray.Skip(1).Prepend(lc).ToArray
Select r = dtCleansed.Rows.Add(ra)).CopyToDataTable

we hope that the topic is now finally solved.We also recommend reflagging the solution flag to the solving post. Also, we recommend sharing all details / sample data directly at the beginning. So we can faster check for the solution approaches. Thanks for support

1 Like

Thank you so much…issue is resolved

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.