Remove special characters and add data in that column

Welcome Forums General PowerShell Q&A Remove special characters and add data in that column

Viewing 4 reply threads
  • Author
    Posts
    • #269906
      Participant
      Topics: 1
      Replies: 2
      Points: 21
      Rank: Member

      I am very new to Powershell scripting. I have only used it for pulling data from sql and creating csv files with the data. I am finding issues with some data entered in the database and need to clean it up and modify it a little before creating the final csv.  Any help would be greatly appreciated.

      First issue I have is spaces leading and trailing some of the data in columns. I have resolved this using trim cleaning every column. The next thing I need to do is select a column containing phone numbers (could be multiple columns) and remove all the extra characters ()-. etc. Then I need to add to the beginning and end of the phone number which could include # ,*  or additional numbers. (1234567890 to 34512345678905*** as example)  This is what I have so far the Where-Object does not currently work but left it in as example.

      • This topic was modified 3 weeks, 1 day ago by grokkit. Reason: moved to correct forum section
    • #269945
      Senior Moderator
      Topics: 9
      Replies: 1420
      Points: 5,280
      Helping Hand
      Rank: Community MVP

      Where-Object is used only to filter data based on conditions, we cannot change data. You can use Where-Object to filter then pipe to ForEach-Object to do replace action for each object

    • #270226
      Participant
      Topics: 1
      Replies: 2
      Points: 21
      Rank: Member

      Thanks for your reply. Yes that is what I gathered. Once I get the filter I dont know how to add the replace function. I imagine it would go something like this but I dont know the correct syntax to get the replace to work.

      • This reply was modified 3 weeks ago by kvprasoon. Reason: Code formatting
    • #270244
      Participant
      Topics: 16
      Replies: 1855
      Points: 3,568
      Helping Hand
      Rank: Community Hero

      It’s much easier to assist if you post mock data. One of the typical approaches that can be used is a calculated expression. This lets you manipulate column data, rename columns, etc.:

      Here is the basic output:

    • #272044
      Participant
      Topics: 1
      Replies: 2
      Points: 21
      Rank: Member

      Thank you Rob that got me in the direction I needed to complete my task. This is a piece of what I used that is directly in line with my original question. This cleared all extra spaces in all columns and where there was a phone number such as (123)456-7890. It removed all special characters and then added a 1 in front and pound at the end to look like this 11234567890#. It then checks for not empty or incomplete rows and exports only complete ones into the new csv file.

       

Viewing 4 reply threads
  • You must be logged in to reply to this topic.