Windows tip: how to efficiently filter and save data records with a script

An acquaintance of mine manages the membership database of a large gymnastics and shooting club in his free time. Since the introduction of the revised Data Protection Act, he’s no longer allowed to circulate the entire Excel list, but only individual, partially anonymised data records.

To create lists for car pools, for example, he has to filter in Excel and laboriously copy and paste data records into other files. Quite a bit of work.

Automate queries in PowerShell

There’s an easier way – a PowerShell script. It’ll search for the corresponding parameters in a database, read the respective data records and copy them into a new, separate text file. This is only possible if the database is in .txt, .ini or .csv format. You can save files in .xlsx to .csv as a copy.

Here’s how to build your script:

1. Open the folder where you saved the CSV file (or TXT/ini file).

1. Open PowerShell by clicking on a free space in the folder and selecting «Open in Terminal».

3. Type the following parameters into the text field and press Enter: get-Content '.\contacts.csv' | Select-String -pattern "Winterthur" | Out-File resultat.txt. The first part of the command selects the text file in question. The second selects the data records that fulfil your conditions. Number three then saves the search results in a text file. In my file, all data records containing the parameter Winterthur are now selected. You can replace the search term with any of your choosing.

4. The file will now be created in the same directory as your database.

You now have a text file containing the corresponding data records – make sure to change the file name, in my case «contacts.txt» and the pattern, in my case «Winterthur».

Note: This tip and command refer to a single use case. Do you have any similar problems that I could help you with? Drop them in the comments! I’ll try to answer them in other Windows tips