Override locale settings for date output

This topic contains 6 replies, has 3 voices, and was last updated by  Scott Bass 3 years, 8 months ago.

  • Author
    Posts
  • #13049

    Scott Bass
    Participant

    Hi,

    I've got the below script that reads an Excel file and spits out a delimited file, in my case a pipe-delimited file:

    < # HEADER
    /*=====================================================================
    Program Name            : Query-Excel.ps1
    Purpose                 : Execute query against Excel worksheet
    Powershell Version:     : v2.0
    Input Data              : N/A
    Output Data             : N/A
    
    Originally Written by   : Scott Bass
    Date                    : 04OCT2013
    Program Version #       : 1.0
    
    =======================================================================
    
    Modification History    :
    
    Programmer              : Scott Bass
    Date                    : 29JAN2014
    Change/reason           : Added delimiter parameter
    Program Version #       : 1.1
    
    =====================================================================*/
    
    /*---------------------------------------------------------------------
    
    THIS SCRIPT MUST RUN UNDER x86 (32-bit) POWERSHELL SINCE WE ARE USING
    32-BIT MICROSOFT OFFICE.  ONLY THE x86 OLEDB PROVIDER IS INSTALLED!!!
    
    The format of the SQL Query MUST be:
    
    select [top x] [column names | *] from [Sheet1$]
    
    Yes, the brackets and trailing $ sign are REQUIRED!
    
    These queries will fail with an error (read the error message!):
    
    select * from [Sheet1]
    "The Microsoft Access database engine could not find the object 'Sheet1'.
    
    select * from Sheet1$
    "Syntax error in FROM clause."
    
    ---------------------------------------------------------------------*/
    #>
    
    < #
    .SYNOPSIS
    Query Excel Worksheet
    
    .DESCRIPTION
    Execute a query against an Excel Worksheet
    
    .PARAMETER  SQLQuery
    SQL Query to execute
    
    .PARAMETER  Path
    Path to Excel Workbook
    
    .PARAMETER  Csv
    Output as CSV?  If no, the Dataset Table object is returned to the pipeline
    
    .PARAMETER  Delimiter
    Override default comma delimiter.  Only used when output as CSV.
    
    .PARAMETER  Whatif
    Echos the SQL query information without actually executing it.
    
    .PARAMETER  Confirm
    Asks for confirmation before actually executing the query.
    
    .PARAMETER  Verbose
    Prints the SQL query to the console window as it executes it.
    
    .EXAMPLE
    .\Query-Excel.ps1 C:\Temp\Temp.xlsx "select top 10 * from [Sheet1$]" -csv
    
    Description
    -----------
    Queries the specified Excel workbook and worksheet with the specified query, outputting data as CSV.
    
    .EXAMPLE
    .\Query-Excel.ps1 C:\Temp\Temp.xlsx "select top 10 * from [Sheet1$]" -csv -delimiter "|"
    
    Description
    -----------
    Queries the specified Excel workbook and worksheet with the specified query, outputting data as a pipe separated file.
    
    .EXAMPLE
    .\Query-Excel.ps1 C:\Temp\Temp.xlsx "select claimnum,covno,suffix from [Sheet1$] where claimnum like '2061301%' order by covno,suffix" -csv
    
    Description
    -----------
    Queries the specified Excel workbook and worksheet with the specified query, outputting data as CSV
    
    .EXAMPLE
    .\Query-Excel.ps1 C:\Temp\Temp.xlsx "select * from Sheet1" -csv:$false
    
    Description
    -----------
    Queries the specified Excel workbook and worksheet with the specified query,
    returning the Object Table to the pipeline
    
    #>
    
    #region Parameters
    [CmdletBinding(SupportsShouldProcess=$true)]
    param(
       [Parameter(
          Position=0,
          Mandatory=$true
       )]
       [String]$Path
       ,
       [Alias("query")]
       [Parameter(
          Position=1,
          Mandatory=$true
       )]
       [String]$SqlQuery="SELECT * FROM Sheet1"
       ,
       [Switch]$csv = $true
       ,
       [Alias("dlm")]
       [String]$delimiter=","
    
    )
    #endregion
    
    $ErrorActionPreference = "Stop"
    
    #$adOpenStatic = 3
    #$adLockOptimistic = 3
    
    $SqlConnection = New-Object System.Data.OleDb.OleDbConnection
    $SqlConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$Path; Extended Properties=""Excel 12.0 Xml; HDR=YES"";"
    $SqlCmd = New-Object System.Data.OleDb.OleDbCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $nRecs = $SqlAdapter.Fill($DataSet)
    $nRecs | Out-Null
    
    # Populate Hash Table
    $objTable = $DataSet.Tables[0]
    
    # Return results to console (pipe console output to Out-File cmdlet to create a file)
    if ($csv) {
       ($objTable | ConvertTo-CSV -delimiter $delimiter -NoTypeInformation) -replace('"','')
    } else {
       $objTable
    }
    
    # Saves to File as CSV
    # ($objTable | Export-CSV -Path $OutputPath -NoTypeInformation)  -replace('"','')
    
    # Saves to File as XML
    # $objTable | Export-Clixml -Path $OutputPath
    

    The pipe delimited output is then read into another program (SAS).

    My problem is, the script runs under different user accounts, with different locale (region settings) which reformat datetime strings which prevent reading in by SAS.

    Is there a way to temporarily override the region settings of the user account running the script such that datetime strings are consistent?

    The format I'd prefer is DD-MMM-YYYY HH:MM:SS (24 hour time). For example: 05-Sep-13 15:09:11

    Thanks,
    Scott

  • #13053

    Richard Siddaway
    Moderator

    This will give you the format you want Get-Date -Format "dd-MMM-yyyy HH:mm:ss"

    £> Get-Date -Format "dd-MMM-yyyy HH:mm:ss"
    12-Feb-2014 14:30:42

    Is you date coming from Excel or direct from PowerShell?

  • #13056

    Scott Bass
    Participant

    The output comes from the script I posted. Date values in Excel are returned as date time strings, such as 31-Jan-2014 15:25:35 or 30/01/2014 03:25:35 PM, depending on the region settings for the end user running the script.

    Date columns could be anywhere within the worksheet being queried.

    Does this answer your question? Thanks for the reply!

  • #13058

    Richard Siddaway
    Moderator

    The problem you'll have is that constructing a date object is dependent on how .NET takes the data.
    Can you force the settings of the date field formats in Excel?

  • #13059

    Scott Bass
    Participant

    I do force the date field settings in Excel, using either built-in date or custom datetime strings. Under the covers, everything is a datetime. Dates are displayed as yyyy-mm-dd, and datetime is displayed as yyyy-mm-dd HH:MM:SS (24 hour clock).

    So, no matter the end user's region setting, the dates and datetimes look the same in Excel. ***BUT, the output CSV file (for dates) is different based on the end user's region settings.***

    I Googled a bit before posting here, and found the Get-Culture cmdlet, but no corresponding Set-Culture cmdlet.

    These commands:

    Get-Culture | gm
    (Get-Culture).DateFormat | gm
    ((get-culture).DateTimeFormat).ShortDatePattern

    are interesting, but I'm not sure if they are readonly or can be set, and if they can be set temporarily only for the life of the script, and only affecting the script. Even if they can be set, if it involves setting the value globally for that user, then resetting it to the original value, that could be dangerous.

  • #13086

    Scott Bass
    Participant

    Hi All,

    Thanks for the replies, much appreciated.

    @dave Wyatt: That post seemed unnecessarily complex (I just use the Get-Culture cmdlet), but gave me some good ideas. I don't know if I need the current threading code???

    @All: What I need goes deeper than just setting a culture. For example, both accounts have the en_AU culture, but my user account has non-standard date/time settings which actually give the desired result, while the batch scheduler account standard settings do not.

    Here is my final script. I don't know why I have to set AM/PMDesignator; setting the ShortTimePattern to "HH:mm" (no tt) should suppress the AM/PM, but it didn't.

    If you want to run it, just create a simple Excel file (I'm using Excel 2007, saving as XLSB), with say "Date" in $A$1, and a few dates in $A$2 onward. Then invoke the script as "select top 5 Date from [Sheet1$]".

    Can you please just have a quick look for general approach and suggest any improvements? Thanks.

    < # HEADER /*===================================================================== Program Name : Query-Excel.ps1 Purpose : Execute query against Excel worksheet Powershell Version: : v2.0 Input Data : N/A Output Data : N/A Originally Written by : Scott Bass Date : 04OCT2013 Program Version # : 1.0 ======================================================================= Modification History : Programmer : Scott Bass Date : 29JAN2014 Change/reason : Added delimiter parameter Program Version # : 1.1 Programmer : Scott Bass Date : 13FEB2014 Change/reason : Added culture (date formatting) code Program Version # : 1.2 =====================================================================*/ /*--------------------------------------------------------------------- THIS SCRIPT MUST RUN UNDER x86 (32-bit) POWERSHELL SINCE WE ARE USING 32-BIT MICROSOFT OFFICE. ONLY THE x86 OLEDB PROVIDER IS INSTALLED!!! The format of the SQL Query MUST be: select [top x] [column names | *] from [Sheet1$] Yes, the brackets and trailing $ sign are REQUIRED! These queries will fail with an error (read the error message!): select * from [Sheet1] "The Microsoft Access database engine could not find the object 'Sheet1'. select * from Sheet1$ "Syntax error in FROM clause." ---------------------------------------------------------------------*/ #>

    < # .SYNOPSIS Query Excel Worksheet .DESCRIPTION Execute a query against an Excel Worksheet .PARAMETER SQLQuery SQL Query to execute .PARAMETER Path Path to Excel Workbook .PARAMETER Csv Output as CSV? If no, the Dataset Table object is returned to the pipeline .PARAMETER Delimiter Override default comma delimiter. Only used when output as CSV. .PARAMETER Whatif Echos the SQL query information without actually executing it. .PARAMETER Confirm Asks for confirmation before actually executing the query. .PARAMETER Verbose Prints the SQL query to the console window as it executes it. .EXAMPLE .\Query-Excel.ps1 C:\Temp\Temp.xlsx "select top 10 * from [Sheet1$]" -csv Description ----------- Queries the specified Excel workbook and worksheet with the specified query, outputting data as CSV. .EXAMPLE .\Query-Excel.ps1 C:\Temp\Temp.xlsx "select top 10 * from [Sheet1$]" -csv -delimiter "|" Description ----------- Queries the specified Excel workbook and worksheet with the specified query, outputting data as a pipe separated file. .EXAMPLE .\Query-Excel.ps1 C:\Temp\Temp.xlsx "select claimnum,covno,suffix from [Sheet1$] where claimnum like '2061301%' order by covno,suffix" -csv Description ----------- Queries the specified Excel workbook and worksheet with the specified query, outputting data as CSV .EXAMPLE .\Query-Excel.ps1 C:\Temp\Temp.xlsx "select * from Sheet1" -csv:$false Description ----------- Queries the specified Excel workbook and worksheet with the specified query, returning the Object Table to the pipeline #>

    #region Parameters
    [CmdletBinding(SupportsShouldProcess=$true)]
    param(
    [Parameter(
    Position=0,
    Mandatory=$true
    )]
    [String]$Path
    ,
    [Alias("query")]
    [Parameter(
    Position=1,
    Mandatory=$true
    )]
    [String]$SqlQuery="SELECT * FROM Sheet1"
    ,
    [Switch]$csv = $true
    ,
    [Alias("dlm")]
    [String]$delimiter=","

    )
    #endregion

    # Error trap
    trap
    {
    # Restore culture
    Set-Culture $oldShortDatePattern $oldShortTimePattern $oldAMDesignator $oldPMDesignator
    }

    Function Set-Culture
    {
    param(
    [string]$ShortDatePattern,
    [string]$ShortTimePattern,
    [string]$AMDesignator,
    [string]$PMDesignator
    )
    # save current settings
    [System.Globalization.DateTimeFormatInfo]$Culture=(Get-Culture).DateTimeFormat
    $script:oldShortDatePattern=$Culture.ShortDatePattern
    $script:oldShortTimePattern=$Culture.ShortTimePattern
    $script:oldAMDesignator=$Culture.AMDesignator
    $script:oldPMDesignator=$Culture.PMDesignator

    # set new settings
    $Culture.ShortDatePattern=$ShortDatePattern
    $Culture.ShortTimePattern=$ShortTimePattern
    $Culture.AMDesignator=$AMDesignator
    $Culture.PMDesignator=$PMDesignator
    }

    # Set Culture
    Set-Culture "dd-MMM-yyyy" "HH:mm" "" ""

    # Run Excel query
    & {
    $ErrorActionPreference = "Stop"

    #$adOpenStatic = 3
    #$adLockOptimistic = 3

    # Run query
    $SqlConnection = New-Object System.Data.OleDb.OleDbConnection
    $SqlConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$Path; Extended Properties=""Excel 12.0 Xml; HDR=YES"";"
    $SqlCmd = New-Object System.Data.OleDb.OleDbCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $nRecs = $SqlAdapter.Fill($DataSet)
    $nRecs | Out-Null

    # Populate Hash Table
    $objTable = $DataSet.Tables[0]

    # Return results to console (pipe console output to Out-File cmdlet to create a file)
    if ($csv) {
    ($objTable | ConvertTo-CSV -delimiter $delimiter -NoTypeInformation) -replace('"','')
    } else {
    $objTable
    }

    # Saves to File as CSV
    # ($objTable | Export-CSV -Path $OutputPath -NoTypeInformation) -replace('"','')

    # Saves to File as XML
    # $objTable | Export-Clixml -Path $OutputPath

    } # end script block

    # Restore culture
    Set-Culture $oldShortDatePattern $oldShortTimePattern $oldAMDesignator $oldPMDesignator

  • #13060

    Dave Wyatt
    Moderator

    This old PowerShell team blog post might be helpful: http://blogs.msdn.com/b/powershell/archive/2006/04/25/583235.aspx .

You must be logged in to reply to this topic.