locked
Need to import csv and manipulate one column RRS feed

  • Question

  • I have a monthly report output by an FTP program into CSV format. The CSV file has headers and 3 columns:

    1) a group name

    2) the number of transfers that group made

    3) the total size of all their transfers (in bytes)

    Example (with headers and 6 rows of data for 7 rows total):

    "monthly transfers","sum of monthly transfers","sum of file size"
    "group1 (Prod to Dev)","26","11556381672"
    "group2 (Prod to Dev)","5","348197396"
    "group3 (Prod to Dev)","14","1272913379"
    "group1 (Dev to Prod)","0","0"
    "group2 (Dev to Prod)","1976","2036311426"
    "group3 (Dev to Prod)","1","131"
    

    I have been asked to write a script that will convert the bytes to megabytes and then sort based on group name and export the new data so auditors (non-technical people) can review it.

    I've worked with Excel a bunch in Powershell (I.E. opening a com object and writing directly to a cell) and I'd prefer to do it that way, but the server this script needs to run on doesn't have MS Office installed and can't have anything else installed on it, so I'm trying to learn about import-csv and such. I've come up with this:

    $file = "C:\ftp\ftp_report.csv"
    $csv = import-csv $file
    
    $csv | sort "monthly transfers"

    The part I'm stuck on is converting that third column from bytes to megabytes. I know that in Powershell I can do "X / 1mb" to convert a number from bytes to megabytes, but I'm not sure of how best to do this in a specific column of a CSV file. Should I create a hash table? Should I count the number of rows and then do a "foreach" on

    $csv[0].'sum of file size' / 1mb

    ?

    Am I overthinking this and there's a really easy way to manipulate all values in a column?

    Thank you in advance.


    zarberg@gmail.com

    Wednesday, February 5, 2014 10:02 PM

Answers

  • Hi Zarberg,

    that ought to work just fine. Here's the corrected line that ought to work:

    $record | Add-Member -MemberType NoteProperty -Name "Sum of File Size" -Value ($row.'sum of file size' / 1mb)

    Note however, that this may give some unwieldy numbers (with plenty of digits after the dot). Fixing that may require a bit more effort :)

    Cheers,
    Fred


    There's no place like 127.0.0.1

    • Marked as answer by zarberg Thursday, February 6, 2014 2:22 PM
    Thursday, February 6, 2014 2:19 PM
  • FYI, here's the final script that I put together. I was also asked to rename that 1 column, name the file based on the previous month, and delete the original file.

    $file = ".\Moveit Central- Dev to Prod_Prod to Dev Monthly Transfers.csv"
    $csv = import-csv $file | Select-Object 'Monthly Transfers', 'Sum of Monthly Transfers', @{name='sum in megabytes';Expression={$_.'sum of file size'}}
    
    $output = @()
    
    foreach ($row in $csv) {
        $record = New-object system.object
        $record | Add-Member -MemberType NoteProperty -Name "Monthly Transfers" -value $row.'monthly transfers'
        $record | Add-Member -MemberType NoteProperty -Name "Sum of Monthly Transfers" -value $row.'sum of monthly transfers'
        $record | Add-Member -MemberType NoteProperty -Name "Sum in Megabytes" -Value ( "{0:N2}" -f ($row.'sum in megabytes' / 1mb ))
        $output += $record
        }
    
    $month= (Get-Date).AddMonths(-1).ToString("MMM")
    $newfile = $output | sort "monthly transfers"
    $newfile | export-csv "Dev to Prod_Prod to Dev Transfer for $month.csv" -NoTypeInformation
    remove-item $file


    zarberg@gmail.com

    • Marked as answer by zarberg Thursday, February 6, 2014 9:59 PM
    Thursday, February 6, 2014 3:32 PM

All replies

  • Ok, so I figured out to change the bytes to megabytes to do the following:

    foreach ($item in $csv) {
        $temp = $item.'sum of file size' / 1mb
        "{0:N2}" -f $temp}

    How do I write that to the corresponding column of the new CSV file?

    zarberg@gmail.com

    Thursday, February 6, 2014 1:30 PM
  • Ok, so I have the following code:

    $file = "C:\directory\test1.csv"
    $csv = import-csv $file
    
    $output = @()
    
    foreach ($row in $csv) {
        $record = new-object system.object
        $record | Add-Member -MemberType NoteProperty -Name "Monthly Transfers" -value $row.'monthly transfers'
        $record | Add-Member -MemberType NoteProperty -Name "Sum of Monthly Transfers" -value $row.'sum of monthly transfers'
        $record | Add-Member -MemberType NoteProperty -Name "Sum of File Size" -Value $row.'sum of file size'
        $output += $record
        }
    
    $newfile = $output | sort "monthly transfers"
    $newfile | export-csv C:\directory\newfile1.csv -NoTypeInformation

    I'm just unsure of how to change that "Sum of File Size" column to megabytes instead of bytes, specifically the syntax for it. I know I either have to do "x / 1mb" or multiply it by 0.00000095367, but how do I format that within the loop? Can I put quotes or braces or parens around the $row.'sum of file size' and then just do / 1mb after?

    zarberg@gmail.com

    Thursday, February 6, 2014 2:10 PM
  • Hi Zarberg,

    that ought to work just fine. Here's the corrected line that ought to work:

    $record | Add-Member -MemberType NoteProperty -Name "Sum of File Size" -Value ($row.'sum of file size' / 1mb)

    Note however, that this may give some unwieldy numbers (with plenty of digits after the dot). Fixing that may require a bit more effort :)

    Cheers,
    Fred


    There's no place like 127.0.0.1

    • Marked as answer by zarberg Thursday, February 6, 2014 2:22 PM
    Thursday, February 6, 2014 2:19 PM
  • Hi Zarberg,

    that ought to work just fine. Here's the corrected line that ought to work:

    $record | Add-Member -MemberType NoteProperty -Name "Sum of File Size" -Value ($row.'sum of file size' / 1mb)

    Note however, that this may give some unwieldy numbers (with plenty of digits after the dot). Fixing that may require a bit more effort :)

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Indeed, you are correct about many decimal places. Hmmm ... how would one put "{0:N2}" -f into that line. Would you do it at the $record variable at the beginning of the line?

    Edit: A little playing around did it. I changed that line further to be:

        $record | Add-Member -MemberType NoteProperty -Name "Sum of File Size" -Value ( "{0:N2}" -f ($row.'sum of file size' / 1mb ))


    zarberg@gmail.com


    • Edited by zarberg Thursday, February 6, 2014 2:40 PM
    Thursday, February 6, 2014 2:32 PM
  • Probably like this, right at the parameter:

    ("{0:N2}" -f ($row.'sum of file size' / 1mb))


    There's no place like 127.0.0.1

    Thursday, February 6, 2014 2:40 PM
  • FYI, here's the final script that I put together. I was also asked to rename that 1 column, name the file based on the previous month, and delete the original file.

    $file = ".\Moveit Central- Dev to Prod_Prod to Dev Monthly Transfers.csv"
    $csv = import-csv $file | Select-Object 'Monthly Transfers', 'Sum of Monthly Transfers', @{name='sum in megabytes';Expression={$_.'sum of file size'}}
    
    $output = @()
    
    foreach ($row in $csv) {
        $record = New-object system.object
        $record | Add-Member -MemberType NoteProperty -Name "Monthly Transfers" -value $row.'monthly transfers'
        $record | Add-Member -MemberType NoteProperty -Name "Sum of Monthly Transfers" -value $row.'sum of monthly transfers'
        $record | Add-Member -MemberType NoteProperty -Name "Sum in Megabytes" -Value ( "{0:N2}" -f ($row.'sum in megabytes' / 1mb ))
        $output += $record
        }
    
    $month= (Get-Date).AddMonths(-1).ToString("MMM")
    $newfile = $output | sort "monthly transfers"
    $newfile | export-csv "Dev to Prod_Prod to Dev Transfer for $month.csv" -NoTypeInformation
    remove-item $file


    zarberg@gmail.com

    • Marked as answer by zarberg Thursday, February 6, 2014 9:59 PM
    Thursday, February 6, 2014 3:32 PM