Array to CSV cell or File to CSV Cell

Welcome Forums General PowerShell Q&A Array to CSV cell or File to CSV Cell

This topic contains 12 replies, has 5 voices, and was last updated by

 
Moderator
1 month, 2 weeks ago.

  • Author
    Posts
  • #118612

    Participant
    Points: -2
    Rank: Member

    Hi Team,

    Greetings !!!

    I need some help related to CSzv export and formatting

    I have a array which is having 10 txt file(Having Multiple line) content added using +=. I am looking for help to export it to csv so each array diamention value goes to single cell. That means each files content should go to single cell.

    Or using get-content after reading a file how i can get all data in a single cell in CSV.So if 10 files are there 10 column cell will be having those file content. Like that in each row i will be having data for multiple server. I have tried lot but it not working.

    Example

    Server name, File1, File2,........file10
    server 1,file 1 content, file 2 content, go on
    Server 2,file 1 content, file 2 content, go on

     

    Thank you.

    Baneswar

  • #118630

    Participant
    Points: 320
    Helping Hand
    Rank: Contributor

    Can you show us what you've tried? What is not working?

  • #118780

    Participant
    Points: -2
    Rank: Member

    Hi Rob,

    Thanks for your reply. Please find below script which i was trying. Write-output and export CSV both i have tried.

    ##################################

    $serverN = get-content Serverlist.txt

    $conffilecont=New-Object System.Collections.ArrayList
    Write-output " Server Name, conf file " | Out-file -FilePath MonConfdetails$((get-date).tostring("dd-MM-yyyy")).csv -Encoding ascii -append
    Foreach($serverE in $serverN)
    {
    $conffilecont += $serverE
    $TargetFolder = "\\"+$serverE+"\C$\tools\Imm\config"
    $Filelist= Get-Childitem $TargetFolder -Recurse | Where {$_.extension -eq ".conf"} | Sort name
    Foreach($Files in $filelist)
    {
    write-output "$Files"
    $filepath = $TargetFolder +"\" + $files
    $conffilecont += get-content $filepath
    $conffilecont

    }
    Write-output "$conffilecont" | Out-file -FilePath MonConfdetails$((get-date).tostring("dd-MM-yyyy")).csv -Encoding ascii -append
    $conffilecont | export-Csv foo.csv -NoTypeInformation

    ############################################################

    Expected output as per below. Let me know if its possible.

     

    Server name File 1 file 2 file 3
    Server 1 # GSMA BlueCARE Windows Agent
    # CustomScript script configuration file
    #—————————————————————————————————–
    # DEBUG : 1 (yes) 0 (no)
    # MAX_DEBUG_SIZE : maximum size of debug file in bytes
    #—————————————————————————————————–
    DEBUG=0
    MAX_DEBUG_SIZE=1000000
    TIMEOUT=20
    USE_THREADS_ON_WINDOWS=0
    MAX_EVENT_AGE=1
    # Minimum time elapsed since last alert with the same EventCode and Source
    # Monitor will not issue alert if time elapsed is less than this parameter
    # Possible values of unit:
    #  m – Month
    #  d – Day
    #  h – Hour
    #  n – Minute
    #  s – Second (default)
    MINIMUM_ELAPSED_TIME=1h
    go on
    Server 2 # GSMA BlueCARE Windows Agent
    # CustomScript script configuration file
    #—————————————————————————————————–
    # DEBUG : 1 (yes) 0 (no)
    # MAX_DEBUG_SIZE : maximum size of debug file in bytes
    #—————————————————————————————————–
    DEBUG=0
    MAX_DEBUG_SIZE=2k
    TIMEOUT=20
    USE_THREADS_ON_WINDOWS=0
    MAX_EVENT_AGE=1
    # Minimum time elapsed since last alert with the same EventCode and Source
    # Monitor will not issue alert if time elapsed is less than this parameter
    # Possible values of unit:
    #  m – Month
    #  d – Day
    #  h – Hour_test
    #  n – Minute
    #  s – Second (default)
    MINIMUM_ELAPSED_TIME=1h

    Thanks and Regards,

    Baneswar AKoley

  • #119365

    Moderator
    Points: 174
    Team MemberHelping Hand
    Rank: Participant

    Not exactly what you are looking for, but I think it will help get you there.
    Getting the content of the file into a cell can be accomplished by joining the data with a line feed, see below.
    Hope this helps.

    Get-ChildItem -Path C:\temp\Files | ForEach-Object -Begin { 
        $obj = @()
    } -Process {
        $content = (Get-Content -Path $PSItem.fullname) -join "`n"
        $obj += [pscustomobject] @{
            Name    = $PSItem.Name
            Content = $content
        }   
    } -End { 
        $obj | Format-List #export-csv
    }
    
    • #119559

      Participant
      Points: 180
      Rank: Participant

      Hello Mr Wes Stahler,

      I am taking the opportunity to learn from your answer/solution to this post, so I creatd just 5 simple TXT files with about 4 short lines each.  Then I copied your code exactly "as is" and ran it (inside ISE).  I verified that the Get-Child statement worked fine: returning the list of 5 file names.  Then I got the error message:

      cmdlet ForEach-Object at command pipeline position 2
      Supply values for the following parameters:
      Process[0]:

      I must be missing a fundamental Powershell principle of operation or syntax.

      Would be grateful if you can shed some light or give some quick tip/pointers.

      Many thanks,

      Ramon Tan

  • #119381

    Participant
    Points: -2
    Rank: Member

    Hi Wes,

    Greetings !!!

    Thank you Very much. When i ran this command i am getting below error but yes i got the concept now so i will try it withing few days.

    If i put direct file path insteaad of $PSItem.fullname its working

    error details

    —————————
    PS C:\users\bace123w\Desktop> .\fileread.ps1
    Get-Content : Cannot bind argument to parameter 'Path' because it is null.
    At C:\users\bace123w\Desktop\fileread.ps1:6 char:30
    + $content = (Get-Content -Path < <<< $PSItem.fullname) -join "`n" + CategoryInfo : InvalidData: (:) [Get-Content], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.GetContentC ommand Name : Content Value : Name : Name Value :   after changing $PSItem.fullname


    PS C:\users\baneswar\Desktop> .\fileread.ps1

    Name : Content
    Value : sdgdggasdg#$,;
    fewfwgfwerfwe
    #fffwef
    hfile =ffeefwe
    efeffwef

    cd\
    cd users

    Name : Name
    Value :

     

    Thanks and Regards,

    Baneswar Koley

  • #119617

    Moderator
    Points: 174
    Team MemberHelping Hand
    Rank: Participant

    Ramon,

    I suspect it is just a formatting issue. Post your code, I will take a look.  You will see that error if the -Process is omitted (like below).

    
    Get-ChildItem C:\TEMP -Filter *.csv | ForEach-Object -Begin {}
    
    

     

    • #119650

      Participant
      Points: 180
      Rank: Participant

      Hello Mr Wes Stahler,

      Here is my code.  My comments appear after all the code listed between the pre's:

      
      $files = 'C:\Users\XXC\Documents\Tdata\TestTxtFiles\'
      
      Get-ChildItem -Path $files |
      
      Foreach-object -Begin { $obj = @() }
      
      -Process {
      $content = (Get-Content -Path $PSItem.fullname) -join "`n"
      
      $obj += [pscustomobject] @{
      Name = $PSItem.Name
      Content = $content
      }
      }
      -End { $obj | Format-List }
      
      

      There are no "back tick" characters except after the word 'join' followed by double-quote.

      The only Pipe symbol (Vertical line)  is after the Get-Childitem -path $files statement.

      My sincerest gratitude for your willingness to assist.

      Thanks in advance.

       

    • #119655

      Participant
      Points: 870
      Helping Hand
      Rank: Major Contributor

      Keep the -Process parameter in the same line. You can have the first flower brace in the same line and the next in another line.

      Get-ChildItem -Path c:\test |
      Foreach-object -Begin { $obj = @() } -Process {
      # your code
      }
      
  • #119622

    Participant
    Points: 870
    Helping Hand
    Rank: Major Contributor

    @bace ,$PSItem was introduced in Version 3.0 and make sure you have files in the irectory.

  • #119664

    Moderator
    Points: 174
    Team MemberHelping Hand
    Rank: Participant

    Happy to help Ramon!

    Looks like formatting.  Keep the closing bracket for the begin and the -Process on the same line.

    -begin {$obj=@()} -Process { Your code..... }
    

    or

    -begin { $obj=@() } -Process {
        "Your code"
    }
    
  • #119704

    Participant
    Points: 180
    Rank: Participant

    Dear Messrs. KvPrasoon, Stahler,

    My sincerest thanks once again to you both.

    By moving the [-Process] up to the line as [-Begin], and also

    the [-End] up to the line ending with the curly bracket, everything worked.

    I am still unclear on how these params and curly brackets are supposed to work.

    I had this similar experience once before with [Foreach] with the curly brackets.

    Is there some quick written documentation/explanation of the syntactic principles governing Powershell statements and parameters and how they "cross line borders"?  I hope so, I'll do some searching and read up further with the books on PS.  I have avoided the back tick as the "continuation" symbol because it is too tiny to be noticed — only makes the code harder to read and debug.  Most books advise against its use, unless absolutely necessary ... if I remember correctly.

    This post has been extremely educational for me.  I am highly grateful for your assistance.

    Sincerely,

     

     

  • #119724

    Moderator
    Points: 174
    Team MemberHelping Hand
    Rank: Participant

You must be logged in to reply to this topic.