I have Googled and tried out things I have found but all I can produce is two empty columns
and a date without the space.
CSV file has 10 columns. I only need A, I J & K.
So I have started by deleting B to H inclusive. Which gives me A B C & D.
I want to concatenate the values in A & B and format C as "dd/mm/yyyy hh:mm:ss"
which is how it actually arrives in the input file.
Code is
Get-ChildItem "C:tempIN" -filter *.csv | Where-Object{!($_.PSIsContainer)} |
Foreach-Object {
#Setup variables. I think some of these can now be removed.
$path = $_.FullName
$entry = $_.BaseName
$output = $outputdir + $entry + "_out.csv"
$Workbook = $a.workbooks.open($path)
$sheet1 = $Workbook.Worksheets.Item(1)
# Delete columns B to H backward
# concatenate columns A and B (was I) into the first column
# leaving 3 columns in total
$sheet1.range("H:H").delete() > $null
$sheet1.range("G:G").delete() > $null
$sheet1.range("F:F").delete() > $null
$sheet1.range("E:E").delete() > $null
$sheet1.range("D:D").delete() > $null
$sheet1.range("C:C").delete() > $null
$sheet1.range("B:B").delete() > $null
# format column C with date
$daterange = $sheet1.range("C:C")
# $daterange.numberformat = "dd/mm/yyyy hh:mm:ss"
$daterange.numberformat = 'dd/MM/yyyy hh:mm:ss'
# concatenate columns A & B (was I)
$MergeCells = $sheet1.range("A:B")
#$Mergecells.Select()
$MergeCells.MergeCells = $true
# format columns A (& B) as numeric no decimal places
#$sheet1.columns.item(1).numberformat = "0"
#$sheet1.columns.item(2).numberformat = "0"
write-host "Output File: " $output
input is
V1.00
10173206,g,,,,,,,***,18/09/2017 00:00:00,0.000000
10173206,g,,,,,,,***,18/09/2017 00:30:00,0.000000
10173206,g,,,,,,,***,18/09/2017 01:00:00,0.000000
10173206,g,,,,,,,***,18/09/2017 01:30:00,0.000000
resulting output is
V1.00,,,
,,18/09/201700:00:00,0
,,18/09/201700:30:00,0
,,18/09/201701:00:00,0
,,18/09/201701:30:00,0
Where am I going wrong?? I am not very clever yet with Powershell
and I am obviously not asking the correct questions.