import csv and parse

PowerShell Help
1

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

 

 

2

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

3

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 GitHub - vexx32/PSKoans: A simple, fun, and interactive way to learn the PowerShell language through Pester unit testing. :slight_smile:

4

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

 

5

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

6

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

 

7

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

[pre]
Clear-Host
Import-CSV -Path “C:\Temp\Test.csv” -Delimiter “;” | select TimeStr,Value | export-csv -path C:\Temp\Test_Out.csv –NoTypeInformation -delimiter ‘,’
[/pre]

8

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

9 
[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
10

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?

11

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