Override locale settings for date output

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

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?

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!

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?

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.

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

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 <scriptname> <path to excel file> “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