Parse Config File Values into an Array

I have been tasked with removing as much hard-coded data as I can from a script and put it into a configuration file to be parsed by the script. I've got two challenges, both very similar but with differences.

My configuration file will have a key/value pair such as:

I've used this method quite a bit in the past, but now, some of the values need to be parsed into an array to meet the requirements of the cmdlet (e.g. Send-MailMessage-multiple addresses has to be an array of string). So the challenge is to have a parameter with the name emailTo go into a variable in PoSh named $emailTo with the value of @("","","")

What I am doing now, and would like to try to keep the same 'format' is I read the file in with: $CSV = import-csv .\my.prop, then use the following function call:
$var = GetParameterValue $CSV "VAR"

where GetParameterValue is coded thusly:
function GetParameterValue($ParmFile, $ParmName)
	$tmp = $ParmFile | where { $_.Key -eq $ParmName } | Select Value
	[string]$result = $tmp.value
	return $result.trim()

Open in new window

To simplify things, I would like to work with the string, and make the determination in code if it can be left as a string, or parsed into an array based on the variable name.

I would prefer NOT to have to put the quotes around each address, but will if it will help.

The second issue is more challenging, I need to pass a parameter to SQLCMD along with the query script, with has a SQLCMD variable in it. The variable basically defines a list of database names to EXCLUDE when querying master.sys.sysdatabases. The code is:

After playing with it for 1/2 a day, I finally learned that I need to pass parameters as an array in name=value format. Thus, instead of:

$exclusions = "'DB1', 'DB2','DB3'"
SQLCMD.EXE -E -S $server -y 2048 -h -1 -d MainDB -i $sqlScript -o $serverOutfile -s "|" -v DB_EXCLUSION_LIST = $exclusions

it turned out to be

$dbExclusionList = @("DB_EXCLUSION_LIST=`"'DB1','DB2','DB3'`"")
SQLCMD.EXE -E -S $server -y 2048 -h -1 -d MainDB -i $sqlScript -o $serverOutfile -s "|" -v $dbExclusionList

Not sure how I am going to do that in a config file. Ideally, again, I'd have DB1,DB2,DB3 and any formatting would be done in my script, but then again I never have gotten everything I wanted. If someone can figure out a clean way to save the value in the config file and parse it into an array, I would sure appreciate it.
LVL 15
Doug BishopDatabase DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

When reading from a file, everything is strings.  So if you need to get an array out of that, your best bet is probably to split a string using some delimiter that you've included.  So if you have a line like

Open in new window

Your function could be
function GetParameterValue($ParmFile, $ParmName)
	$tmp = $ParmFile | where { $_.Key -eq $ParmName } | ForEach { $_.Value -split "\|" } | ForEach { $_.trim() }
	return $result

Open in new window

I'm wondering if the array notation, @(), is really needed in
$dbExclusionList = @("DB_EXCLUSION_LIST=`"'DB1','DB2','DB3'`"")
since it's just a string.
Not sure if I can help with SQLCMD issue.
Doug BishopDatabase DeveloperAuthor Commented:
It IS required. My first thought was to code it as a string and it did not work. Research indicates it has to be an array with the each element being a variable=value pair.
Jeremy WeisingerSenior Network Consultant / EngineerCommented:
an array with the each element being a variable=value pair
Like a hash table?

Instead of importing your file as a CSV, why not parse it and put it in a hash table?
Assume it is comma separated, something like this:
$ht1 = @{}
Get-Content .\my.prop | % { 
    $len = ($_.Split(',')[0]).length

Open in new window

And as far as the SQLCMD, I think footech was saying that if this is just an array with one entry:
$dbExclusionList = @("DB_EXCLUSION_LIST=`"'DB1','DB2','DB3'`"")

Open in new window

So this should work too:
$dbExclusionList = "DB_EXCLUSION_LIST=`"'DB1','DB2','DB3'`""

Open in new window

But to construct it from your key file, if the key for you DBs is "DBexclusions", you could do something like this:
$dbExclusionList = "DB_EXCLUSION_LIST="""+$ht1.DBexclusions

Open in new window

SD-WAN: Making It Work for You

As bandwidth requirements and Internet costs grow, businesses naturally want to manage budgets by reducing reliance on their most expensive connection types. Learn more about how to make SD-WAN work for your business in our on-demand webinar!

One error in my code, line 4 should be
	return $tmp

Open in new window

Doug BishopDatabase DeveloperAuthor Commented:
Been doing some research (didn't have a lot of time for that when I posted this) and have come up with the following. The MAIN concern was parsing and building a value that would work with passing parameters to SQLCMD. Without changing the base code that parses the config file, this accomplishes what I need:
Function GetParameterValue($ParmFile, $ParmName)
	$tmp = $ParmFile | Where { $_.Key -eq $ParmName } | Select Value
	[string]$result = $tmp.value
	Return $result.trim()

$PARMS = import-csv "c:\temp\"

$DB_EXCLUSION_LIST = "`"'" + (GetParameterValue $PARMS "DB_EXCLUSION_LIST").Replace(' ', '').Replace(';', ',').Replace(',', "','") + "'`""
[string]$dbExclusionList = "DB_EXCLUSION_LIST=" + $DB_EXCLUSION_LIST

Open in new window

With the above code, the parameter file can accept either a quoted comma delimited string of values, or a quoted or unquoted semicolon-delimited string of values (if it is comma-delimited, the whole string must be quoted in the config file (notice I am typing $dbExclusionList as a string array).

For other strings, where all I need to do is parse the string into an array (email addresses), they can be delimited with a semicolon and I can use the syntax
$emailTo = $SendToAddresses.replace(' ', '').split(';')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Doug BishopDatabase DeveloperAuthor Commented:
None of the answers provided a solution. I had time to do some research online and was able to find workable solutions to my problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.