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