How to Upgrade to PowerShell 7

https://www.petri.com/how-to-upgrade-to-powershell-7

How to Use Parameters in PowerShell

https://www.red-gate.com/simple-talk/sysadmin/powershell/how-to-use-parameters-in-powershell/

Call SQL Server Stored Procedures with PowerShell using Parameter Objects

https://www.mssqltips.com/sqlservertip/5949/call-sql-server-stored-procedures-with-powershell-using-parameter-objects/

Ordering T-SQL Scripts with Git and PowerShell

https://www.sqlservercentral.com/articles/ordering-t-sql-scripts-with-git-and-powershell

Sequential Execution of SQL Server Stored Procedures

https://www.mssqltips.com/sqlservertip/5957/sequential-execution-of-sql-server-stored-procedures/

Execute SSIS Package in Docker Container from PowerShell

https://www.sqlservercentral.com/blogs/execute-ssis-package-from-powershell

Add a calculated property with Select-Object in PowerShell

https://4sysops.com/archives/add-a-calculated-property-with-select-object-in-powershell

Get List of Files and Insert to SQL Server Table

Get-ChildItem -Path C:\ETL\EXPORT_FILES |

Select-Object Name, FullName, Extension, CreationTime, Length |

Write-SqlTableData -ServerInstance localhost -DatabaseName ETL -SchemaName import -TableName ImportFileList -Force

Initially loclhost was working but then it stopped with failed to connect message; changed to actual SQL Server instance name
and that worked:

Get-ChildItem -Path C:\ETL\EXPORT_FILES |

Select-Object Name, FullName, Extension, CreationTime, Length |

Write-SqlTableData -ServerInstance "DESKTOP-U8PAEJC" -DatabaseName "DEV_STAGING" -SchemaName "import" -TableName "ImportFileList" -Force

Get List of Files in Folder and Pipe to Excel

Get-ChildItem -Path C:\ETL\EXPORT_FILES |

Select-Object Name, FullName, Extension, CreationTime, Length |

Export-Excel -Now

Import Excel to SQL Server Table

Import-Excel -Path C:\ETL\EXCEL-FILES\CONTACT.xlsx |

Write-SqlTableData -ServerInstance localhost -DatabaseName ETL -SchemaName import -TableName Contact -Force

 

 

-Force on Write-SqlTableData will create anything that doesn't exist; e.g. Databasename, SchemaName, TableName

 

 

Column types will be nvarchar(max)

Using Version Control in VS Code

https://code.visualstudio.com/docs/editor/versioncontrol

Use PowerShell 6 in VS Code

VSCode as a console for PowerShell Core 6 - Use Multiple shells

https://4sysops.com/archives/vscode-as-a-console-for-powershell-core-6-part-1-multiple-shells/

PowerShell Invoke-SQLCmd outputs DataTables you can INSERT into SQL Server

https://www.mssqltips.com/sqlservertip/5802/powershell-module-to-read-directory-contents-and-store-in-a-sql-server-table/

Process file paths from the pipeline in PowerShell functions

https://4sysops.com/archives/process-file-paths-from-the-pipeline-in-powershell-functions/

Tips on Implementing Pipeline Support

https://learn-powershell.net/2013/05/07/tips-on-implementing-pipeline-support/

Understanding PowerShell pipeline parameter binding

https://4sysops.com/archives/understanding-powershell-pipeline-parameter-binding/

Understanding the PowerShell $_ and $PSItem pipeline variables

https://4sysops.com/archives/understanding-the-powershell-_-and-psitem-pipeline-variables/

The PowerShell Here-String – Preserve text formatting

https://4sysops.com/archives/the-powershell-here-string-preserve-text-formatting/

PowerShell Tips on MSSQLTips.com

https://www.mssqltips.com/sql-server-tip-category/81/powershell/

PowerShell Module to Read Directory Contents and Store in a SQL Server Table

https://www.mssqltips.com/sqlservertip/5802/powershell-module-to-read-directory-contents-and-store-in-a-sql-server-table/

Getting Started with PowerShell File Properties and Methods

https://www.mssqltips.com/sqlservertip/5878/getting-started-with-powershell-file-properties-and-methods/

Rename and Move a File

https://www.mssqltips.com/sqlservertip/3207/rename-and-move-a-file-with-powershell-in-a-sql-server-agent-job/

Strings in PowerShell

https://mcpmag.com/articles/2015/07/23/strings-in-powershell.aspx

Note "HERE" string

@"
string content goes here; can be multiple line; can include variables; e.g. $varname
"@

Another way of evaluating a string within a string"

$MyName = "State Your Name"

  "Hello $($MyName), today is $((Get-Date).DayOfWeek)"

Download Visual Studio Code

https://code.visualstudio.com/download

How to install Visual Studio Code and configure it as a replacement for the PowerShell ISE

https://www.youtube.com/watch?v=1sD-RktG_dM

Use Visual Studio Code for PowerShell Development

https://code.visualstudio.com/docs/languages/powershell

ImportExcel Module on GITHUB

https://github.com/dfinke/ImportExcel

PowerShell 6

https://www.thomasmaurer.ch/2019/03/how-to-install-and-update-powershell-6/
C:\Program Files\PowerShell\6\pwsh

PowerShell on GITHUB

https://github.com/PowerShell/PowerShell

Searching SQL Servers & Saving Results, with Write-SqlTableData

http://sqlvariant.com/2016/10/quick-blog-searching-sql-servers-saving-results-with-write-sqltabledata/

Get Free Space Per Drive Mount Point

http://sqlvariant.com/2017/05/finding-free-space-per-drive-mount-point-in-powershell/

Export Adventureworks Tables Using Export-Excel

http://sqlvariant.com/2019/03/export-adventureworksdw2017-to-excel-for-a-powerbi-demo-with-export-excel-in-powershell/

Achieve more in less time using the new SQL PowerShell - THR2114

https://www.youtube.com/watch?v=3tVnNm-w4Bg

Where is PowerShell.exe

Open command prompt
where PowerShell

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Query the PowerShell Version You're Running

$PSVersionTable

Name                           Value                                                                                 
----                           -----                                                                                 
PSVersion                      5.1.17134.590                                                                         
PSEdition                      Desktop                                                                               
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                               
BuildVersion                   10.0.17134.590                                                                        
CLRVersion                     4.0.30319.42000                                                                       
WSManStackVersion              3.0                                                                                   
PSRemotingProtocolVersion      2.3                                                                                   
SerializationVersion           1.1.0.1


$PSVersionTable.PSVersion

Major  Minor  Build  Revision
-----  -----  -----  --------
5      1      17134  590 


$PSVersionTable.PSVersion.Major

5

Import Excel How-To Videos

https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5uoqS92stXioZw-u-ze_NtvSo0k0K0kq

Using the ImportExcel PowerShell Module by Doug Finke

https://www.youtube.com/watch?v=4Xw7r6436w0

Windows PowerShell for Developers by Doug Finke

PASS PowerShell on YouTube

https://www.youtube.com/PowerShellVirtualChapter

Using the Import-Excel module: Part 1 Importing

https://jamesone111.wordpress.com/2017/12/05/using-the-import-excel-part-1-importing/

Create Pipe-Delimited File from Fixed-Width File

<# SPECIFY INPUT FILE NAME #>
$file = "FULL_PATH_TO_FIXED_WIDTH_FILE"


<# SPECIFY INPUT FILE NAME #>
$outputFileName = "FULL_PATH_TO_PIPE_DELIMITED_FILE"



$count = 0
$instream = New-Object System.IO.StreamReader($file)
$outstream = New-Object System.IO.StreamWriter($outputFileName, 0)      <# create new file #>

<# write out columns names in the first row #>
$outstream.WriteLine("<< PIPE DELIMITED LIST OF FIELD NAMES FOR 1ST ROW >>")


while (($line = $instream.ReadLine()) -ne $null)         
        {
            $SYS_ID = $line.Substring(30, 1)
            if ($SYS_ID -eq "K" -or $SYS_ID -eq "L") {

                <#  add $line.Substring(OFFSET, LENGTH)  for each field to extract #>
                $out = $line.Substring(0, 44) + "|" + `
$line.Substring(0, 2) + "|" + `
$line.Substring(2, 2) + "|" + `
$line.Substring(4, 2) + "|" + `
$line.Substring(6, 8) + "|" + `
$line.Substring(14, 9) + "|" + `
$line.Substring(23, 1) + "|" + `
$line.Substring(24, 6) + "|" + `
$line.Substring(30, 1) + "|" + `
$line.Substring(31, 9) + "|" + `
$line.Substring(40, 2) + "|" + `
$line.Substring(44, 5) + "|" + `
$line.Substring(89, 8) + "|" + `
$line.Substring(102, 10) + "|" + `
$line.Substring(112, 10) + "|" + `
$line.Substring(122, 10) + "|" + `
$line.Substring(132, 2) + "|" + `
$line.Substring(174, 12) + "|" + `
$line.Substring(195, 4) + "|" + `
$line.Substring(228, 4) + "|" + `
$line.Substring(228, 4) + "|" + `
$line.Substring(296, 10) + "|" + `
$line.Substring(436, 9) + "|" + `
$line.Substring(524, 4) + "|" + `
$line.Substring(528, 8) + "|" + `
$line.Substring(528, 8) + "|" + `
$line.Substring(536, 8) + "|" + `
$line.Substring(544, 8) + "|" + `
$line.Substring(560, 8) + "|" + `
$line.Substring(568, 8) + "|" + `
$line.Substring(585, 69) + "|" + `
$line.Substring(710, 1) + "|" + `
$line.Substring(891, 4) + "|" + `
$line.Substring(953, 8) + "|" + `
$line.Substring(961, 1) + "|" + `
$line.Substring(978, 40) + "|" + `
$line.Substring(1078, 11) + "|" + `
$line.Substring(1089, 11) + "|" + `
$line.Substring(2865, 2) + "|" + `
$line.Substring(2867, 8) + "|" + `
$line.Substring(2875, 8)


$outStream.WriteLine($out)
                $count = $count + 1
            }
        }
$instream.Close();
$outstream.Close();

How to Combine Multiple Text Files into a Single File

<# Combine multiple claims files into a single file #>

$combinedClaimsFiles = "TARGET_PATH_FILENAME_GOES_HERE"

$inputFileList = "INPUT_FILES_GO_HERE_EG_*.TXT"

New-Item -ItemType file $combinedClaimsFiles –force

Get-Content $inputFileList | Add-Content $combinedClaimsFiles

Change Line Endings of Text FIle

https://ss64.com/ps/syntax-set-eol.html

Read a File 1 Line at a Time

$file = "FILE NAME GOES HERE"
$line = Get-Content $file | Select -First 1
$line.Substring(0, 50)
$line.Length


<# Get-Content $file | Select -First 10 | ForEach-Object { $_ } #>

$count = 0
$stream = New-Object System.IO.StreamReader($file)
Write-Output("CL_PA_ID|CL_SYS_ID")
while (($line = $stream.ReadLine()) -ne $null -and $count -lt 100)
        {
            $CL_SYS_ID = $line.Substring(30, 1)
            if ($CL_SYS_ID -eq "K" -or $CL_SYS_ID -eq "L") {
                $out = $line.Substring(14, 9) + "|" + $line.Substring(30, 1)
                Write-Output($out)
                $count = $count + 1
            }
        }
$stream.Close();