import csv and parse

Welcome Forums General PowerShell Q&A import csv and parse

Viewing 10 reply threads
  • Author
    Posts
    • #188983
      Participant
      Topics: 1
      Replies: 4
      Points: 25
      Rank: Member

      Hi

      Im new at powershell and would like to import and parse the following csv file

      INPUT CSV

      "TimeInt";"TimeStr";"IsInitValue";"Value";"IQuality"
      123456;"06/11/2019 07:55:20";AAAA;BBBB

       

      OUTPUT CSV

      06/11/2019 07:55:20,AAAA

       

      How do I go about this?

      Thanks

       

       

    • #189001
      Participant
      Topics: 1
      Replies: 1632
      Points: 3,074
      Helping Hand
      Rank: Community Hero

      What have you tried so far? You might start with learning the very basics of Powershell.

      And please – format your code as code using the code tag button (pre). Thanks

    • #189016
      Participant
      Topics: 2
      Replies: 510
      Points: 1,301
      Helping Hand
      Rank: Community Hero

      I'd recommend starting (for the current problem) with Get-Help Import-Csv

      For a general path to mastering PowerShell I'd recommend starting with https://aka.ms/pskoans 🙂

    • #189850
      Participant
      Topics: 1
      Replies: 4
      Points: 25
      Rank: Member

      I am parsing the data as required (displayed in the console) with the following, but cant seem to get the data to export

      Clear-Host

      Import-CSV -Path "C:\Temp\Test.csv" -Delimiter ";" | select TimeStr,Value

      This export -csv is not working – any suggestions?

      Export-CSV -Path C:\Temp\Test_Out.csv –NoTypeInformation

       

    • #189856
      Participant
      Topics: 8
      Replies: 1274
      Points: 1,033
      Helping Hand
      Rank: Community Hero

      Does it work as expected? Are you meeting the requirements?

    • #189877
      Participant
      Topics: 1
      Replies: 4
      Points: 25
      Rank: Member

      I have finally got PS working... This is what I came up with.  The question is, is the best way of achieving the parsing?

      Thanks for your comments

       

      Clear-Host
      Import-CSV -Path "C:\Temp\Test.csv" -Delimiter ";" | select TimeStr,Value |

      export-csv -path C:\Temp\Test_Out.csv –NoTypeInformation
      $file="C:\Temp\Test_Out.csv"
      (GC $file) | % {$_ -replace '"', "}  > $file

       

    • #189895
      Participant
      Topics: 4
      Replies: 94
      Points: 187
      Helping Hand
      Rank: Participant

      Hi there, nice that you found the solution. I don't know why you get ; as delimiter, but you can force any character to be delimiter with -delimiter argument

      Clear-Host
      Import-CSV -Path "C:\Temp\Test.csv" -Delimiter ";" | select TimeStr,Value | export-csv -path C:\Temp\Test_Out.csv –NoTypeInformation -delimiter ','
      
    • #190249
      Participant
      Topics: 1
      Replies: 4
      Points: 25
      Rank: Member

      How can I change the date format of my output csv to have the timestamp in either AM PM?

      eg

      This is what Im currently outputting with my script

      TimeStr,Value
      18/11/2019 12:55:20,725.48

       

      I would like

      TimeStr,Value
      18/11/2019 12:55:20 p.m,725.48

    • #190273
      Participant
      Topics: 6
      Replies: 658
      Points: 47
      Rank: Member
      [datetime]::ParseExact('18/11/2019 21:55:20','dd/MM/yyyy HH:mm:ss',$null).tostring('dd/MM/yyyy hh:mm:ss tt')
      18/11/2019 09:55:20 PM
    • #190450
      Participant
      Topics: 1
      Replies: 4
      Points: 25
      Rank: Member

      ok, turns out my timstamp in the csv file was 24:00 hrs so I solved with the following script.

       

      Clear-Host

      Import-CSV -Path "C:\Temp\* Test.csv" -Delimiter ";" | ForEach-Object {
      $_.TimeStr = (Get-Date $_.TimeStr).ToString("dd-MM-yyyy HH:mm:ss")
      $_
      } | select TimeStr,Value |
      export-csv -path C:\Temp\Test_Out.csv –NoTypeInformation

      $arr = New-Object System.Collections.ArrayList

      $file="C:\Temp\Test_Out.csv"
      (GC $file) | % {$_ -replace '"', "}  > $file

       

      The last task I need to achieve is to process multiple CSV's that are in the same folder (all CSV's are the same format, except the data stamp and values are different)

      What is the best way of achieving this? Any suggestions?

    • #190573
      Participant
      Topics: 5
      Replies: 277
      Points: 170
      Helping Hand
      Rank: Participant

      This will get the path of all *.csv in your folder (and subfolders) then execute Import-Csv cmdlet on all.

      
      (Get-ChildItem -path C:\Temp\ -Recurse -Filter *.csv).FullName | Import-Csv
      
      
Viewing 10 reply threads
  • You must be logged in to reply to this topic.