How to Remove empty columns in excel using uipath

[ranaprathap928 (Rana reddy)]

Hello everyone , I’m working on a excel sheet I have some data in that excel . In that data some columns are empty. I want to remove those empty columns completely and I want to re write the data in same excel sheet can anyone help me how to solve this.

Input data

I need output should be like this

[prasath17 (Guru)]

@ranaprathap928 - Please check this post…i have provided to the similar problem…

How to delete rows in the same sheet (Excel)?

@Tana20 - Here you go… [image] LINQ Query in the Assign activity dtInput.AsEnumerable().Where(Function(row) Not(row(“Z”).ToString.Trim.Contains(“0”) or row(“Z”) Is DBNull.Value Or row(“Z”).Equals(“”) )).CopyToDataTable Input [image] Output [image]

Input

Query
dtInput.AsEnumerable().Where(Function(row) Not(row(“Retry”) Is DBNull.Value Or row(“Retry”).Equals(“”) )).CopyToDataTable

Output

If this helps, please mark my post as solution.

[ranaprathap928 (Rana reddy)]

Thanks @prasath17 let me try this

[ranaprathap928 (Rana reddy)]

@prasath17 I’m not getting “AsEnumerable” for that which package I need to install

[prasath17 (Guru)]

Can you please share your query. Dtinput comes from your Read Range…

Please check what is your readrange output variable…

Also check this post on how to fix it(if the error persist).

Compile Error: "AsEnumerable" is not a member of "System.Data.DataTable"

@VegitlX_HuNteR Follow these Steps and Check : Open your Main.xaml File in Notepad or Notepad++ or any other Editing Tool. Then Add the Highlighted Reference as an Assembly Reference in the Assembly Reference Section as Shown in the Below Image. [Capture] Make sure to Close your xaml and then after the Edit open it again.

[Akshay07 (Akshay Suryawanshi)]

Hello @ranaprathap928,

you can also try filter datatable activity as shown n below image.

[ranaprathap928 (Rana reddy)]

@Akshay07 I tried filter data table but it’s not working properly I will check once again

[prasath17 (Guru)]

@ranaprathap928 …Please find the starter help here…

DeleteEmptyRows.zip (47.1 KB)

[ranaprathap928 (Rana reddy)]

@prasath17 after adding that into uipath xaml also I’m not getting “AsEnumerable”. My read range output I’m using in that code

[prasath17 (Guru)]

@ranaprathap928 - Please share your query…and the error…

[ranaprathap928 (Rana reddy)]

@Akshay07 while I’m filtering data I’m able to write that data in another excel sheet but I want write the updated data in same excel sheet by removing that empty columns

[ranaprathap928 (Rana reddy)]

@prasath17 I’m not getting AsEnumerable

I added data extension in main Xaml also

[Akshay07 (Akshay Suryawanshi)]

Hello @ranaprathap928,

Thats simple !!!
In output Datatable use the same datatable variable as Input Datatable and use write range activity to write the excel with the same file name.

eg. Input Datatable = InDt and Output DataTable = InDt.

Mark this as solution if it solved you problem.

[ranaprathap928 (Rana reddy)]

Sure @Akshay07 I will check this

[prasath17 (Guru)]

Thanks for adding the missing item…Have you closed and reopened it again…Just making sure…(as shown in this video)

How to fix 'AsEnumerable' is not a member of 'System.Data.DataTable' in UiPath

[ranaprathap928 (Rana reddy)]

@prasath17 in your Xaml it’s working fine when I copy that code to my Xaml I’m getting error

I changed input variables also like in your xaml

[ranaprathap928 (Rana reddy)]

Thanks @prasath17 it’s working

[ranaprathap928 (Rana reddy)]

Thanks @Akshay07 even filter data table which you shared that also working , I can’t make both solutions I guess. Thanks for your help guys.

[prasath17 (Guru)]

@ranaprathap928…glad you were able to solved…

Yes @Akshay07 solution is also works…only thing with the LINQ is , Faster…you have more # of rows in ur excel then this guy does the job much faster.

[ranaprathap928 (Rana reddy)]

Yes @prasath17 @Akshay07 thanks for your help.