Copy row to new sheet by LINQ

Help Activities
, ,
1

I want to sort column Planned Start from ascending and count character in column Create ID if found count=7 insert 0 in front of old text.

After that copy row that count character column create id = 8 to new sheet (include header)

image

Please guide me for solve it (LINQ)

Thank you.

data.xlsx (10.2 KB)

1 Like
2

Hi @fairymemay

=> Read Range Workbook
image
Output-> dt

=> Use below syntax in Assign:

filteredRows = (From row In dt.AsEnumerable()
                    Let newCreateID = If(row.Field(Of String)("Create ID").Length = 7, "0" & row.Field(Of String)("Create ID"), row.Field(Of String)("Create ID"))
                    Where newCreateID.Length = 8
                    Order By DateTime.ParseExact((row.Field(Of String)("Planned Start")),"M/d/yyyy HH:mm",System.Globalization.CultureInfo.InvariantCulture)
                    Select dt.Clone().Rows.Add(row.ItemArray)).CopyToDataTable()

filteredRows is of DataType System.Data.DataTable

=> Write Range Workbook filteredRows.
image

image
image1065×794 35 KB

Share the expected output if this is not the one.

Regards

1 Like
3

@vrdabberu sorry, I write wrong.

after add0 if count character , if create id <=6 copy to new sheet.

Sorry.

2 Likes
4

Hi @fairymemay

Give me some time. I will help you with the query.

Regards

2 Likes
5

Hi @fairymemay

=> Read Range Workbook. Please Enable Preserve Format Option.
image
Output-> dt

=> Use below syntax in Assign:

filteredRows = (From row In dt.AsEnumerable()
                    Let CreateID = If(row.Field(Of String)("Create ID").Length <= 6, "0" + row.Field(Of String)("Create ID"), row.Field(Of String)("Create ID"))
					Let newCreateID = CreateID
                    Where newCreateID.Length = 7
                    Order By DateTime.ParseExact(row.Field(Of String)("Planned Start"), "M/d/yyyy HH:mm", System.Globalization.CultureInfo.InvariantCulture)
                    Select dt.Clone().Rows.Add(row.Field(Of String)("Status"),row.Field(Of String)("Owner"), newCreateID,row.Field(Of String)("SR Opened"), row.Field(Of String)("Planned Start"), row.Field(Of String)("Summary"))).CopyToDataTable()

filteredRows is of DataType System.Data.DataTable

=> Write Range Workbook filteredRows.
image

image
image1433×796 54.8 KB

Regards

2 Likes
6

@vrdabberu thank you for support. :grinning:

1 Like
7

You’re welcome @fairymemay

Happy Automation!!

8

@vrdabberu I have question

in code as below.
If I have column 20 column , I must insert 20 column?
It can easy way for modify in future (if have more column)

                    Select dt.Clone().Rows.Add(row.Field(Of String)("Status"),row.Field(Of String)("Owner"), newCreateID,row.Field(Of String)("SR Opened"), row.Field(Of String)("Planned Start"), row.Field(Of String)("Summary"))).CopyToDataTable()
9

Hi @fairymemay

Since you want to write the NewCreateID within the same column, you need to specify the columns.

If not you can go with index numbers too. I think that would be easy to specify

filteredRows = (From row In dt.AsEnumerable()
 Let CreateID = If(row.Field(Of String)(2).Length <= 6, "0" + row.Field(Of String)(2), row.Field(Of String)(2))
 Let newCreateID = CreateID
 Where newCreateID.Length = 7
 Order By DateTime.ParseExact(row.Field(Of String)(4), "M/d/yyyy HH:mm", System.Globalization.CultureInfo.InvariantCulture)
 Select dt.Clone().Rows.Add({row.Field(Of String)(0), row.Field(Of String)(1), newCreateID, row.Field(Of String)(3), row.Field(Of String)(4), row.Field(Of String)(5)})).CopyToDataTable()

Regards

1 Like
10

@vrdabberu I have last question.

If I want to delete row that copy to new sheet.
How to write code?

11

Hi @fairymemay

Do you want to keep the other instead of the rows where CreateID is less than 6. Please specify.
Please raise an new query regarding this, I will help you.

Regards

1 Like
12

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