asked on

Reading in data from a csv powershell

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)

oBdA

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

cmatchett

ASKER

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

cmatchett

ASKER

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

oBdA

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 Bamforth

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.

cmatchett

ASKER

i don't understand what format the csv should be in for this to work

Daryl Bamforth

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 Bamforth

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.

cmatchett

ASKER

so just have a separate line for each volume in the csv?

Daryl Bamforth

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.

oBdA

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

cmatchett

ASKER

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 Bamforth

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.

oBdA

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

cmatchett

ASKER

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 Bamforth

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.

cmatchett

ASKER

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

cmatchett

ASKER

Daryl Bamforth, a CSV with too many lines is also hard to maintain

Daryl Bamforth

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 Bamforth

cmatchett .. What is your data source? Are you using this to create your a VM?

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cmatchett

ASKER

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