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)
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.

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

cmatchettAuthor Commented:
so what should be the separator be for the volumes?  should it be (D:,E:) or  ('D:','E:')
cmatchettAuthor 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
Discover the Answer to Productive IT

Discover app within WatchGuard's Wi-Fi Cloud helps you optimize W-Fi user experience with the most complete set of visibility, troubleshooting, and network health features. Quickly pinpointing network problems will lead to more happy users and most importantly, productive IT.

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):"

Open in new window

Daryl BamforthTechnical ExpertCommented:
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.
cmatchettAuthor Commented:
i don't understand what format the csv should be in for this to work
Daryl BamforthTechnical ExpertCommented:
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 {$ -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.


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 ExpertCommented:
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.
cmatchettAuthor Commented:
so just have a separate line for each volume in the csv?
Daryl BamforthTechnical ExpertCommented:
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.
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.

Open in new window

cmatchettAuthor 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 ExpertCommented:
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.
And that is exactly what my initial script (https:#a42345955) produces:
input.csv I used:

Open in new window

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

Open in new window

cmatchettAuthor 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 ExpertCommented:
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.
cmatchettAuthor 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
cmatchettAuthor Commented:
Daryl Bamforth, a CSV with too many lines is also hard to maintain
Daryl BamforthTechnical ExpertCommented:
So you are creating VM's, so for your Drives and Sizes just use a different separator and split these fields after import.



then after import


would return

would return

You can add them into your create vm script.
Daryl BamforthTechnical ExpertCommented:
cmatchett  .. What is your data source? Are you using this to create your a VM?
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.

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
cmatchettAuthor Commented:
if anyone feels the points should be awarded differently, please let me know
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.