CSV ignoring '0'

Tagged: 

This topic contains 7 replies, has 4 voices, and was last updated by Profile photo of Olaf Soyk Olaf Soyk 4 weeks ago.

  • Author
    Posts
  • #57368
    Profile photo of Tony Antony
    Tony Antony
    Participant

    Hello,

    I have a CSV file that contains date 2016-11-10 00:00:00, the problem is that, it's ignoring the 0 on the day (10

    $Date_Effective = ($Import_CSV."Date Effective").Trim(" 00:00:00")

    It's reading 2016-11-10 as 2016-11-1

    It was working fine yesterday since yesterday was 2016-11-09

    Thank you,

    Tony

  • #57370
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    try this:

    $Date_Effective = ($Import_CSV."Date Effective").Trim("00:00:00").Trim()

    ... works for me

  • #57374
    Profile photo of Tony Antony
    Tony Antony
    Participant

    Thank you Olaf Soyk, that worked.

    I know the second Trim() is to trim the 00:00:00, but how does the first Trim() work?

    • #57389
      Profile photo of Olaf Soyk
      Olaf Soyk
      Participant

      Of course Robs recommendation is always the best way. But to answer your question you can do this:

      "'$('2016-11-10 00:00:00'.Trim('00:00:00'))'"

      The Result will be '2016-11-10 '. There's still a trailing space. That's what the second Trim() removes:

      "'$('2016-11-10 '.Trim())'"

      The result will now be '2016-11-10'

  • #57377
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    I would highly recommend if you are working with Dates to work with them as a date versus a string. Rather than trying to manipulate the string with Trims or replaces, convert it to a date and then you can format it however you want:

    $date_as_string = "2016-11-10 00:00:00"
    Get-Date $date_as_string -Format d
    #or
    Get-Date $date_as_string -Format "yyyy/MM/dd"
    

    Output:

    11/10/2016
    2016/11/10
    
  • #57379
    Profile photo of Tony Antony
    Tony Antony
    Participant

    Thank you Rob

  • #57394
    Profile photo of Tim Curwick
    Tim Curwick
    Participant

    Tony,

    I often wish .Trim() worked like that, but it doesn't.

    $DateString.Trim( ' 00:00:00' ) is going to trim all spaces, colons and zeros that appear at the beginning and end of the string in any order.

    The are many ways you could do what you want; I would probably do this:

    $DateString.Split( ' ' )[0]

    • #57395
      Profile photo of Olaf Soyk
      Olaf Soyk
      Participant

      Tim,

      wow ... cool ... thanks ... now I really understand how the trim() operator works.

      So if we would like to accomplish the task with the same approach we could even make it a little shorter:

      "'$('2016-11-10 00:00:00'.Trim('0:').trim())'"

You must be logged in to reply to this topic.