none
Conditional statement and imported CSV RRS feed

  • Question

  • Hi Guys,

    Having issues with a script and i just cannot get it to work.

    The CSV i am importing looks like this:

    CustID,TransNo

    12312K123J,59748

    There are a few thousand entries like this, each entry has a unique custID and Transno. The CSV has many more columns but there are to many to replicate here. The script i am running is:

    #import report
    $students = import-csv "D:\Powershell\Spreadsheets\Bulk Billing List - Students*.csv"
    
    #create variable to store results
    
    #Search report for students who meet the invoice criteria and add them into the results variable
    $ExportStus = @()
    
    #search 
    foreach($student in $students)
    {
        #failing at this point, something to do with the -lt 5000000, works on 50000000000000000000000000 but not 5000000
        #changing it to -gt 0 also works. 
        if($student.TransNo -lt 5000000)
            {
                
                if ($student."rest amount" -eq 0.00)
                {
                    Write-Host "$Student.CustID (T) has Paid"
                    $found = $true
                    $Stu = New-Object System.object
                    $Stu | Add-Member -type NoteProperty -name ALIAS -Value $student.CustID
                    $Stu | Add-Member -type NoteProperty -name BOOKHIRE -Value YES
                    $ExportStus += $Stu
    
                }        
        }  
    }
    
    $ExportStus | export-csv 'D:\Export.csv'

    The whole thing seems simple enough but as i mentioned in the comments the -lt just will not pick up anything regardless of the value of the TransNo. The values in the TransNo i want to pick up are between 0 and 5000000 but if i put $student.transno -gt 0 it picks up everything, if i put $student.transno -lt 500000000000000000000000 it will also pick up everything. However using -lt 5000000 just wont work even though the numbers are mostly around the 50000-60000 area.

    I originally thought a formatting issue with the CSV however if the 5000000000000000000000000 number is picking things up then that does not seem like a formatting issue. Any help would be appreciated.

    Regards

    Pete

    Thursday, December 4, 2014 5:43 AM

Answers

  • There are many syntax and other errors in you script.  I do not see how it can work at all.

    #1 - you cannot use wildcard to load CSV file.
      $students = import-csv "D:\Powershell\Spreadsheets\Bulk Billing List - Students*.csv" #2 - cannot compare  a string to a number.
          this:
                   if ($student."rest amount" -eq 0.00)
           #3 - Try
       if([int]($student.TransNo) -lt 5000000)
    #4 - This is wrong:
       Write-Host "$Student.CustID (T) has Paid"
        should be
        Write-Host "$($Student.CustID) (T) has Paid"

    The following is closer to what you want:

    $csvfile='D:\Powershell\Speadsheets\Bulk Billing List - Students.csv'
    
    Import-Csv $csvfile |
        ForEach-Object{
    $student=$_ if([int]($student.TransNo) -lt 5000000){ if([float]($student.'rest amount') -eq 0){ Write-Host "$($Student.CustID) (T) has Paid" $p=@{ ALIAS=$student.CustID BOOKHIRE='YES' } New-Object PsObject -Property $p } } } | Export-Csv 'D:\Export.csv'
    Of course it is impossible to know the full story without a decent example of your CSV file.





    ¯\_(ツ)_/¯



    • Edited by jrv Thursday, December 4, 2014 8:15 AM
    • Marked as answer by Fezza213 Thursday, December 4, 2014 9:45 PM
    Thursday, December 4, 2014 8:08 AM

All replies

  • Hi Pete,

    $students = Import-CSV "D:\scripts\Get-CSV\students.csv"
    
    foreach($student in $students)
    {
    if($student.TransNo -lt 5000000)
    {
    Write-Host "Less"
    }
    else
    {
    Write-Host "More"
    }
    }

    This works for me, I have the following CSV file:

    CustID,TransNo
    123123123,5000001
    12342,2424

    This returns: "More" for line 1 and "Less" for line 2.


    Nico Martens
    SharePoint/Office365/Azure Consultant

    Thursday, December 4, 2014 7:20 AM
  • Hi Steve,

    if I see this right, you have a type problem. Try this:

    # Old line
    if ($student.TransNo -lt 5000000)
    
    # New line
    if ([int]$student.TransNo -lt 5000000)

    the objects of a csv import is an PSObject with strings for properties, while you are trying to compare numbers with numbers.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Thursday, December 4, 2014 8:00 AM
  • There are many syntax and other errors in you script.  I do not see how it can work at all.

    #1 - you cannot use wildcard to load CSV file.
      $students = import-csv "D:\Powershell\Spreadsheets\Bulk Billing List - Students*.csv" #2 - cannot compare  a string to a number.
          this:
                   if ($student."rest amount" -eq 0.00)
           #3 - Try
       if([int]($student.TransNo) -lt 5000000)
    #4 - This is wrong:
       Write-Host "$Student.CustID (T) has Paid"
        should be
        Write-Host "$($Student.CustID) (T) has Paid"

    The following is closer to what you want:

    $csvfile='D:\Powershell\Speadsheets\Bulk Billing List - Students.csv'
    
    Import-Csv $csvfile |
        ForEach-Object{
    $student=$_ if([int]($student.TransNo) -lt 5000000){ if([float]($student.'rest amount') -eq 0){ Write-Host "$($Student.CustID) (T) has Paid" $p=@{ ALIAS=$student.CustID BOOKHIRE='YES' } New-Object PsObject -Property $p } } } | Export-Csv 'D:\Export.csv'
    Of course it is impossible to know the full story without a decent example of your CSV file.





    ¯\_(ツ)_/¯



    • Edited by jrv Thursday, December 4, 2014 8:15 AM
    • Marked as answer by Fezza213 Thursday, December 4, 2014 9:45 PM
    Thursday, December 4, 2014 8:08 AM
  • A simple example:

    PS >'9' -lt 5000000
    False

    Of course 9 is less.

    PS >9 -lt 5000000
    True

    A CSV file is text and not numbers.

        $student.TransNo.GetType() will return "String"


    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, December 4, 2014 8:16 AM
    Thursday, December 4, 2014 8:14 AM
  • thanks for the help guys, all i needed to do was add the [int] in.

    JRV - thanks for pointing out the other areas, i know i didnt add the full csv but it has about 50 columns of which only three are relevant/useful.

    I do want to say though is that

    "#1 - you cannot use wildcard to load CSV file.
      $students = import-csv "D:\Powershell\Spreadsheets\Bulk Billing List - Students*.csv" "

    This works for me, the actual csv report exports from the system with a random lettering system after students and this import method picks it up without having to enter the full line into the script. If you have multiples of the same csv with different random numbers i believe it picks up the first one however i havent tested that properly yet.

    This is the first time for me getting others to look at my scripts so i appreciate it and love the pointers.

    Thanks

    Pete

    Thursday, December 4, 2014 9:35 PM
  • It will pick up multiples and throw an error. I have tested this many times before posting that.  If ther is only one matching file it works.


    ¯\_(ツ)_/¯

    Thursday, December 4, 2014 10:27 PM