Indexing Data from Text file


I’ve got a problem, I’m reading out 3 tables from a SQL Server Database and sending them to a simple text file. I need a way to read in the text file and have structured data so that I can “make it so”.

Three tables are:
Device to Group Mappings

Each Device has a device ID, and each group has a group ID, the Device to group mapping does exactly that. There is no way to export these discovery/inventory ranges, they have to be recreated from scratch. When talking about this at scale manually recreating a 1000 of these isn’t going to happen(not by me)

I’ve tried numerous things and I’d like to stick to powershell on this one. I’m sure pandas could make quick work of this however I want something that can be used everywhere <3 powershell.
$device = Import-csv -delimiter “:” -path …
$device = get-content …
I can reference $device[n] and get the line however this is not good because I don’t need the line as the index. I need these to be more PowerShell like.

I'd like to be able to do things like
This would allow me to correlate the three tables in such a way that I can use the built in features of the product to script back the discovery/inventory ranges recreating all my groups nicely

Example of device ID1 from text file created. If there is a way to read in the text file and have each device be its own powershell object where you can reference each object by index say device ID or Address the rest should be easy.
Id : 1
Address :
AddressType : 1
SNMPGetCommunity : public
SNMPSetCommunity :
CIMUserName :
CIMPassword :
IPMIUserName :
IPMIPassword :
IPMIRetry : 2
IPMITimeout : 5
EnabledState : 1
SNMPRetry : 2
SNMPTimeout : 4
SubnetType :
SubnetMask :
Protocols : 64
DeviceTypeMask : 1
ICMPRetry : 1
ICMPTimeout : 1000
EMCUserName :
EMCPassword :
EMCPort :
WSMANUserName : root
WSMANPassword : {1, 0, 0, 0…}
WSMANCertPath :
WSMANPort : 443
WSMANTimeout : 60
WSMANRetries : 3
WSMANBitSettings : 7
BladeDiscovery : 0
NameDisplay :
SSHUserName :
SSHPassword :
SSHPort : 22
SSHTimeout : 3
SSHRetries : 3
RowVersion : {0, 0, 0, 0…}

Any pointers in the right direction would be great

You could use Export-CliXml and Import-CliXml. Export-CliXml creates an XML-based representation of an object and stores it in a file. Import-CliXml brings back the object from the saved file.

I believe both cmdlets are available since PowerShell version 2.

An alternative would be to use ConvertTo-Json and ConvertFrom-Json, and handle the file saving and loading yourself.

I hope that helps.

Thanks for the reply Daniel however I was not successful and ended up with the same thing I have been since the beginning. Text that looks nice on the console but is useless for processing.

Example lets take sample “Device to Group Mappings” table. Notice that unlike the other two tables that are like this

This one is has more traditional headers already.
Id IdDiscoveryConfiguration IdDiscoveryConfigurationGroup
102 58 1
1 1 2
2 2 2
3 3 3
4 4 3
5 5 4
6 6 4
16 16 7
86 43 7
87 44 7
88 45 7
89 46 7
90 47 7
91 48 7
92 49 7
93 50 7
94 51 7
95 52 7
96 53 9
103 59 9

maybe I’m missing its not so easy in powershell however python this is super straight forward.
import pandas as pd
data_frame = pd.read_table(‘path’, sep=’\s+’)
(20, 3)
df.Id or df.IdDiscoveryConfiguration

which will list out everything nicely and putting in simple comparison logic to rebuild stuff is simple once you can reference the data.

Powershell leaves me with a (20,1) shape which is not going to work.

I have the MOL books and I’m not really sure if I missed anything in there that would help me. Again I want this all in powershell so I can give it away for others instead of them having to send me stuff everytime to get this bit of processing done.



It’s a little hard to believe that there is no way to get data from a data base as a table. Anyway this here might help you transforming your list to a table:
stackoverflow - Powershell convert list formatted record into table format
Or this guides to a similar direction:
Create collections of custom objects
and … I’m not sure but this might be helpful for you too:

You can try to use ConvertFrom-String -Template, but I’m think modifying source of your data into objects much easier :slight_smile:

@Olaf Soyk
Thank you very much for the link Join-Object

@Max Kozlov
I went ahead and used Notepad++ for the table that already had headers the way you would expect and turned it into a nice csv and it worked great. The others I was able to come up with a solution.

Forgive me for not posting the code yet I will edit this post before the end of the night and show you what I came up with :slight_smile: I think you will like it.

One of my idea’s was to read in the text file to a hash table. You can see what I mean assuming you have more than 1 network adapter. Things that would need to be fixed. The keys have extra whitespace that would need to be removed. Also would need to check to see if key is already created if hash table ContainsKey() then could just add or append to its value?

#Get-Netadapter | fl > c:\test.txt

$test = Get-Content -Path C:\test.txt

$global:i = 0
$global:count = $test.Count

$array0 = @{}

foreach ($line in $test){

    $key = $test[$i].Split(':')[0]
    $value = $test[$i].Split(':')[1]  
    $i = $i + 1
    if ($i -ge $count) {break}


you not use $line in your code but use $test[$i], this way is not ‘foreach’ way :slight_smile:
you did not explain, you have one file for one device or may devices in one file.

if many in one than you can ‘append’ data into hash but it is not practically usable.

but better if you start with saving your data to array of hashes/objects/ or hash of hashes/objects/
something like this (if your data guaraneed to not have the same fields for one object)
[not tested]

$data = {}.Invoke() # use collection instead of PS array for fast adding
$current = @{} # init current object hash
foreach ($line in (Get-Content $datafile) { # foreach have current line in $line variable
  $name, $value = $line -split ' : ' # use PS split for regex usage instead of char array for string .split()
  if ($current.ContainsKey($name)) { # current info already have this key
     # object filled, save it to array
     $current=@{}      # reinit current object hash
  #fill new field of current object 
# save last object
if ($current.Count) {

this way you have collection of hashes (or you can have connection of objects if you save it as
$data.Add([PSCustomObject]$current) )

you also can have hash of objects if you have unique field (id, for example)

$data = @{}
# ...
# saving
$data[$current.ID] = [PSCustomObject]$current

thus you can get all of your device data with $data[$index] or $data[$deviceid]

I ended up using SMSS to export the tables I wanted to excel. Then it was very simple to convert xls file to csv notepad++.

$device = Import-Csv -Path C:\temp\device.csv
$group = Import-Csv -Path C:\temp\group.csv
$map = Import-Csv -Path C:\temp\map.csv

$device | ft -AutoSize

Id Address      SNMPGetCommunity CIMUserName        IPMIUserName SubnetMask      WSMANUserName
-- -------      ---------------- -----------        ------------ ----------      -------------
1   public                                  root
2   public                                  root
5 public                                  root
6   public                                 
7   public                                 
8  public                                 
9  public                                 

$group | ft -AutoSize

Id GroupName  SNMPGetCommunity SubnetMask
-- ---------  ---------------- ----------
1  All Ranges
2  OOB-iDRAC  public 
4  OOB-CMC    public 
5  HV-Cluster public 
6  EQL        public 

$map | ft -AutoSize

Id IdDiscoveryConfiguration IdDiscoveryConfigurationGroup
-- ------------------------ -----------------------------
1  1                        2
2  2                        2
5  5                        4
6  6                        5
7  7                        5
8  8                        6
9  9                        6

$tempmap = Join-Object -Left $map -Right $group -where {$args[0].IdDiscoveryConfigurationGroup -eq $args[1].Id}
-LeftProperties "IdDiscoveryConfiguration" -RightProperties "GroupName","SNMPGetCommunity" -Type AllInBoth
$tempmap | ft -AutoSize

IdDiscoveryConfiguration GroupName  SNMPGetCommunity
------------------------ ---------  ----------------
1                        OOB-iDRAC  public
2                        OOB-iDRAC  public
5                        OOB-CMC    public
6                        HV-Cluster public
7                        HV-Cluster public
8                        EQL        public
9                        EQL        public
                         All Ranges

$finalmap = Join-Object -left $device -Right $tempmap -where {$args[0].Id -eq $args[1].IdDiscoveryConfiguration}
 -LeftProperties "Address","SubnetMask","WSMANUserName","CIMUserName","SNMPGetCommunity" -RightProperties "GroupName" -T
ype AllInBoth
$finalmap | ft -AutoSize

Address      SubnetMask      WSMANUserName CIMUserName        SNMPGetCommunity GroupName
-------      ----------      ------------- -----------        ---------------- --------- root                             public           OOB-iDRAC root                             public           OOB-iDRAC root                             public           OOB-CMC                                  public           HV-Cluster                                  public           HV-Cluster                                  public           EQL                                  public           EQL
                                                                               All Ranges