Script to modify data in .csv file



  • Hi,

    I have only experience in programming VBA in Excel so i’m totally new to this.
    I wanna write a script in any language available in Notepad++ that modifies the data in a comma separated .csv file:

    The script should loop through each row in the file and do the following:

    For each row in file
    If type = “Mobil” and Left(phonenumber, 3) <> “467” Then type = “Fastmobil”
    next row

    File before script:
    orgnr,phonenummer,type
    5566848271,4670224311,Mobil
    5566848271,4670661963,Mobil
    5566848271,463041874,Mobil
    5566848271,4630337231,Mobil
    5566848271,4685602815,Fast

    File after script:
    orgnr,phonenummer,type
    5566848271,46702249311,Fastmobil
    5566848271,46706612963,Fastmobil
    5566848271,4630410874,Mobil
    5566848271,46303377231,Mobil
    5566848271,4685602815,Fast

    So, how do I write the script?
    And how do I run it?
    I suppose if I write in PHP I can run it from cmd?

    Regards/
    Christian



  • You could do it with a script… but that one can be done with a regular expression in the Search and Replace dialog…

    • Find What: ^(\d+),((?!467)\d+),Mobil$
    • Replace With: $1,$2,Fastmobil
    • Regular Expression

    Where

    • ^(\d+) will match all the digits from the start of line to before the first comma, and store them in $1
    • (?!467) makes sure it does not match 467 at the beginning of the phonenumber field
    • ((?!467)\d+) stores the phone number (not starting with 467) in $2
    • ,Mobil$ makes sure it ends with “,Mobil”

    If it does not match those requirements, no change is made. If it does match, then it uses the number stored in $1, then a comma, then the number stored in $2, then a comma, then “Fastmobil”.

    Oh, wait, sorry. Your VBA psuedocode said to change it if phone number does not start with 467, but your example output changed if it did start with 467. In that case,

    • Find What: ^(\d+),(467\d+),Mobil$

    Please select whichever you need.

    See the FAQ for more about Regular Expressions



  • Thank you peter,

    That was a great solution! The output example was incorrect, sorry for that…


Log in to reply