none
Import-Csv with a column that needs to be multivalued RRS feed

  • Question

  • I am trying to import for Exchange having ResourceDelegates as a column in my CSV file.  When I import I get a [string]"Resource1,Resource2,Resource3" instead of @("Resource1","Resource2","Resource3").  I can use "-split ',' " but I don't want to mess up when I am running the commands straight from the command line parameter "-ResourceDelegates 'Resource1', 'Resource2', 'Resource3' " it is works beautifully. 

    The issue with using "-split" is that I am trying to keep all capabilities of accepted data in the "Set-CalendarProcessing <Identity> -ResourceDelegates <delegates[]>" alive and well which includes being able to use our DistinguishedName's CN:  "LastName, FirstName".  "-split" would mess this up. 

    CSV File sample data:

    "Name","ResourceDelegates"
    "GroupA","Resource1,Resource2,Resource3"
    "GroupB","Resource1, Resource2, Resource3"

    Function Test-CsvImportArray 
    {
    	Param(
    		[Parameter(ValueFromPipelineByPropertyName = $true)]
    		[string[]]$ResourceDelegates
    	)
    
    	"ResourceDelegates Count: $($ResourceDelegates.Count)"
    	"ResourceDelegates Value: $($ResourceDelegates)"
    }

    Then I check the variable using Import-Csv (only showing one Row of CSV import)::

    PS D:\PowerShell> Import-Csv .\CSV\Resource_List.csv | Test-CsvImportArray
    ResourceDelegates Count: 1
    ResourceDelegates Value: Resource1, Resource2, Resource3
    PS D:\PowerShell>

    Now if I did it via the parameters on command line (only showing one Row of CSV import):

    PS D:\PowerShell> Test-CsvImportArray -ResourceDelegates Resource1, Resource2, Resource3
    ResourceDelegates Count: 3
    ResourceDelegates Value: Resource1
    ResourceDelegates Value: Resource2
    ResourceDelegates Value: Resource3
    PS D:\PowerShell>
    What can I do to make this all happen by ONLY changing code in the function?  I don't want to typecast anything before piping to the import.


    Find this post helpful? Does this post answer your question? Be sure to mark it appropriately to help others find answers to their searches.



    • Edited by Chase Roth Monday, October 23, 2017 10:03 AM
    Monday, October 23, 2017 12:54 AM

Answers

  • As jrv pointed out, there's no such thing as a multi-valued column in a CSV (comma-separated values) file. CSV files have one or more columns, each of which contain a single string. If you want to "split" one of those strings into a multiple values, you will need to decide on a delimiter character (preferably one other than a comma) that your script will then split into multiple values when it reads the CSV data.

    One common approach is to use a tab character (represented as "`t" in PowerShell). When you create the CSV file, separate multiple values in that particular column with a tab character. When you read the CSV file, use -split "\t" to split that value into a multiple values.


    -- Bill Stewart [Bill_Stewart]




    Monday, October 23, 2017 4:09 PM
    Moderator

All replies

  • To get a multi-valued column as an array use "-join".


    \_(ツ)_/

    Monday, October 23, 2017 10:07 AM
  • To get a multi-valued column as an array use "-join".


    \_(ツ)_/


    Going to need more of an example that that...  Could you provide the code example as is relevant to my examples.  It would be a great help.

    Find this post helpful? Does this post answer your question? Be sure to mark it appropriately to help others find answers to their searches.

    Monday, October 23, 2017 10:09 AM
  • Have you learned how to use help yet?  It should be the first thing a scripter does with PowerShell.

    $s = 1,2,3,4  -join '|'
    $s -split '|'


    \_(ツ)_/


    • Edited by jrv Monday, October 23, 2017 10:12 AM
    Monday, October 23, 2017 10:12 AM
  • Have you learned how to use help yet?  It should be the first thing a scripter does with PowerShell.

    $s = 1,2,3,4  -join '|'
    $s -split '|'


    \_(ツ)_/


    Thanks for the wise-ass answer...doesn't make the community toxic or anything!  I know how to use help and I know what "-join" does.  I didn't see how you were going to make it relevant to the issue presented.  Maybe you responded before I added the additional details in the OP, that I was trying to avoid using "-split" if there were other ways.  I don't see why I would use "-join" just to split it again.  In essence all you did was change the separator from "," to be "|"...

    I have a working prototype, but am looking for any better solutions.

    Function Test-CsvImportArray 
    {
    	Param(
    		[Parameter(ValueFromPipelineByPropertyName = $true)]
    		[string[]]$ResourceDelegates
    	)
    
    	$ResourceDelegates = ($ResourceDelegates -split ',').Trim()
    	"ResourceDelegates Count: $($ResourceDelegates.Count)"
    	foreach ( $r in $ResourceDelegates ) 
    	{
    		"ResourceDelegates Value: $($r)"
    	}
    }
    
    
    
    

    This results in: 

    PS D:\PowerShell> Import-Csv .\CSV\Resource_List.csv | Test-CsvImportArray
    ResourceDelegates Count: 3
    ResourceDelegates Value: Resource1
    ResourceDelegates Value: Resource2
    ResourceDelegates Value: Resource3
    PS D:\PowerShell>
    

    After seeing above, do you understand why I don't know how or why you wanted me to implement "-join".  Is there something I am missing you're trying to imply I should do to better this code?


    Find this post helpful? Does this post answer your question? Be sure to mark it appropriately to help others find answers to their searches.

    Monday, October 23, 2017 10:22 AM
  • Then why not try to ask a simple question?  What is the issue and what is it you think you want.  Forget all of the fancy code. 

    THe way you are asking the question is a circular explanation ith no tail.  It may make sense to you but it never states what you need to do.

    The parameter takes and array.  It is done like this:

    -Parameter arg1,arg2,arg3

    Notice - no quotes.  It is a string separated by commas. Or:

    $p = 'arg1','arg2','arg3'
    -Parameter $p

    If that is not what you are trying to ask then you will need to find a way to be clear with your question.


    \_(ツ)_/


    • Edited by jrv Monday, October 23, 2017 10:28 AM
    Monday, October 23, 2017 10:27 AM
  • This is just getting worse.  Are you reading anything I have posted?  I presented it is as clear of a view as I thought possible. Apparently, you still haven't grasped that I am IMPORTING from a CSV!  The function works without issue when using the "Test-CsvImportArray -ResourceDelegates Resource1, Resource2, Resource3" directly from the command line, AS SHOWN IN THE ORIGINAL POST.  Showing the code, IMHO was the best way for those looking to respond to understand what was being requested. 

    Maybe the issue is not me, but you.  Before responding to posts assuming people are idiots, take a moment for yourself to understand the issue, then respond.  Would have saved...5 posts of back and forth because YOU didn't understand the question.  If you didn't understand, ask a question instead of throwing out half baked answers, just to try and belittle those coming to the community asking for HELP!

    Could we please move on.  All this jabber is just making it more difficult for anyone reading the post to actually grasp the issue being faced.

    Thus far my best option I can figure is to use a different separator in my CSV instead of comma "," maybe use the pipe "|" or a semi-colon ";".  So I have changed both the CSV File and the Function code and it is working. I don't like it, but if it is the only way to go I may have to live with it. 

    CSV File contents: 

    "Name","ResourceDelegates"
    "GroupA","Resource1;Resource2;Last, First"

    I sample code below.

    Function Test-CsvImportArray 
    {
    	Param(
    		[Parameter(ValueFromPipelineByPropertyName = $true)]
    		[string[]]$ResourceDelegates
    	)
    
    	$ResourceDelegates = ($ResourceDelegates -split ';').Trim()
    	"ResourceDelegates Count: $($ResourceDelegates.Count)"
    	foreach ( $r in $ResourceDelegates ) 
    	{
    		"ResourceDelegates Value: $($r)"
    	}
    }
    
    
    

    PS D:\PowerShell> Import-Csv .\CSV\Resource_List.csv | Test-CsvImportArray
    ResourceDelegates Count: 3
    ResourceDelegates Value: Resource1
    ResourceDelegates Value: Resource2
    ResourceDelegates Value: Last, First
    PS D:\PowerShell>
    PS D:\PowerShell>
    PS D:\PowerShell> Test-CsvImportArray -ResourceDelegates Resource1, 'Resource2', 'Last, First'
    ResourceDelegates Count: 3
    ResourceDelegates Value: Resource1
    ResourceDelegates Value: Resource2
    ResourceDelegates Value: Last, First
    PS D:\PowerShell>
    

    This is WORKING!  It comes out as 3 separate values from the one CSV column.  I just still felt like maybe there is a better way.  If no one comes up with something, I'll just integrate the sample code with my real project and move on.


    Find this post helpful? Does this post answer your question? Be sure to mark it appropriately to help others find answers to their searches.

    Monday, October 23, 2017 10:56 AM
  • As I pointed out above, we use "-split" to turn a string into an array.  It is very simple.  Just do this:

    -ResourceDelegates ($_.ResourceDelegates -split ';')

    That will convert and pass the column as an array.


    \_(ツ)_/

    Monday, October 23, 2017 11:36 AM
  • I think part of your issue is assuming that a CSV can support a multi valued column.  It can't.  What we are doing with a CSV is enclosing a delimited string in a column  and either surrounding the column entry with quotes if we use a comma as a delimiter or using a delimiter that a CSV does not use as a special character.

    The other method of reading a CSV is with the ODBC data provider which can allow us to declare column types in a "schema.ini" file.  Even with the advanced conversion capabilities of the TEXT driver we still can  only declare simple types. 

    See: https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver


    \_(ツ)_/

    Monday, October 23, 2017 12:04 PM
  • Thank you for the additional insight.  Looks as though I may need to do what we figured and use the "-split".  Seems like a bit of a hack, but I think it will serve my purpose.  I'll need to include those examples in my comment based help of my actual function as to remind me and others to use ";" to separate records.

    Find this post helpful? Does this post answer your question? Be sure to mark it appropriately to help others find answers to their searches.

    Monday, October 23, 2017 12:42 PM
  • Not a hack.  That is how it is done in all programing languages.  I don't understand why you would think anything could be easier.'

    Again - spend some time learning PowerShell formally and not just by guesswork.  It will save you a lot wasted time.  You should also take a modern course in basic programming.  "split" has been fundamental to programming for a few decades.  It is used constantly and everywhere.


    \_(ツ)_/

    Monday, October 23, 2017 12:52 PM
  • Again, you've made an assumption that because I setup a small scale test to work out a small issue my overall code, that I am using "guesswork", and didn't already have a solution in mind. Split was the assumed solution all along, but while most languages are dealing with strings of data and having to manipulate those strings is the norm, I thought there may be a different way to "do it better" as PowerShell since it has many ins and outs with Objects rather than just string manipulation, that may have provided a better solution to tell it that the particular column was an Array and treat it as such.  In this case thus far it doesn't seem that is the case though.

    Long story short, you made this an amazingly distressing and frustrating back and forth that didn't need to be that way.  I was looking for a bit of feedback and guidance to maybe find a way to do it I hadn't thought about or just basically reassurance that "split" was really the only way to do it.  But instead I got condescending remarks and elitist attitude.  Not everyone has time to RTFM as the code is just a means to an end for an Administrator.  I am not a developer, I just need the pieces of code to perform the tasks required to get the job done. 

    I'd suggest you reflect on how you interact with people online.  Don't immediately jump to the side of this person doesn't know anything and given the chance many people may surprise you.  Lastly, do yourself and future posters asking for help, from those with more knowledge, skill, and experience,  a favor and READ the posts full and identify what the intent of the question is asking for before responding.  It will go a long way...

    In any event I am done with this.  If someone has anything further to suggest I'll let post open another day or so before marking answered.


    Find this post helpful? Does this post answer your question? Be sure to mark it appropriately to help others find answers to their searches.

    Monday, October 23, 2017 1:29 PM
  • Sorry but it is that rationale that is keeping you from seeing why you are fishing.  A string is an object and "split" is a method on that object.

    Learning PowerShell and the basics of programming would make you aware of that.  Just because you think it makes sense to you does not make it make sense.  Science and technology are not open to individual interpretation.

    In any case just use "split" and realize that it will all become clearer as you gain more knowledge and experience.


    \_(ツ)_/

    Monday, October 23, 2017 1:34 PM
  • As jrv pointed out, there's no such thing as a multi-valued column in a CSV (comma-separated values) file. CSV files have one or more columns, each of which contain a single string. If you want to "split" one of those strings into a multiple values, you will need to decide on a delimiter character (preferably one other than a comma) that your script will then split into multiple values when it reads the CSV data.

    One common approach is to use a tab character (represented as "`t" in PowerShell). When you create the CSV file, separate multiple values in that particular column with a tab character. When you read the CSV file, use -split "\t" to split that value into a multiple values.


    -- Bill Stewart [Bill_Stewart]




    Monday, October 23, 2017 4:09 PM
    Moderator
  • As I pointed out above, we use "-split" to turn a string into an array.  It is very simple.  Just do this:

    -ResourceDelegates ($_.ResourceDelegates -split ';')

    That will convert and pass the column as an array.


    \_(ツ)_/

    Thanks jrv, this solved the issues I was having and will help to fix lots of other issues too!
    Wednesday, October 31, 2018 5:44 PM