MergeCSV and date with space problems

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:\temp\IN\" -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 = $$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.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

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


Where am I going wrong??  I am not very clever yet with Powershell

and I am obviously not asking the correct questions.

Parents Reply Children
No Data