How to handle with spaces in CSV file

I have this csv file

I’m using read csv file to save it in a datatable variable and use the fields “Quantidade” and “Valor_total” as a filter to return “Item” value. but when this csv file is generated by my company’s system the values come with spaces, so if i want to use the values to filter i’ll have to put the spaces, like the example below:

Is there a way to handle with this? I know that if i open this csv in excel and save, this spaces desapear, but i have to do this manually. Is there an activity to make it?

we do have differnet options:

  • cleansing the CSV within a correction flow
  • using trim() for evaluations
  • Custom filterings with LINQ

I’ve made a quicky search about these 3 topics and looks like trim() and LINQ are the best options, but i don’t know how to apply it in my case, can you help me?

Hello ,

What you can do is:

  1. Read the csv and get the data to a Datatable( space will be there)
    2)(From r In dtData.AsEnumerable
    let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.Replace(" ",“NA”)).toArray()
    Select dtCorrected.Rows.Add(ra)).CopyToDataTable()

you can use below post also

Looks like it is working!

Can you explain to me how this code works?


The datatable with spaces is dtEnt_Garan
And i’ve created the table dtCorrect

I’ve changed the replace too, because i just want to remove the spaces, not to replace for NA like in the other post


From the datatable it will fetch each row as an array of data and we are using the Trim and replace to remove the anonymous spaces. After that, it is getting copied to a new Datatab;le.

1 Like

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