Powershell create table from key value pairs with key as column heading

Question

I have multiple rows of data in a powershell script that look like:

SSAS=PerformanceAnalysisService, Provider=SQLNCLI11.1;Data Source=LAP123;Integrated Security=SSPI;Initial Catalog=PerformanceDataMart:
SSAS=EnvironmentalAnalysisService, Provider=SQLNCLI11.1;Data Source=LAP123;Integrated Security=SSPI;Initial Catalog=EnvironmentalDataMart:

I'm currently using:

ConvertFrom-String -Delimiter ";" -PropertyNames Server, Provider, Source, Security, Catalog.

Which gives me:

Server                                                  Provider            Source                   Security                              
------                                                  --------            ------                   --------                              
SSAS=EnvironmentalAnalysisService, Provider=SQLNCLI11.1 Data Source=LAP123  Integrated Security=SSPI Initial Catalog=EnvironmentalDataMart:
SSAS=PerformanceAnalysisService, Provider=SQLNCLI11.1   Data Source=DEV-EDW Integrated Security=SSPI Initial Catalog=PerformanceDataMart:

I'd like to create a table, with the key as the column headings, or even to be able to strip the key out of the table data.

+------------------------------+-------------+-------------+-----------------+-----------------------+
|            SSAS              |  Provider   | Data Source |    Security     |    Catalog            |
+------------------------------+-------------+-------------+-----------------+-----------------------+
| EnvironmentalAnalysisService | SQLNCLI11.1 | LAP123      | Integrated SSPI | EnvironmentalDataMart |
| PerformanceAnalysisService   | SQLNCLI11.1 | LAP123      | Integrated SSPI | PerformanceDataMart   |
+------------------------------+-------------+-------------+-----------------+-----------------------+

But is there a way to identify the key/column names and the strip them out dynamically?

The end result will be used by SQL. So either returning the data as seperate columns, or comma seperated would be great.

Moerwald · Accepted Answer · 2019-07-08 04:41:33Z

ConverTo-Csv should be able to do that, simply pipe the output of ConvertFrom-String to it.

> $in = "SSAS=PerformanceAnalysisService, Provider=SQLNCLI11.1;Data Source=LAP123;Integrated Security=SSPI;Initial Catalog=PerformanceDataMart:","SSAS=EnvironmentalAnalysisService, Provider=SQLNCLI11.1;Data Source=LAP123;Integrated Security=SSPI;Initial Catalog=EnvironmentalDataMart:"
> $in | ConvertFrom-String -Delimiter ";" -PropertyNames Server, Provider, Source, Security, Catalog | ConvertTo-Csv -NoTypeInformation -Delimiter ';'
"Server";"Provider";"Source";"Security"
"SSAS=PerformanceAnalysisService, Provider=SQLNCLI11.1";"Data Source=LAP123";"Integrated Security=SSPI";"Initial Catalog=PerformanceDataMart:"
"SSAS=EnvironmentalAnalysisService, Provider=SQLNCLI11.1";"Data Source=LAP123";"Integrated Security=SSPI";"Initial Catalog=EnvironmentalDataMart:"

You can also replace ConverTo-Csv with Export-Csv if you want to store the content to a CSV file, or you can pipe the output of ConvertTo-Csv to Out-File

Hope that helps

Collectives™ on Stack Overflow

Powershell create table from key value pairs with key as column heading

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related