Archive

Posts Tagged ‘Windows PowerShell’

Rename multiple FILES using POWERSHELL

November 30, 2013 Leave a comment

Requirement ::

I needed to upload my Educational certificates onto a particular websites ..

after i was done with scanning my docs .. i never realised that the sites wont accept PDF or any other fromat Documents

which had special Characters in the name Exxmple –> ( ,&,*,@,$ ETC …

in my case the Documents were named Document(1).PDF 

PS_rename

so i needed to get there name changed to just Document1.PDF 

aligned snapshot is the what my docs looked

POWERSHELL SCRIPT ::

to change the names of the PDF files .. used POWERGUI and used below command to set my location to the path which

contained the PDF files

SL stands for SET LOCATION and is equvalent to CD

sl ‘D:\GS\DOCS\TEST_scancopy’

then i used below command to get a count of files that had round brackets [ ( )  ] in their names

PS_rename._2JPGget-childitem -Filter ‘*(*’ | measure

this gave me idea of how many docs need to renamed …

i used a while loop to run for 29 times … to ensure all the PDF files

having *(* present in their name are set to required name ..

The rename-item was used to set the names of PDF files .

below is PS Script i used to rename the docs … as i have around 29 docs .. i have used 30 as the no. of times the loop will run through

___________________SCRIPT  STARTs __________

$int = 1

Write-Host $int

while ($int -ne 30)
{
Write-Host ‘within while loop’
Write-Host $int

$var = ‘Document’+ $int.ToString()+’.PDF’

# the names to which docs will be set

get-childitem -Filter ‘*(*’ | select -first 1 | rename-item -NewName $var

Write-Host ‘renamed to ‘ $var

$int ++

}

___________________SCRIPT  ENDs __________

The SCRIPT ran fine ..

you may use similar approach to tackle .. any other requirement 

specific to your need  ENJOY 🙂 

PS_rename._4

 

PS_rename._3

Advertisements

using powershell to select from Tables in database

July 28, 2013 Leave a comment

I was looking for some ways to connect to connect to SQL instance .. Sarabpreet had covered quite interesting ways to do the same … you may check his article over here (a big Thanks to Sarab for sharing the same …)

below is what I tried on windows 7 (SQL 2008 R2 )

once we are within shell .. shell treats all objects with SQL

as files and folders … like what we have while we are working with CMD

we can use different ways to connect to sql server via PowerShell tools ..PS1

1> type powershell.exe   in the start –> run prompt and hit enter

this will keep you PowerShell prompt

2> type SQLPS.EXE   in the start –> run prompt and hit enter

3> or right click on any tab in the SSMS and click on Start PowerShell

depending on were you have right clicked .. you be taken to appropriate

depth or hierarchy with in the SQL instance

PS1_1

for this example we will use step 3 …

refer the snapshot on the right …

if you right click on any of the object within the SQL instance .. we will

taken into the corresponding hierarchy level within the SQL

once we are within the database .. we will use command DIR .. to look for

list of folder and files within (comparing the database structure to OS file system )

Note :: after a certain level command will not return any results or output …

below is an example of a command ran at database level .. we can all objects that are available in SSMS for  a database

connect_2

we will need to use a SQLPS command named INVOKE-SQLCMD to get any meaning full data out of the SQL server ..

once OS based commands like dir (which will work same as LS and get-childitem) hits its limits ..

1> get-help invoke-sqlcmd -FULL

2> get-help invoke-sqlcmd -EXAMPLE

3> get-help invoke-sqlcmd

connect_7

invoke-SQLCMD has multiple parameters which help use to define query and connection options

so once we are within correct location (use cd or cd .. to move from one path to another .. )

we will select data from a table ..as below  .. command used is as below (we may try it on any table with some data in it)

invoke-sqlcmd “select top 10 * from dbo.T2 where id > 1000”

connect_3

below is an example of using SQLCMD to run another command to populate data ..

so you can run any t-sql command in SQLPS shell.. that use can run in SSMS

connect_4

so what is use of all this .. if we can run these commands in SSMS … well the power of powershell

really comes across when we have list of servers to manange .. and we want to automate some admin

tasks across all of them …. going forward I will share more examples on Powershell …

Enjoy:)

Powershell Crash course part – 1

April 1, 2013 Leave a comment
Windows PowerShell exists?

Windows PowerShell exists? (Photo credit: crazytales562)

Hi All,

in this efforts , I will cover some PS basics to get myself and you started on Powershell..

Powershell helps DBA’s to achieve some complex functionality which are not possible by t-sql and command prompt

PS 1 had around 130 commands ,PS2 and PS3 have far more commands now

InstallingPS ..

PS wont get install on windows 2000 or older version then that ..

Newer OS like windows 7 and higher have Powershell preinstalled in that ..

for windows XP,windows 2003 etc go to http://www.microsoft.com/download to get your setup (check whether you need 32 or 64 bit)

to check whether PS is already available for your OS .. either enter powershell.exe in the START–> run prompt

OR go to add/remove program winmdows componets and add Powershell component ..

customizing the SHELL ..

you can right click on the edge of the prompt to customize the size of the window to suite ur preference …

getting Started :: (ur script may not run for that you have to set your execution policy to > set – executionpolicy unrestricted (refer this link for details PowerShell Basics – Execution Policy and Code Signing Part 1 (pauldotcom.com) ))

all the commands (also called as command-lets or cmdlets) in PS are in the form of VERB-noun form

even if the command returns multiple results the noun is singular ..

for example type get-process on the PS prompt (this will list all the process currently running on system )

PS D:\powershell\myscript> get-process

Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName
——- —— —– —– —– —— — ———–
194 4 3632 652 46 1.67 4140 AcroRd32
349 9 55876 29360 162 109.86 4700 AcroRd32
277 8 5296 712 61 5.59 3452 AdobeARM

below is my default prompt .. that i have set my powrshell to start in ..

PS D:\powershell\myscript> 

you will most probably start in in PS C:\document and setting\yourID >

to read the content of a file example text file use  :: get-content

PS D:powershell\myscript> get-content myfile.txt

all commandlets take parameters to acheive different funalities and parameters are positional … meaning you may skip typing parameter name if you passing the actual value of parameter

PS D:powershell\myscript> get-content myfile.txt

is same as PS D:powershell\myscript> get-content -path myfile.txt

where –path was the parameter

in PS if a articular path has a space in it use double quotes for it

PS D:powershell\myscript> get-content “C:\test files\myfile.txt”

ALIASES :: to make all administrators (*nix,cmd.exe) switch to PS an easier curve .. PS has all *nix working in PS too .. so you already many PS commands without knowing it 🙂

example :: cd ,dir,mkdir,del,ren,remdir will all work in PS without any issues ..

as all of them are aliases to PS under hood commands

PS D:powershell\myscript> get-alias  CD
CommandType Name Definition
———– —- ———-
Alias cd Set-Location

so CD is alias of PS command named SET-LOCATION

to get a list of all alias just type –> get-alias

creating a new alias (it will be available only in your session : to make it persistent you need to save it in ur profile )

PS D:powershell\myscript> new-alias N notepad

PS D:powershell\myscript> N 

above command will open up new notepad window .. you can create aliases of ur preferences ..

Thanks for reading .. will continue in part -2