Reading in data from a csv powershell

cmatchett
cmatchett used Ask the Experts™
on
Hi Expects ! :)

I have a table of virtual machine specifications
Certain specs have multiple disks and instead of creating multiple lines for multiple disks, i was wondering if you can put them in brackets in the CSV?

ID                               |  TYPE    |      CPU        |      MEMORY  |      VOLUME      | SIZE
PDSTPTSPEC001       | TEST      |        2              |            4         |             D:               | 100
PDSTPTSPEC001       | TEST        |      2          |            4         |                f:               |100

could become in the csv

ID                               |  TYPE    |      CPU        |      MEMORY  |             VOLUME               | SIZE
PDSTPTSPEC001       | TEST      |        2              |            4         |            ( D:  E:)       | (100, 100)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Import-Csv -Path .\input.csv |
	Group-Object -Property ID |
	ForEach-Object {
		$Volumes = ($_.Group | Select-Object -ExpandProperty Volume) -join ', '
		$Sizes = ($_.Group | Select-Object -ExpandProperty Size) -join ', '
		$_.Group | Select-Object -First 1 -Property ID, TYPE, CPU, MEMORY, @{n='VOLUME'; e={"($($Volumes))"}}, @{n='SIZE'; e={"($($Sizes))"}}
	} |
	Export-Csv -NoTypeInformation -Path .\output.csv

Open in new window

Author

Commented:
so what should be the separator be for the volumes?  should it be (D:,E:) or  ('D:','E:')

Author

Commented:
what i want to do is take each volume like you would in array, for example D: would be volumes[0]

and assign the value to powershell parameter
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
That's not at all what your question was about ( i was wondering if you can put them in brackets in the CSV).
To process that in Powershell, you'll need something like this:
$Specs = Import-Csv -Path .\input.csv |
	Group-Object -Property ID |
	ForEach-Object {
		$Volumes = $_.Group | Select-Object -ExpandProperty Volume
		$Sizes = $_.Group | Select-Object -ExpandProperty Size
		$_.Group | Select-Object -First 1 -Property ID, TYPE, CPU, MEMORY, @{n='VOLUMES'; e={$Volumes}}, @{n='SIZES'; e={$Sizes}}
	}

$Specs | Format-Table -AutoSize
"Volume 0 for $($Specs[0].ID):"
$Specs[0].Volumes[0]

Open in new window

Daryl BamforthTechnical Expert

Commented:
You wouldn't have the brackets at all, the separator depends on what you want it to be as you can specify this at export/import. As people use different data within fields, the key is to choose a separator that won't be found in your dataset, otherwise you end up with misaligned data on import.

Author

Commented:
i don't understand what format the csv should be in for this to work
Daryl BamforthTechnical Expert

Commented:
If you are wanting to have these imported from a CSV and you really want them combined, you are going to have to split each field after you re-import it. As far as the import-csv function is concerned the separator acts to determine one field from the next. If you need to be able to separately reference each disk and value you would be better just leaving them on different rows.

After import if you need to work with an entire computer object you could filter your import.

$computer = $csvimport | where-object {$_.id -eq 'PDSTPTSPEC001'}

Open in new window


At this point, if you have multiple values for computer, they are now all referenced within the new $computer object.

$computer[0].volume
$computer[0].size

You need to look at what your data is being used for, and then determine the best way to manipulate it to return the information that is pertinent to the scenario.
Daryl BamforthTechnical Expert

Commented:
CSV import isn't going to work like that from the outset. If you want to create a new powershell object with those values you will need to split out each field and then build this into a new object.

Author

Commented:
so just have a separate line for each volume in the csv?
Daryl BamforthTechnical Expert

Commented:
Yes. Especially if you already have your data formatted like this.

If you have access to the source of data (for example, if it is a database, or from VMWare) you can probably use powershell to query it directly.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
I don't quite get your question about "i don't understand what format the csv should be in for this to work".
The title of this topic is "Reading in data from a csv", so I have to assume you already have a csv in the format you described in your initial question, and that is obviously the format my script expects.
ID,TYPE,CPU,MEMORY,VOLUME,SIZE
PDSTPTSPEC001,TEST,2,4,D:,100
PDSTPTSPEC001,TEST,2,4,E:,100

Open in new window

Author

Commented:
yes, but this format is inefficient because if the particular spec has 5 volumes, its 5 lines per spec to cover each volume.

It would be much more efficient to have a single line in the csv per spec that covered the 5 volumes and their respective sizes
Daryl BamforthTechnical Expert

Commented:
I suspect he was asking
i was wondering if you can put them in brackets in the CSV?


i.e. he was wanting to modify his CSV directly so that when he did import it the resulting powershell object would include arrays for multiple volumes/sizes.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
And that is exactly what my initial script (https:#a42345955) produces:
input.csv I used:
ID,TYPE,CPU,MEMORY,VOLUME,SIZE
PDSTPTSPEC001,TEST,2,4,D:,100
PDSTPTSPEC001,TEST,2,4,E:,100
PDSTPTSPEC002,TEST,2,4,D:,200
PDSTPTSPEC002,TEST,2,4,E:,300
PDSTPTSPEC002,TEST,2,4,F:,400

Open in new window

output.csv generated by the script:
"ID","TYPE","CPU","MEMORY","VOLUME","SIZE"
"PDSTPTSPEC001","TEST","2","4","(D:, E:)","(100, 100)"
"PDSTPTSPEC002","TEST","2","4","(D:, E:, F:)","(200, 300, 400)"

Open in new window

Author

Commented:
your output is the format i want to start with.  I then want to read in your csv into a function to create a virtual machine
Daryl BamforthTechnical Expert

Commented:
Not really inefficient. It is just lines of text. Powershell will quite happily import thousands of lines within a few seconds. But this is where it comes down to what it is you are wanting to do with the data and do you have access to the original source.

Why is it in a CSV in the first place? Why not just query VMWare directly?

Essentially, there are a good many ways to solve the problem, but you need to know what your desired outcomes are first to decide as to the most appropriate for your scenario.

Author

Commented:
in the end, we want a database of specs that PS reads and then creates virtual machines from
a CSV is the dirty way to achieve the same as we build the DB

Author

Commented:
Daryl Bamforth, a CSV with too many lines is also hard to maintain
Daryl BamforthTechnical Expert

Commented:
So you are creating VM's, so for your Drives and Sizes just use a different separator and split these fields after import.

So

"PDSTPTSPEC002","TEST","2","4","D:;E:;F","(200;300;400)"

then after import

$server.volume.split(';')

would return
D:
E:
F:
$server.size.split(';')

would return
200
300
400

You can add them into your create vm script.
Daryl BamforthTechnical Expert

Commented:
cmatchett  .. What is your data source? Are you using this to create your a VM?
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
If you want to start with a csv to create machines, the original format is way more "efficient" when it comes to avoiding errors while building the DB.
You'll see immediately which volume has which size, and whether all volumes are defined to start with. With lists in a field, that's hard to do while doing it the "dirty" way.
And the original format is what a DB query would return. You don't put tables inside tables.
Powershell doesn't care how long the input file is.
You can keep the original easily in Excel until the DB is up, and use all of its filtering functions to handle the data.

Author

Commented:
if anyone feels the points should be awarded differently, please let me know

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today