Use ODBC – DSN, to read data from the database with PowerShell

by Oct 6, 2015



I'm trying to write a program in PowerShell that retrieves data from a database (no SQL database).
The only way (for me) to retrieve information from the database, is through ODBC and SQL-queries.

The non SQL database is on the same computer as this PowerShell script . The operating system can be Windows 7 (or newer).

The Windows is 64-bit, but the driver is only 32-bit.

DSNname or ODBC-name = SquidStar, (no username and password.)
TableName = OPERATOR
DriverName (and path) = c:windowssystem32pvxodb32.dll
IniFile = c:WindowsStarODBC.ini

My desire

Have made some attempts to connect to the database, and create a CSV file of the contents, but it failed.

One try is this begin .:

# Don't need User and Password for the database
$connectstring = "DSN=SquidStar"
# Select all fields from the table – maybe later, be able to select specific fields.
$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)

Now I got an Error

Exception calling "Open" with "0" argument(s): "ERROR [IM002] [Microsoft][ODBC Driver Man
ager] Data source not found and no default driver specified"
At line:1 char:1
+ $
+ ~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OdbcException

If I understand the error message correctly, PowerShell is trying to invoke a 64-bit ODBC driver, like the OS, but I only have a 32-bit ODBC -driver.

How to open and read to the database properly?


Then I found the following program sequence to resolve and take care of
the data from the database, but do not really understand what happens,
and how to get an CSV-file.

$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)
$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
$dt = New-Object system.Data.datatable
$null = $da.fill($dt)

Is my wish possible to solve with PowerShell or do I also need a 64-bit ODBC driver?