none
Powershell reading CSV input RRS feed

  • Question

  • Hi, 

    i'm trying to get my snippet of my main script to read a CSV file below. I've been looking at this for a week and am getting nowhere. My thoughts are a nest ForEach-Object, but i'm not sure if that's the route that needs to be taken.

    This is what i'm trying to accomplish... looking at the portion of my CSV file below, i am trying to perform a get-childitem. The first time i tried to run this portion of the script, it ran but created a 30MB+ file. I need to have each of column A make it's own file instead of one huge file.

    Hopefully someone can help :)

    From my CSV file

    Name,Path
    OIT-BIO,V:\LI-SHARE\OIT\BIO
    OIT-BES,V:\LI-SHARE\OIT\BIO

    #takes a LONG time to run... do not run below this line
    Get-Content \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp.txt | ForEach-Object {
        gci -r $_ | % {$_ | add-member -name "Owner" -membertype noteproperty -value (get-acl $_.fullname).owner -passthru} | Select fullname,creationtime,lastwritetime,owner | 
        Export-Csv -Append \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp_$_.csv
        }



    Pennsylvania Department of Labor and Industry ~Windows Enterprise Administration Team

    Friday, March 4, 2016 2:30 PM

Answers

  • We already gave you the answer above.  You need to learn enough about Windows and PowerShell to be able to understand the answer:

    Import-Csv E:\FS10Quotas_AuditTemp.csv | 
         ForEach-Object {
              Write-Host $_.Name, $_.Path -fore green
              Get-ChildItem $_.Path | Export-Csv $_.Name
         }

    This will create one CSV for each line in the CSV.


    \_(ツ)_/

    • Marked as answer by Jim_Anderson Monday, March 7, 2016 4:48 PM
    Monday, March 7, 2016 4:43 PM

All replies

  • Sorry but it is almost impossible to understand what you are asking.

    What is this: "\\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp.txt"

    It looks like a plain text file.  What is in it?

    Where is the CSV file you are referencing?


    \_(ツ)_/

    Friday, March 4, 2016 3:30 PM
  • Get-Content E:\FS10Quotas_AuditTemp.csv | ForEach-Object {
        gci -r $_ | % {$_ | add-member -name "Owner" -membertype noteproperty -value (get-acl $_.fullname).owner -passthru} | Select fullname,creationtime,lastwritetime,owner | 
        Export-Csv -Append E:\FS10Quotas_AuditTemp_$_.csv
        }

    updated...

    Basically, if the CSV file from the above, would like a CSV file created with the name OIT-BIO.csv with the contents of V:\LI-SHARE\OIT\BIO

    then, i would like a second CSV created with the name OIT-BES.csv with the contents of V:\LI-SHARE\OIT\BIO.

    the way i have it currently set up.... it write one HUGE file.......does this make sense? i'm trying to get column A to write each file name with the contents of column B.


    Pennsylvania Department of Labor and Industry ~Windows Enterprise Administration Team

    Friday, March 4, 2016 3:52 PM
  • To read a csv file we would use Import-Csv.

    See: help Import-Csv -Examples


    \_(ツ)_/

    Friday, March 4, 2016 4:18 PM
  • Import-Csv E:\FS10Quotas_AuditTemp.csv |
         ForEach-Object {
              Write-Host $_.Name, $_.Path -fore green
              Get-ChildItem $_.Path
         }


    \_(ツ)_/

    Friday, March 4, 2016 4:21 PM
  • Maybe I should have taken a screenshot instead... :(

    This is my desired output...........


    Pennsylvania Department of Labor and Industry ~Windows Enterprise Administration Team

    Friday, March 4, 2016 4:43 PM
  • What is you desired output?  I see3 the names of Excel spreadsheets. Are you trying to get a list of spreadsheets.  You have to be clear about what you are trying to do.


    \_(ツ)_/

    Friday, March 4, 2016 4:50 PM
  • I still dont understand what are you trying to do here
    Can you show the output of 

    Get-Content \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp.txt 

    Regards

    Friday, March 4, 2016 5:01 PM
  • The desired output is to use the values from column A in the AuditTemp.csv as a label for the CSV files. Then the get-child item would use the directory from column B and write to the file matching in column A.   does the screen shot on 3/4 make sense? I tried to lay it out like the desired output should look.

    I can get the script to loop, but it makes one HUGE CSV file..... I wanted to break it up into smaller CSV files so I can link them in sharepoint.


    Pennsylvania Department of Labor and Industry ~Windows Enterprise Administration Team

    Monday, March 7, 2016 4:33 PM
  • We already gave you the answer above.  You need to learn enough about Windows and PowerShell to be able to understand the answer:

    Import-Csv E:\FS10Quotas_AuditTemp.csv | 
         ForEach-Object {
              Write-Host $_.Name, $_.Path -fore green
              Get-ChildItem $_.Path | Export-Csv $_.Name
         }

    This will create one CSV for each line in the CSV.


    \_(ツ)_/

    • Marked as answer by Jim_Anderson Monday, March 7, 2016 4:48 PM
    Monday, March 7, 2016 4:43 PM
  • thanks for the feedback! I will do some more research for this! This is a start for me... Loop scripting in Powershell is newer concept me. For some reason, I thought I would have to do a nest loop script.

    Fair enough :).... thanks for all your help!


    Pennsylvania Department of Labor and Industry ~Windows Enterprise Administration Team

    Monday, March 7, 2016 4:50 PM
  • Please do not post colorized code.  It is impossible to read.

    \_(ツ)_/

    Monday, March 7, 2016 4:55 PM
  • It is still broken and pretty much unreadable.  Use code posting control on edit toolbar.  It looks like <>


    \_(ツ)_/

    Monday, March 7, 2016 6:39 PM
  • ##############################################################################################################################################################################################################
    ######################################################################################## WORKING CODE BELOW !!!!!!!!! ########################################################################################
    ##############################################################################################################################################################################################################
    
    <#
    Generate File/Folder Report for FS10Quotas usage
    
    this script is be ran from a UNC path
    
    #>
    
    Clear-Host
    
    Import-Csv -Delimiter "," -Header a,b \\lihbg000fs10\E$\StorageReports\ScriptedReports\FS10Quotas.csv | foreach {
        Write-Output $_.b | Out-File -Append \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp.txt
        }
    
    #V:\\ drive
    (get-content \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp.txt) | ForEach-Object {$_ -replace 'V:', '\\lihbg000fs10\V$'} | Out-File \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp1.txt
    
    #W:\\ drive
    (get-content \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp1.txt) | ForEach-Object {$_ -replace 'W:', '\\lihbg000fs10\w$'} | Out-File \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp2.txt
    
    #U:\\ drive
    (get-content \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp2.txt) | ForEach-Object {$_ -replace 'u:', '\\lihbg000fs10\u$'} | Out-File \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp3.txt
    
    Remove-Item \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp.txt
    Remove-Item \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp1.txt
    Remove-Item \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp2.txt
    Rename-Item \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp3.txt \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp.txt
    
    #takes a LONG time to run... do not run below this line
    Get-Content \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp.txt | ForEach-Object {
        gci -r $_ | % {$_ | add-member -name "Owner" -membertype noteproperty -value (get-acl $_.fullname).owner -passthru} | Select fullname,creationtime,lastwritetime,owner | 
        Export-Csv -Append \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp_$_.csv
        }
    
    #Cleaning up results
    Rename-Item \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp.csv \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp1.csv
    Get-Content \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp1.csv | Select-Object -Skip 1 | Out-File \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_Audit.csv
    
    #removing tempfiles
    Remove-Item \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp.txt
    Remove-Item \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp1.csv
    
    posted using <>.... never knew about that! ;)

    Pennsylvania Department of Labor and Industry ~Windows Enterprise Administration Team

    Monday, March 7, 2016 6:44 PM
  • your code worked when I put the titles back in...lol

    PS D:\Example-Import-CSV> Import-Csv d:\FS10Quotas.csv | ForEach-Object { Write-Host $_.Name, $_.Path -fore green Get-ChildItem $_.Path | Export-Csv $_.Name } LIHBG000FS16_BIO$ D:\test1\ LIHBG000FS16_BBVS$ D:\test2\



    Pennsylvania Department of Labor and Industry ~Windows Enterprise Administration Team

    Monday, March 7, 2016 6:45 PM
  • You should also never not format your lines:

    Example:

    Get-Content \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp1.csv | Select-Object -Skip 1 | Out-File \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_Audit.csv

    This should be:

    Get-Content \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_AuditTemp1.csv |
         Select-Object -Skip 1 | 
         Out-File \\lihbg000fs10\E$\StorageReports\ReportsSharePoint\CreationWriteAudit\FS10Quotas_Audit.csv
    
    You can also make it more readable by using variables:
    Get-Content $auditcsv |
         Select-Object -Skip 1 | 
         Out-File $reportcsv
    





    \_(ツ)_/


    • Edited by jrv Monday, March 7, 2016 6:49 PM
    Monday, March 7, 2016 6:49 PM
  • FS10Quotas.csv contents is below...

    Name,Path
    LIHBG000FS16_BIO$, D:\test1\
    LIHBG000FS16_BBVS$, D:\test2\

    and here's my code.......

    THIS  is what I was trying to accomplish... yeah yeah.. I know I have to clean up the code, but I got accomplished what I needed to :)

    ### Below works
     $testcsv = Import-Csv d:\FS10Quotas.csv 
         ForEach ($test in  $testcsv)
            {
                $field1 = $test.name   
                $field2 = $test.path
                "$field1, $field2"
                foreach ($f in Get-ChildItem -Recurse $field2)
                    {
                        $f > d:\$field1.txt
                    }
            }
            



    Pennsylvania Department of Labor and Industry ~Windows Enterprise Administration Team

    Tuesday, March 8, 2016 9:07 PM