Hi,
I have an excel sheet from which I need to clear column .
It has column which contains space with the column names
Columns to be cleared are - “DEV”,“SSA”,"acf ","aaq "
Here I have attached the test file for the same
I am using invoke code workflow and the code I have used is
Datatable.AsEnumerable().ToList().ForEach(Sub(row)row(in_Column_Name.ToString) = “”)
Thanks in advance!!
TEst.xlsx (8.7 KB)
Hi @Kunal_Jain
please try with below code.
DataTable.AsEnumerable().ToList().ForEach(Sub(row)
If row.Table.Columns.Contains(in_Column_Name.ToString()) Then
row(in_Column_Name.ToString()) = “”
End If
End Sub)
hope it helps!!!
we dont see spaces in DEV, SSA. So we assume that you are talking about a predefined Column Set.
However for clearing we do have multiple options
Excel - selective overwriting
- prepare a 1 column Datatable with the same row count as data is present and and each value within the datatable column is - nothinh
- use the prepared datatable and write it to Excel by specifiying the needed Range and offset
DataTable Reconstruction
Assign Activity
dtCleansed = dtData.Clone
Assign Activity:
dtCleansed =
(From d in dtData.AsEnumerable
Let ia = d.ItemArray
Let cds = new String(){"DEV","SSA","ACF","AAQ"}
Let ra = ia.Select(Function (x,i) If(cds.Contains(d.Table.Columns(i).ColumnName.Trim.ToUpper), nothing, x)).toArray
Select r = dtCleansed.Rows.Add(ra)).CopyToDataTable
we can also externalize cds to a variable
Hi @Baskar_Gurumoorthy
We don’t need the column name also in the output.
The Output will be like the below mentioned result sheet.
Result.xlsx (8.4 KB)
Thanks!!
Hi @Kunal_Jain
better you can use filter data table activity in that configure filter output you can mention your need columns.
if you choose remove you need to mention the column names DEV,SSA,acf,aaq
if you choose keep you need to mention the column names ABC,FFA
in that case we recommend, a selective 1 column Datatable writing to Excel, due we cannot have Datatables with empty columnnames
Hi @Kunal_Jain
Invoke Code:
dtData.AsEnumerable().ToList().ForEach(Sub(row) row(currentText) = DBNull.Value)
Invoked Arguments:
Sequence28.xaml (9.3 KB)
Output:
Hope it helps!!
Hey @Kunal_Jain
Here is the Solution!
Without Invoke Code:
Below mentioned LinQ is for getting the column name that have input values:
arr_ColumnName = (
From columnn In dt_Input.Columns.Cast(Of DataColumn).Select(Function(y) y.ToString)
Where dt_Input.AsEnumerable.Any(Function(x) {"dev","ssa","acf","aaq"}.Contains(x(columnn.ToString).ToString.ToLower.Trim))
Select columnn
).ToArray
After that a loop in which one more expression:
dt_Input.Columns(currentText).Expression = ""
Output without header:
Workflow Screenshot for your reference:
Attaching .xaml for your reference:
BlankColumn_Sequence.xaml (9.8 KB)
Input - Output:
TEst.xlsx (9.0 KB)
Regards,
Ajay Mishra
Hi @Ajay_Mishra
Can you share the Zip file of the code
It is showing the document is invalid.
Thanks!!
@Kunal_Jain
Zip File:
BlankColumn.zip (10.1 KB)
Regards,
Ajay Mishra
Hi @ppr
Can you share the working code in a zip file
As I have checked the given code but it is not working for me
Cheers!!
what was done in detail and what is failing (error)?
Hi @ppr
I have copied your code and have done what you have suggested
Here is the screenshot of the same.
I am not getting the desired output
Kindly note: Code was given before we got the information:
where we also reacted with:
However as there are many options to achive it (when the column headers are to surpress within the output)and was also mentioned here within the thread, you could give a signal of preference for further assistance
Hi @ppr
I have tried using the above code by @Ajay_Mishra but it not giving us the required output for other sheet which I cannot mention here.
It has around 70 columns and is an xlsb file.
@Kunal_Jain If you want to use my code in other sheet for multiple columns then just put all column names in that array in lower case.
This will resolve your issue.
Regards,
Ajay Mishra