I am using a bat file to open a daily-generated Excel file named 'YYYYMMDD_foo.csv' using batch - this works fine, but I am also trying to group sort the Excel file by one of it's columns - say Column C (Dept.).
ID, Name, Dept.
001, Greg, Maths
002, Sue, Operations
003, Bob, Academics
004, Joan, Operations
How could I accomplish this sort in batch, so that the file is opened, and then sorted as such?
ID, Name, Dept.
003, Bob, Academics
001, Greg, Maths
002, Sue, Operations
004, Joan, Operations
Thank you kindly
EDIT: I have attempted to find a solution with Powershell and with Batch - I will post my scripts below and hopefully with will reopen my question.
Powershell:
$FilePath = "C:\exceltest\20191203Records.csv"
$rawDate = Get-Date
$year = $rawDate.toString('yyyy')
$month = $rawDate.toString('mm')
$day = $rawDate.toString('dd')
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
$objExcel.DisplayAlerts = $true
$WorkBook = $objExcel.Workbooks.Open($FilePath)
$WorkBook | Sort-Object Program
$WorkBook.Save()
Start-Sleep -s 30
Batch:
@echo off
SET dY=%DATE:~0,4%
SET dM=%DATE:~5,2%
SET dD=%DATE:~8,2%
SET searchString=%dY%%dM%%dD%Records.csv
SET thePath=C:\exceltest\
echo %thePath%%searchString%
start Excel.exe %thePath%%searchString%
PAUSE
There is no need to use Excel to sort the List. This can be done directly with Powershell. Below example reads the file, sort it by column "Dept." and writes it into an new CSV-File that can be opened in Excel directly.
#Make sure to use MM for Month, mm is Minutes
$dateString = (Get-Date).ToString('yyyyMMdd')
$filepath= "C:\temp\" + $dateString + "_foo.csv"
$list = Import-Csv -LiteralPath $filepath -Delimiter ","
$outPutFilePath = "C:\temp\"+$dateString+"_sorted.csv"
#sort the List and Export it into CSV-File. Use ; so excel will directly open it correctly
$list | Sort-Object -Property "Dept." | Export-Csv -LiteralPath $outPutFilePath -Delimiter ";" -NoTypeInformation