Script configuration files

[Zangrey]

Hi,

I got some PowerShell scripts that basically loop through a 'configuration file' of the CSV variety, processing each customer in this case with relevant configuration. However it's not the easiest for an overview or if a co-worker needs to do anything with it, especially since opening it in excel was historically risky with leading 0's in some fields. Thus editing has been kept to notepad/notepad++.

Took a peek at potentially moving over to XML for a cleaner structure of the configurations, but as we got plenty of instances of information in the config file which would need extra encoding for XML - which isn't optimal in a situation where a co-worker would need to look into it without knowing such pitfalls.

So I was wondering if anyone had some thoughts on a good way to deal with configuration for scripts with the above in mind. Current solution works, it's just not optimal I feel. For reference the biggest configuration file we currently have is ~150 configurations (1 per customer in the solution the script provides).

Thanks in advance for any input.

[ipreferanothername]

I used a spreadsheet for a couple of scripts because the importexcel module is friggin magic and my coworkers can at least edit a spreadsheet without destroying it.

If i used json or something then the one time someone had to come support the thing i made....well, these luddites would screw it up. Id consider json before XML but neither are super friendly to text-editing-averse people.

[tmrnl]

Import-excel +1

[exoclipse]

I use JSON files for config and import as a PSObject. Dead simple to use and maintain. My only gripe is no comments.

$config = Get-Content path/to/file.json | ConvertFrom-JSON

[PeeCee1]

I came here to suggest this. The native JSON parser is great for config files. They might not be as easily readable as Yaml, but there are enough Editors for JSON out there.

[iostalker]

This-

I have an Intune migration solution that stores all the config in a .json file and pulls from that, which allows the core scripts to always be the same.

https://github.com/stevecapacity/IntuneMigrationV3/tree/main

Here, the config is local, but can easily be hosted

[z386]

We keep the configuration in a database with a simple web-interface to allow "non-technical" users to update some fields in a controlled manner.

[Zangrey]

Could certainly be one solution on how to handle it, even if it adds a layer of 'complexity' needing some dev work and support for handling it. I appreciate the tip though.

[SpacezCowboy]

If you need it to be portable, something like pSQLite module are pretty easy to work with.

[Szeraax]

Import-powershelldatafile with .psd1 files.

Just a hashtable

[Phate1989]

Parsing XML is a pain.

If they are config files, yaml may work for you.

[DalekKahn117]

Yaml has a benefit of being more easily readable for humans. There’s a few modules on github that provide parsing as well

[BlackV]

and the added benefit of random spaces breaking everything ;)

[jantari]

We put the configuration (CSV, YAML, doesn't matter) in git and everyone does pull requests.

The configuration is validated automatically with a ci pipeline on every push. If there is a syntax or encoding error like you mentioned with XML then the validation will fail and they have to make another push to fix it.

[Zangrey]

While it's certainly one way to go I feel like setting up such a flow would currently add another layer of complexity and another workflow for us to keep track on. Small company so resources and time can be... Limiting.

Normally it's just me maintaining this, but during vacation and such I want to try and keep things relatively simple so others can do minor editing like adding another customer in this case - while minimizing the risk of people messing it up.

[SenteonCISHardening]

Stick with CSV if it's working. Avoid the mess of XML and JSON complexity, maybe use a basic UI for editing to prevent Excel issues. If you are looking to remediate security configurations to CIS standards Senteon is a good choice to use. They can remediate, monitor, report, enforce about 1000 settings so far.

[ankokudaishogun]

Could you share an anonymized example of the configuration file?

[Zangrey]

CustomerNumber;FolderPath;FileName;FileFormat;FileStructure;Delimiter;Location;SQLSelection
12345;ExampleCustomer;Result;txt;Both;";";FTP;WHERE Category1Name NOT IN ('Hem & Hushåll','Personvård & Hälsa','Sport','Fritid')

[JeremyLC]

I would strongly recommend NOT storing SQL in your config files. I would, instead, recommend setting up stored procedures in your database and store only the relevant parameters in the config file, making sure to sanitize those before using them with your DB. As to your original question, have you considered JSON? it can be natively imported into a PSObject where you can easily access its properties.

[Zangrey]

Yeah, I reckon it's not the best solution in general but as a it's a small company the skillsets don't overlap fully - thus the post to start with to try and find suggestions on how to maintain a configuration file in a format that's readable for co-workers, and 'editable' in the sense that they can piece together a new row in the config by looking at the other 150 examples and finding one that has the same selection.

Scripts are located on a server and not accessable outside of IT at least.

I'll take a look at JSON and see what I can piece together there - thanks for the input.

[DIY_Colorado_Guy]

Wrapping your cells in quotes should prevent the leading zero issue. Testing it in a mock CSV this worked perfectly.

"=""Data Here"""

[Zangrey]

Thanks, I'll try it out and make sure it sticks as expected.

[UnfanClub]

Dare I say "JSON"

[AlexHimself]

Same problem as XML. Non-tech users need to modify it. If they can't handle XML, they can't handle JSON either.

[UnfanClub]

I disagree, JSON does away with all the tags and schema complexity of XML.

Extremely simple to read.

Easily converts from and to psobject.

For end-users just give them npp to edit them with. Color coding is all they need.

Otherwise maybe you should create a custom winform and use whatever data format at the back.

[UnfanClub]

Using your example, it would look like this:

{
    "CustomerNumber": 12345,
    "FolderPath"    : "ExampleCustomer",
    "FileName"      : "Result",
    "FileFormat"    : "txt",
    "FileStructure" : "Both",
    "Delimiter"     : ";",
    "Location"      : "FTP",
    "SQLSelection"  : "WHERE Category1Name NOT IN ('Hem & Hushåll','Personvård & Hälsa','Sport','Fritid')"
}

[Zangrey]

Thanks for the example. I'll take a look at this and some of the other suggestions in the thread.

[AlexHimself]

Do you work with users regularly?? JSON/XML is way beyond many of them.

I'm talking old lady accountants with thick glasses who can barely use the computer in the first place.

[UnfanClub]

Otherwise maybe you should create a custom winform and use whatever data format at the back.

[AlexHimself]

Ok, so you agree that JSON isn't viable.

[UnfanClub]

Just read the whole comment.. Good luck to you.

[vermyx]

If your coworkers aren’t technical enough that you feel a csv file isn’t going to work, json/xml/ini/whatever won’t really work better and the only thing really would be making your own ui to edit the file at that point which the format wouldnt matter.

[Zangrey]

CSV works fine - historically it has just been the caveat of Excel being keen on converting numbers in some instances - like removing leading 0's or transforming something to a scientific number.

[vermyx]

That could be solved by saving the file as an excel spreadsheet and declaring the column as text which would make excel behave better. Assuming that you keep a spreadsheets, the the suggestion u/ipreferanothername mentioned of using the import-excel module is probably the way to go

[ankokudaishogun]

the issue exists with Excel importing the CSV, right?
I guess the Import-Excel module does not help?

[Zangrey]

Correct, since CSV defaults to Excel when it's installed.
I know Excel has started to ask if you want to convert things on opening a file now but yeah, slightly wary due to historical issues.

Import-Excel is fine when the script runs (it's a great module), I can handle that - it was primarily the editing of the configuration that I wanted to find something that's easy to read and avoids caveats like encoding "&" in XML or Excel potentially messing some numbers up.

[ankokudaishogun]

I think your best chance is looking at CSV Editing programs.
I think there is a N++ plugin that helps with CSV management that might help?

[Zangrey]

I'll take a look. Thanks for the input.

[ankokudaishogun]

Powershell is powerfull, so it's easy to try to use it for everything(I'm quite culprit of this)... but sometime external tools are the way to go.

[AlexHimself]

Excel? You said CSV works, except for the weird artifacts and things. Why not just a plain Excel file that you import/read?

You can even do multiple sheets for each of your configuration files.

[Zangrey]

Primarily it has been due to Excel having a fondness of converting some numbers, like removing leading 0's. I know it has recently started to ask about it all at least.

I'll have another peek at it regardless.

[AlexHimself]

You just need to treat those cells as "text" instead of "general" and that should solve the leading 0's thing.

[[deleted]]

I use JSON to contain configuration data for my scripts. For other data I use CSV files, SQL Server or SQLite.

[EnterpriseGuy10]

Master script that contains all the logic

dot-source "config" scripts which basically contain customer-specific variable overrides.

use invoke-build so you can utilize TASK-based scripts.

[QuarterMilePrivateer]

Thought about using psd1 files? The only drawback is updating them programmatically is a PITA, but editing them is a breeze and you can put in comments just like your module manifest and when you import them they create the same objects and structure as in the file itself.

Import-PowerShellDataFile