Forum Discussion

swright's avatar
swright
New Contributor III
5 years ago

How to search and replace in a csv file

I’m trying to design a pipeline that changes gets a zip file via sftp that contains a single csv file, then do a search and replace in the csv (in this case it is replacing all the values in a single column with another value), then put the csv back in the zip file.

I’m not sure how to best do this. I can get the csv unzipped using a ZipFile Read snap. Then I use a csv parser snap, but now I need to do a search and replace for the value. How should I do this?

Basically, I need to know how to search and replace in a csv file. In this case I need to search and replace in a column but I also need to know how to do a general search and replace.

Thanks,
Scott

4 Replies

  • del's avatar
    del
    Contributor III
    5 years ago

    Check out the String functions replace and replaceAll

    • swright's avatar
      swright
      New Contributor III
      5 years ago

      Thanks Del, but I’m looking for something that will find and replace something in the entire file. If I can break the file into strings, do a replace, and then put the csv back together then I guess that could work but I’m not sure how to address all the rows and fields that I need in the csv. Maybe I’m misunderstanding your answer…

      Regards,

      Scott

      • alchemiz's avatar
        alchemiz
        Contributor III
        5 years ago

        Hi Scott,

        Good day, what I usually do is to use the replace function with regex args… for example I want to replace a group of characters within the boundaries of c-f and length is greater than 3 with <<SUCCESS>>

        in the mapper where you can access the whole content do the a string manipulation Base64.decode(Base64.encode($['content'])).replace(/([c-f]){3,}/gi,'<<SUCCESS>>')

  • skatpally's avatar
    skatpally
    Former Employee
    5 years ago

    Try the below Expression.

    $.mapValues((value, key) => value.toString().contains(‘img’) ? value.replaceAll(‘img’,‘’) : value)

    Hope this helps.