Eliminating manual steps in Excel to format Date value

[andrewbeno]

I just wanted to start off by saying I love this project and have found it incredibly useful at my job to automate manual/tedious processes that I discovered my coworkers were doing on a monthly basis - we collectively say 'thank you' to all those involved!

The only feedback that they've given me which I can't seem to resolve is detailed below. I didn't see any discussions which specifically talked about this so I figured I'd post here.

Background: I'm taking the output of a SQL query which has multiple datasets and importing it into Excel. A portion of the script is below. I'm somewhat of a novice with PowerShell, so I'm sure there's a more elegant way of doing this - I'm open to any feedback there as well!

`#Query SQL
$result = Invoke-Sqlcmd -ServerInstance "NameOfSQLInstance" -Database "NameOfSQLInstance" -Query $query -Username $Cred.Username -Password $pass -OutputSqlErrors $False -OutputAs dataset

    # Export each dataset to its own Excel file
	for($i=0; $i -lt $result.Tables.Count; $i++){
		switch ($i) {
		0 {$result.Tables[$i] | Export-Excel -Path $ExcelFilePath -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors  -WorksheetName $ExportDescriptionArray[$i] -FreezeTopRow -AutoFilter -AutoSize }
		1 {$result.Tables[$i] | Export-Excel -Path $ExcelFilePath -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors -NoNumberConversion Username -WorksheetName $ExportDescriptionArray[$i] -FreezeTopRow -AutoFilter -AutoSize -AutoNameRange}
		2 {$result.Tables[$i] | Export-Excel -Path $ExcelFilePath -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors  -WorksheetName $ExportDescriptionArray[$i] -FreezeTopRow -AutoFilter -AutoSize}
		3 {$file = $result.Tables[$i] | Export-Excel -Path $ExcelFilePath -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors -NoNumberConversion Username -WorksheetName $ExportDescriptionArray[$i] -FreezeTopRow -AutoFilter -AutoSize -PassThru}
		}	
	}

    $ws = $file.Workbook.Worksheets["Perpetual Users"]
    Set-ExcelRange -Range $ws.Names["DateAdded"] -NumberFormat 'Short Date'
    Set-ExcelRange -Range $ws.Names["DateUpdated"] -NumberFormat 'Short Date'
    
    Close-ExcelPackage $file -Show`

Initially I wasn't using the 'Set-ExcelRange' commands, and the records for columns 'DateAdded' and 'DateUpdated' were showing up with a 'General' format by default, and I couldn't filter on them properly without doing additional manual steps (essentially using 'Text to Columns' to change to a date).

I thought the easy solution would be to set them to a date format as part of the script, so I added the 'Set-ExcelRange' lines. That does set the formatting of the columns/ranges as expected, but for some reason Excel doesn't recognize it as a date field (or that's how I interpret it) until I click into the cell and then out of it. My workaround for now is to highlight each date column individually and then use 'Text to Columns' to set each column as a date value, and then it displays properly and can be sorted properly.

Here's a visual example of what I'm talking about in case I didn't explain that well enough. Has anyone come across this and/or know of a workaround that doesn't require the manual steps? It's trivial in the end but I wanted to make this a fully automated process, so it's nagging me a bit. Thanks for the help!

[ninmonkey]

The first thing I notice is your table isn't a table in excel. when your cursor is placed within the range -- you should see the **green table ** in the toolbar. ( Or open name manager )

Before any other changes, you can

try your code but add table name parameters

$data | Export-Excel -Path $ExcelFilePath -Table FirstTable ...  
$data | Export-Excel -Path $ExcelFilePath -Table SecondTable ...

Background: I'm taking the output of a SQL query which has multiple datasets and importing it into Excel.

I'm not clear on the what the switch is for, are you wanting to export to different worksheet tabs? or multiple, separate tables on one page? Like are you appending ( SQL Union ) multiple tables with matching columns ? Or do they differ?

Do you have sample data? I had none so this example was created by two different gci queries

I use this pattern as a template for multiple tables

note If you structure it this way, using -passThru and ExcelPackage, it's supposed to only write the file once. This can make a performance difference. Without a -PassThru, I believe it re-writes the file every time.
-passthru should write once when you call Close-ExcelPackage

I use a "random" filename so that I can run multiple times, and not have to worry about write errors, or files are open, errors.
( And, I can see leave the last 3 runs open at the same time for comparison )

$ExcelPath = Join-path 'g:\temp' (New-SafeTimeString -FilenameTemplate 'export-{0}.xlsx')
Remove-Item -ea ignore $ExcelPath
$Pkg = Open-ExcelPackage -Path $ExcelPath -Create

$shareSplat = @ {
    'TableStyle' = 'Light2'
    AutoSize = $true
    PassThru = $True
}

# example using Ps5 syntax
$Pkg = @( gci ~ -file ) |
    Select-Object Name, Extension, Length, Parent, FullName, LastWriteTime, CreationTime |
    Export-Excel @shareSplat -ExcelPackage $Pkg -table 'AppData' -WorksheetName 'AppDataSheet'

$pkg = @( gci $Env:LOCALAPPDATA ) |
    Select-Object Name, Length, FullName, LastWriteTime, CreationTime |
    Export-Excel @shareSplat -ExcelPackage $Pkg -table 'LocalAppData' -WorksheetName 'LocalAppDataSheet'

'wrote file: {0}' -f @( $Pkg.File )
Close-ExcelPackage $Pkg -Show

full file: Template/Getting Started With ImportExcel.ps1

Using hashtables to simplify duplicate arguments

Splatting is super useful for making long lines readable. I took your code, then converted some of the calls to use the hashtable named $SharedFreezeSplat It's a regular hashtable. Keys are the function paramater names, like -AutoSize or ExcludeProperty

to do the actual splatting part, you call a function, but you use a @ instead of $.

Export-Excel @SomeArgs -MoreStuff

$ExcludeProperties = @( 'ItemArray', 'RowError', 'RowState', 'Table', 'HasErrors'  )
$SharedFreezeSplat = @{
    AutoNameRange   = $true
    AutoSize        = $true
    AutoFilter      = $true    
    # FreezeLeftColumn = $true 
    FreezeTopRow    = $true
    ExcludeProperty = $ExcludeProperties
    Path            = $ExcelFilePath    
}

for ($i = 0; $i -lt $result.Tables.Count; $i++) {
    switch ($i) {
        0 { $result.Tables[$i] |
            Export-Excel @sharedFreezeSplat -WorksheetName $ExportDescriptionArray[$i] }		
        1 { $result.Tables[$i] |
            Export-Excel @sharedFreezeSplat -NoNumberConversion Username -AutoNameRange }
        
        2 { $result.Tables[$i] |
            Export-Excel @sharedFreezeSplat }
        
        3 { $file = $result.Tables[$i] |
            Export-Excel @sharedFreezeSplat -NoNumberConversion Username  -PassThru }
    }	
}

[dfinke]

I'd need a sample repro for this. Data, script, xlsx.

Off the top of my head, is the date coming in as text?

[andrewbeno]

Thanks @ninmonkey / @dfinke for your replies and sorry for my delay in getting back to you.

try your code but add table name parameters

I updated my script and added the TableName parameter, and while it did format the output as a table on each Excel tab, it didn't resolve the minor issue I described.

I'm not clear on the what the switch is for, are you wanting to export to different worksheet tabs?

Yep that's correct - my coworkers were running a stored proc in SQL which spits out 4 tables of output. They were copying each table and pasting it on a separate tab in Excel each month. I used the switch to be able to output to multiple tabs and also because I had to use the -NoNumberConversion parameter on a particular column within 2 of the 4 tabs (due some of the data having leading zeroes).

Do you have sample data?
I'd need a sample repro for this. Data, script, xlsx.

I've attached a Sample_Data.xlsx file created by my updated version of the script (in the zip) with some of your suggestions re: hashtables. I watched all Doug's YouTube videos where it talked about them and I still didn't implement that, so thanks for the nudge! If you needed something different please let me know.

Sample_Data - 2023-06-08.xlsx
Export.zip

Off the top of my head, is the date coming in as text?

It appears it's being returned from SQL in a date format vs. text. I had our DBA send me the code behind the stored proc - this isn't the whole thing obviously, but I just wanted to show how 1 of the 4 datasets that's being shown in the output is defined/populated. I've attached a screenshot of the SQL output as well w/some values blurred out since it has sensitive info.

 DROP TABLE IF EXISTS [#Final_StudySpecificUsers];
    CREATE TABLE [#Final_StudySpecificUsers]
    (
        [Username] VARCHAR(100),
        [ProtocolNumber] VARCHAR(100),
        [MonthAdded] DATE,
        [MonthUpdated] DATE,
        [DateAdded] DATE,
        [DateUpdated] DATE,
        [Role] VARCHAR(500),
        [Status] VARCHAR(100)
    );

	-- Dataset 4
    SELECT [fssu].[Username],
           [fssu].[ProtocolNumber],
           FORMAT([fssu].[MonthAdded], 'MM/dd/yyyy') AS [MonthAdded],
           FORMAT([fssu].[MonthUpdated], 'MM/dd/yyyy') AS [MonthUpdated],
           FORMAT([fssu].[DateAdded], 'MM/dd/yyyy') AS [DateAdded],
           FORMAT([fssu].[DateUpdated], 'MM/dd/yyyy') AS [DateUpdated],
           [fssu].[Role],
           [fssu].[Status]
    FROM [#Final_StudySpecificUsers] AS [fssu]
    WHERE [fssu].[Status] = 'Active';

I use this pattern as a template for multiple tables

I'll take a closer look at this approach this week, just haven't had time yet - but thanks for sharing!

[andrewbeno]

@dfinke 's assumption about the date coming in as text was correct and this is a non-issue.

I looked up the 'FORMAT' function being used in the SQL select statement shown in my previous comment and it explains why Excel is interpreting the dates as text vs. a date value by default.

The https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver16 page shows that when you use that function, the value being supplied will be formatted as nvarchar / a string of text:

Anyway thanks for your time and input - I'll go back to our DBAs to get their thoughts on using something else besides the 'FORMAT' function.

[dfinke]

Thanks for the update!