To Remove unwanted character in xlsx

Help Studio
, , , , , ,
1

Hello team we have this file which we get after converting csv to xlsx

Have attached for reference

Here in this how to we remove =“” present in cells

Example if data present in below format
=“App”

To be converted to

App

Or

=“”

It should be converted to

Blank value

Thanks in advance
test_file.xlsx (8.1 KB)

2

Are you using “Read CSV” and then using Write Range Activity?

Try to set “IgnoreQuotes” in Read CSV activity to True:

image

3

Read the csv with read csv file and get the output as datatable as dt
Once after reading use a INVOKE CODE activity where pass dt as input argument
And inside the activity use this expression

dt.AsEnumerable().ToList().ForEach(Sub(row)
    For Each cell In row.ItemArray
        Dim value As String = cell.ToString()
        If value.StartsWith("=""") AndAlso value.EndsWith("""") Then
            row.SetField(Array.IndexOf(row.ItemArray, cell), value.Trim(""""c))
        End If
    Next
End Sub)

Cheers @NISHITHA

4

Hi @NISHITHA ,

In the process of converting from CSV to Excel, do you have datatable as the intermittent value, if so we could maybe try converting using the Linq Expression mentioned in the post below :

For your case :

dtCorrected = (From r In dtData.AsEnumerable
let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.Replace("""","").Replace("=","")).toArray()
Select dtCorrected.Rows.Add(ra)).CopyToDataTable()

Another Option would be to use Modern Excel Replace Activity which will effect the Excel sheet directly :
image
image

5

Hey @Palaniyappan

When we try the invoke code approach then it changes the format as below

="Case

And Other cells where blank value was present as =“”

Is getting changed to =

Example
the final output actually for cells containing this character format
=“Case”
=“Amt”

output should be

Case
Amt

And if Blank was present in this format =“”

It should actually get changed to Blank Cell Format

Thanks @Palaniyappan

6

Hello @supermanPunch

Actually tried this approach as well but did not seem to work

Do let me know if any other ideas will surely try

Thanks @supermanPunch

7

Hey @supermanPunch

Its working now with Replace

Thanks @supermanPunch

1 Like
8

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