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 = $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.

  • I am not sure I get what you are after here and you input data does not include the header line. So, suggesting, you don't really need that. In mot cases you won't.

    However, this says get every Csv(xls) in the directory...
    Get-ChildItem "C:\temp\IN\" -filter *.csv

    SO, I am not sure why you are doing this...
    Where-Object{!($_.PSIsContainer)}

    If you are trying to get every Csv/Xls in a root folder and all the sub folders, you just do this...

    This says get every Csv(xls) in the directory...
    Get-ChildItem "C:\temp\IN\" -filter *.csv -recurse

    So, are you trying to do all these at one time, or one at a time?

    If you are pulling the file path name or just the basename... you can do this
    (Get-ChildItem "C:\temp\IN\" -filter *.csv -recurse).FullName
    (Get-ChildItem "C:\temp\IN\" -filter *.csv -recurse).BaseName

    Because these are CSv/Xls files, to bring them in as objects to work with, just use the
    Import-Csv cmdlet or ConvertFrom-Csv cmdlet.

    See the PowerShell help files for details.

    Also, a more complete version of the input file would help way more the snippet you have provided. Sanitize it first of course.
  • In reply to postanote:

    multiple csv files. the script works
    EXCEPT where for 2 points
    1) date format - the existing csv file has the date as 'dd/mm/yyyy hh:mm:ss' with one space between date and time but when it comes out of the script there is no space
    2) it appears to merge the data in columns A & B but I thought it would write it back into column A - all I get are 2 blank columns
  • In reply to Chrissy2017:

    That whole delete step is IMHO not really needed. in your code you can just pick the columns as you read it in. You can use several method to do that, but RegEx is your friend.

    For example to match you Col_A if it's always a 8 character number string. You can easily grab that using ... RegEx

    ($Col_Number = ([regex]::Match($MyData,'^([^,]+)')).Value)

    ... for a datetime string you could do ... RegEx

    ($Col_DateTime = ([regex]::Match($MyData,'(\d+)/(\d+)/(\d+) (\d+):(\d+):(?:\d+)')).Value

    Then concatenate using the variables content, then output as you need to.

    Something like the below, just might be a bit easier on you.

    # Get the input data to parse

    ( $columns = Get-Content -Path (Get-ChildItem 'D:\temp' -filter *.csv ).FullName)

    V1.0

    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
    #>

    # Parse the Input

    ForEach ($column in $columns)
    {
    # Match for only the data needed

    $Col_Number = ([regex]::Match($column,'^([^,]+)')).Value
    $Col_DateTime = ([regex]::Match($column,'(\d+)/(\d+)/(\d+) (\d+):(\d+):(?:\d+)')).Value

    # Concat the data extraction

    "$Col_Number $Col_DateTime"
    }

    # results

    V1.0

    10173206 18/09/2017 00:00:00
    10173206 18/09/2017 00:30:00
    10173206 18/09/2017 01:00:00
    10173206 18/09/2017 01:30:00

    Now, I am only grabbing and parsing the number and datetime with no delimiter added, but you can easily add that in.

    If you want to keep your original method, you can always add the space back in with a RegEx replace effort.