Add-OdbcDsn

I wrote a few odbc articles using ODBCCONF in my blog, so I edit them because ODBCCONF will be removed; read https://docs.microsoft.com/en-us/sql/odbc/odbcconf-exe Using Powershell Add-OdbcDsn is much easier PS> remove-OdbcDsn -name DB01 -dsntype User PS> Add-OdbcDsn -name DB01 -DriverName   "Oracle in client12201" -DsnType "User"   -SetPropertyValue @("Server=DB01") PS> Get-OdbcDsn Name       : DB01 DsnType    : User Platform   : […]

powershell odbc sybase

Oracle goes there ==> …/oracle-odbc-hello-world-with-powershell.html To test Sybase ODBC driver with Powershell, it’s not much different configure the DsN with odbcconf or %SYSTEMROOT%\SysWOW64\odbcconf for the 32bits drivers under Windows 64. cmd /c "%SYSTEMROOT%\SysWOW64\odbcconf.exe /a {configdsn ""Adaptive Server Enterprise"" ""DSN=helloworld|database=mydb|port=25000|server=srv01""}" The name of the driver is important. It is probably also called “Adaptive Server Enterprise” on […]

Open last one-note page

If you got a one-note document, you may want to automatically go to the last page. This is possible with powershell. First you create a ComObject. There are incredibly many ComObject that could be manipulated in powershell. $o = New-Object -ComObject OneNote.Application Now it get’s a bit confusing. First you open your document [ref]$x = […]

How to convert Excel file to csv

#excel to #csv in #powershell (New-Object -ComObject Excel.Application).Workbooks.Open("c:x.xlsx").SaveAs("c:x.csv",6) — laurentsch (@laurentsch) February 13, 2015 One-liner to convert Excel to CSV (or to and from any other format). There is a bug 320369 if you have excel in English and your locale is not America. Just change your settings to us_en before conversion.

xml to csv in powershell

Powershell is very strong with XML, to convert an XML document to something flat like a CSV file, it is incredibly powerfull. Let’s take a file called emp.xml <EMPTABLE>   <DEPT>     <DEPTNO>10</DEPTNO>                 <EMPLIST>       <ENAME>CLARK</ENAME>       <ENAME>MILLER</ENAME>       <ENAME>KING</ENAME>     </EMPLIST>   </DEPT>   <DEPT>     <DEPTNO>20</DEPTNO>     <EMPLIST>        <ENAME>SMITH</ENAME>        <ENAME>FORD</ENAME>        <ENAME>ADAMS</ENAME>        <ENAME>SCOTT</ENAME>        <ENAME>JONES</ENAME>     </EMPLIST>   </DEPT>   <DEPT>     <DEPTNO>30</DEPTNO>         <EMPLIST> […]

dotNet transaction guard

also with ODP in 12c, you can check the commit outcome as in jdbc let’s create a table with a deferred primary key create table t (x number primary key deferrable initially deferred); Here an interactive Powershell Demo PS> [Reflection.Assembly]::LoadFile("C:\oracle\product\12.1.0\dbhome_1\ODP.NET\bin\4\Oracle.DataAccess.dll") GAC    Version        Location —    ——-        ——– True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_64\Oracle.DataAccess\v4.0_4.121.1.0__89b483f429c47342\Oracle.DataAccess.dll I first load the assembly. Some of my frequent readers […]

powershell goodies for Active Directory

What are my groups? PS> Get-ADPrincipalGroupMembership lsc |       select -ExpandProperty "name" Domain Users oracle sybase Who is member of that group ? PS> Get-ADGroupMember oracle|       select -ExpandProperty "name" Laurent Schneider Alfred E. Newmann Scott Tiger What is my phone number ? PS> (get-aduser lsc -property MobilePhone).MobilePhone +41 792134020 This works like a charm on your […]

unreadable output file in powershell

If you redirect a not-completly-string output (like a spfile) to a file in powershell, you may not see the same in the file as in the output without redirection PS C:\> Select-String "compatible" .\spfileDB01.ora spfileDB01.ora:13:*.compatible='11.2.0.4.0' with redirection PS> Select-String "compatible" .\spfileDB01.ora > compatible.txt PS> vim -b .\compatible.txt ÿþ^M^@ ^@s^@p^@f^@i^@l^@e^@D^@B^@0^@0^@1^@.^@o^@r^@a^@:^@1^@3^@:^@*^@.^@c^@o^@m^@p^@a^@t^@i^@b^@l^@e^@=^@'^@1^@1^@.^@2^@.^@0^@.^@4^@.^@0^@'^@^M^@ ^@ With redirection and conversion to […]

[Windows] Email of current user

I hate having to type my email address, so I created a long one-liner to do the trick of getting my email from Exchange and copying it in my clipboard powershell -noprofile -command "$o=New-Object DirectoryServices.DirectorySearcher; $o.SearchRoot=New-Object DirectoryServices.DirectoryEntry;$o.Filter='samaccountname='+$ENV:USERNAME;write-host ($o.FindOne().Properties.mail)" | clip save this as “C:\WINDOWS\E.BAT” Then, when you have to enter your email in a form […]

weekinmonth in powershell starts on Sunday…

This April month is full of surprises! Not only I did not have the opportunity to trick my colleagues on Sunday April first, but I had some bug of my code due to the start of the week. [int](get-date).dayofweek and (Get-WmiObject Win32_LocalTime).dayofweek both refer Sunday as day 0. PS> get-date Wednesday, 11. April 2012 18:31:52 […]

to ADO or to ODP ?

I just read on System.Data.OracleClient Namespace that System.Data.OracleClient are deprecated. The types in System.Data.OracleClient are deprecated. The types are supported in version 4 of the .NET Framework but will be removed in a future release. Microsoft recommends that you use a third-party Oracle provider. The alternative is to use ODP (from Oracle) instead of ADO […]

on Windows Management Instrumentation

I have a bit versed into powershell over the last months, I just cannot stop discovering new gems. Ex : Win32 Classes Self-explanatory examples : PS> (get-wmiobject win32_processor).currentclockspeed 3292 PS> (get-wmiobject win32_operatingsystem).version 5.1.2600 PS> (get-wmiobject win32_proxy).ProxyServer proxy.example.com PS> (get-wmiobject win32_proxy).ProxyPortNumber 8080 it goes on and on and on… the ways those things are accessible from […]

change system wide PATH variable in Windows

This is utterly simple and mostly you did it with the mouse. To do it with command line and powershell, proceed this way. Open a powershell window powershell or if you have no right to edit the machine-wide environment, runas admin runas /user:yourdomain\youruserplusadmin powershell then access the environment assembly [environment]::setEnvironmentVariable("PATH","c:\oracle\product\11.2.0\client_1\bin;C:\WINDOWS\;C:\WINDOWS\system32;C:\Program Files\Putty;C:\Program Files\Perforce;C:\Program Files\TortoiseSVN\bin;C:\WINDOWS\system32\WindowsPowerShell\v1.0","MACHINE") MACHINE is […]

xml and powershell : using XPATH

I wrote about powershell [xml] yesterady : xml and powershell Let’s see how to use XPATH expressions in Powershell <emplist>   <emp no="1">     <ename>John</ename>   </emp>   <emp no="2">     <ename>Jack</ename>   </emp> </emplist> With the [xml] datatype, we create a navigator : (([xml](GC foo.xml)).psbase.createnavigator().evaluate( '//emplist/emp[@no="1"]/ename/text()' ))|%{$_.Value} John I have not been seduced by a Microsoft product since ages, but […]

xml and powershell

I wrote about the unix command-line utility xmllint there : extract xml from the command line Let’s do the same exercice in Powershell PS> gc foo.xml <emplist>   <emp no="1">     <ename>John</ename>   </emp>   <emp no="2">     <ename>Jack</ename>   </emp> </emplist> Simply use [xml] datatype ! (([xml](GC foo.xml)).emplist.emp|Where{$_.no-eq"1"}).ename John Powershell rules!