locked
validate date before import RRS feed

  • Question

  •  

      

     I'm trying to make sure dates are valid before a load process, but seems like it's kicking out all of the records
    except my exclude validation in the IF. The dates in the flat file look like this "10/23/2019" and fields in the file
    are separated by the bar character(|). The date is wrapped in a double quote, and not sure how to parse.

     Looking to get a count and data record of the records in file that may be a problem loading.

    $badLines = 0
    Get-Content c:\inventorymrg.csv | ForEach-Object   {
        $currentLine = $_
        $cols = $_.Split('|')
    
     
        Try 
        {
    
        If($cols[9] -eq '""'){
            #write-host "Skipping record"
        }
    
        
         Else
          {   
    
           $cols[9]
        
            # Note columns are 0 based
            [DateTime]:: ParseExact($cols[9], 'mm/dd/yyyy', $null) | Out-Null
           }
         
         }
         Catch {
                 Write-Output $currentLine
                 $badLines ++
               }
           
        
       
      }
    if ($badLines -gt 0)
    {
        throw "$($badLines) lines contained invalid dates"
    }

     Thanks.

     

    Wednesday, October 23, 2019 6:19 PM

All replies

  • Does your CSV file have a header row? If so, why not just use "Import-CSV c:\inventorymrg.csv -Delimiter '|'"?

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Wednesday, October 23, 2019 6:54 PM

  •  No header row in file.

     Thanks.
    Wednesday, October 23, 2019 10:10 PM
  •  No header row in file.
    You can add headers with the paramter -Header of the cmdlet Import-Csv. Please read the complete help including the examples to learn how to use it.

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Wednesday, October 23, 2019 10:49 PM
  • No header row? No problem.

    # create header data
    # columns named 1,2,3...10
    $NumCol = 10
    $h = @()
    1..$NumCol | foreach {$h += $_}
    
    $badLines = 0
    import-csv C:\junk\x.csv -Header $h -Delimiter "|" |
        foreach {
            if ( ($_.9).trim().Length -ge 1){
                try{
                    [DateTime]:: ParseExact($_.9, 'mm/dd/yyyy', $null) | Out-Null
                }
                catch{
                    Write-Output $currentLine
                    $badLines ++
                }
            }
        }


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)



    Thursday, October 24, 2019 2:16 AM
  • Hi,

    Thanks for your question.

    Sorry I can't understand the use of "$cols[9]", can you help me explain it?

    Also I think it is help to solve your problem quickly if you can provide your csv example.

    Best regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, October 24, 2019 7:15 AM
  • No header row? No problem.

    # create header data
    # columns named 1,2,3...10
    $NumCol = 10
    $h = @()
    1..$NumCol | foreach {$h += $_}
    
    $badLines = 0
    import-csv C:\junk\x.csv -Header $h -Delimiter "|" |
        foreach {
            if ( ($_.9).trim().Length -ge 1){
                try{
                    [DateTime]:: ParseExact($_.9), 'mm/dd/yyyy', $null | Out-Null
                }
                catch{
                    Write-Output $currentLine
                    $badLines ++
                }
            }
        }


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


     [DateTime]:: ParseExact($_.9), 'mm/dd/yyyy', $null | Out-Null

    It would be better to replace the ")" to the back of the $null variable.

    Best regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Thursday, October 24, 2019 7:21 AM
  •  There was a date in the file 09/30/0820, and I was thinking it should have detected as not a
    valid date.

      Sorry I can't understand the use of "$cols[9]", can you help me explain it?

      This was just to make sure I was getting the correct data into the process.


     Many Thanks...

    Thursday, October 24, 2019 12:22 PM
  • You're correct. I fixed the code I submitted. That line should have been:

    [DateTime]:: ParseExact($_.9, 'mm/dd/yyyy', $null) | Out-Null


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Thursday, October 24, 2019 2:32 PM
  • The date "09/30/0820" isn't invalid. It may be out of the acceptable range of dates you were expecting, but you haven't coded anything for any such checking.

    I didn't use any subscripting in my example. Where did you see "$cols[9]"? What I did use was "$_.9", which is "the current object" (i.e., "$_") and its property named "9" (i.e., the name in the header for the ninth column).

    If you want to validate ranges you could try something like this:

    # create header data
    # columns named 1,2,3...10
    $NumCol = 10
    $h = @()
    1..$NumCol | foreach {$h += $_}
    
    $EarliestDate = get-date "01/01/2000"
    $LatestDate = get-date
    
    $badLines = 0
    import-csv C:\junk\x.csv -Header $h -Delimiter "|" |
        foreach {
            if ( ($_.9).trim().Length -ge 1){
                try{
                   $x= [DateTime]:: ParseExact($_.9, 'mm/dd/yyyy', $null)
                   if ($x -lt $EarliestDate -or $x -gt $LatestDate)
                   {
                       Throw "Date out of acceptabe range"
                   }
                }
                catch{
                    Write-Output $currentLine
                    $badLines ++
                }
            }
        }


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Thursday, October 24, 2019 2:53 PM
  •  There was a date in the file 09/30/0820, and I was thinking it should have detected as not a
    valid date.

      Sorry I can't understand the use of "$cols[9]", can you help me explain it?

      This was just to make sure I was getting the correct data into the process.


     Many Thanks...

    In Windows the first valid date is 1/1/1 or "Monday, January 1, 0001 12:00:00 AM"

    If you want to set valid dates to a later year then just do this:

    if(([datetime]'09/30/0820').Year -ge 1900){ #code}

    Or any other earliest year you need to guard for,


    \_(ツ)_/

    Thursday, October 24, 2019 4:18 PM
  •  I don't know the date ranges, but thought this date would be flagged as not invalid.

     09/30/0820

     Thanks,

    Sunday, October 27, 2019 10:24 PM
  • The earliest date can be discovered like this:

    [datetime]0

    or like this:
    [datetime]::MinValue
    [datetime]::MaxValue


    \_(ツ)_/

    Sunday, October 27, 2019 10:28 PM
  • Well, we don't know what the range of dates in your dates might be, either. In fact, we have no idea what type of data you're dealing with. Is it employee birthdays? Employee hire dates? Equipment purchases?

    Surely you can take an estimated guess, though. You seem sure that the year 820 is out-of-range on the low side, so why not start with that? Or, say, the year 1095, 1492, 1582, 1914, 1945, 2000, or some other reasonable year.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Monday, October 28, 2019 2:38 AM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, November 8, 2019 1:20 PM