How to sort the values in specific column in excelsheet


#1

I want to sort the values in specific column based on the values in ascending order in an excel sheet.
As sort table can only used for tables in excel sheet.
Thanks
anantha


#2

Hi,
Did this help



#3

I tried this solutions i am getting some error.are there any alternative solutions


#4

What are the errors you are getting?

This is what I got that worked:
dt.DefaultView.Sort = ("[columnname] ASC")
dt = dt.DefaultView.ToTable


#5

yaa its worked thank you @ClaytonM


#6

Hey @ClaytonM
I have to read the range in the middle of the excel,in that range i have to sort the data using specific column,i tried this solution,as i m not reading 1st row it is not able to match the column name.
I tried different solutions.
can you suggest me one solution.
Thanks
anantha


#7

Hello @Ananthaeswar2
Can you set the range to include the 1st row. You can, I think, use a comma and include multiple ranges.

For example,
“1:1,10:100”

That might solve your problem of not having headers.

Thanks.


#8

hii @ClaytonM
Thanks for the solution, I tried with that, but i am getting some null reference exception.
i tried to solve the error,but i didnt find anything.
Can you please help me with that.
Thanks


#9

Hi,
Make sure your Excel Package is up to date. Also, can you post example workflow or screenshot your activity you are using with the properties shown? That way we can confirm you have it set up correctly. It may also get that error if the first row is blank.

Thanks.


#10

Hi @ClaytonM
I am attaching one sample example,In that sheet i want sort 3rd column with the range 4th row to 9th row.sample.xlsx (8.0 KB)

Thanks


#11

Well I’m not sure why you can’t specify multiple ranges in Read Range with Headers. However, I was able to get your specified range into a DataTable by using an Assign with the below .Net functions, and I’m no expert on this.

dt2 = dt.AsEnumerable().Skip(3).Take(6).CopyToDataTable()
It skips the first 3 rows, and includes 6 rows, so 4-9

Then you can Sort that with:
dt2.DefaultView.Sort = ("[Id] ASC")
dt2 = dt2.DefaultView.ToTable

Then Write Range dt2 will put that sorted datatable into the file.

If you are looking to keep the rest of the original datatable with the sorted range then you’d probably also need to merge them.


#12

i have one excel sheet which has data from the middle of the sheet and doesn’t have any headers.
and i have to sort the excel sheet using one column .i tried like this: i read range from A30:AI100 and
dt=dt.select("",",[column H] ASC").CopyToDataTable
but is showing index out of range exception.
Can anyone help me with this.

Thanks
Ananthaeswar


#13

Hi ClaytonM

Tried doing this but i am getting below error.

" Assign : Exception has been thrown by the target of an invocation."

Can you please help


#14

@Savartak, You can also order by values in specific column as follows,

In assign,

Datatable dtFinal = (From row in YourDatatable. Select Order by row(“ColumnName”) select row).CopyToDatatable()

Regards,
Dominic :slight_smile:


#15

@Dominic ,

hi bro… I saw this suggestion that you gave to sort the columns in an excel .

you have made it so simple.

Can you please suggest a .net site I can go through to get a better understanding of the code


#16

you can write like Dt.select(columname(“value”) ASC))