Add-Type -AssemblyName System.Web
#********************************************************************************************************#2021-10-11#CLIPBOARD SQL TABLE ROWS TO HTML or EXCEL##This guy Reads SQL Row Results Text From Clipboard#The Text can be tab delimited, etc see HOW TO PARSE below##I will generate html table, and or xlsx file from it.
#To past Excel in Outlook#Click On File#Options (Outlook Options Dialog)#Mail#Editor Options#Advanced (on the left)#Uncheck Use Smart cut and Paste (It's really stupid)#********************************************************************************************************
#OUTPUT FOLDER -> Where to write HTML or Excel File. Contents will be copied to clipboard as well for ez pasting.$targetFolder = "D:\_ggoeppel\";
#OUTPUT FORMATS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>[string] $OutputClipboardFormat = "HTMLTEXT";#TEXT (HTML TABLE SOURCE TEXT FOR PASTING IN WIKI)[string] $OutputClipboardFormat = "EXCEL";#Writes an xlsx file and attempts to put xl formatted on clipboard.#[string] $OutputClipboardFormat = "SQLINSERT";#SQL Insert rows on clipboard (Still outputs HTML table file for column verification)[int] $GregsExcelFontSize = 8;#Only Used if OUTPUT is 'EXCEL'
#$SQLInsertRowStr = "Insert Into BTPmtTbl";#If Blank assumes first col is tablename$SQLInsertRowStr = "";$SQLInsertSkipFirstColVal='N'; #This will get set to Y if $SQLInsertRowStr is blank above. In that causes first Col is assumed to be table name.
#HOW TO PARSE >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>#[string] $splitByRegEx = '\s+';#Split by WhiteSpace#[string] $splitByRegEx = '\s{2,}';#Split by at least 2 WhiteSpace[string] $splitByRegEx = '\t';#Split By Tabs works best straight from SSMS, but this is useless for file results from customer.#[string] $splitByRegEx = '';#USER HEADER FIELD AS TOP ROW FROM FLAT FILE TO COMPUTE FIELD POSITIONS (Use for Results from Customer)
#-------------------------------------------------------------------------------------------------------------------------------#VARS#-------------------------------------------------------------------------------------------------------------------------------Class colmn{ [int] $colNum [string]$colName
[int]$startIndex [int]$endIndex [int]$length [string] $lastColFlag}
$ht_HdrColNames = @{};#Key is ColNum (1 based Index),Value is ColName$ht_HdrColPos = @{};#Key is ColNum (1 based Index),Value is colmn object#-------------------------------------------------------------------------------------------------------------------------------
#-------------------------------------------------------------------------------------------------------------------------------#FORMAT XML#-------------------------------------------------------------------------------------------------------------------------------function Format-XML ($xml, $indent=2) { $StringWriter = New-Object System.IO.StringWriter $XmlWriter = New-Object System.XMl.XmlTextWriter $StringWriter $xmlWriter.Formatting = “indented” $xmlWriter.Indentation = $Indent $xml.WriteContentTo($XmlWriter) $XmlWriter.Flush() $StringWriter.Flush() return $StringWriter.ToString();}
#*******************************************************************************************************************#MAIN EXECUTION STARTS HERE#*******************************************************************************************************************
clear-host;$clipboard = Get-Clipboard;$counter = 0Write-Host "rows in clipboard "$clipboard.LengthWrite-Host
$sbFullHTMLStr = [System.Text.StringBuilder]::new();$sbFullSQLInsertStr = [System.Text.StringBuilder]::new();
if ($OutputClipboardFormat -eq "EXCEL"){ $excel = New-Object -ComObject excel.application; $excel.visible = $False $workbook = $excel.Workbooks.Add(); $SQLRowsWkSht= $workbook.Worksheets.Item(1) $SQLRowsWkSht.Name = "SQLRows"}
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>#TOP HEADER ROW PROCESSING#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>if ($SQLInsertRowStr.Length -eq 0 -and $splitByRegEx -eq ''){ $SQLInsertSkipFirstColVal='Y';}
$headerRow = $clipboard[0];
$sbFullHTMLStr.Append("<table border='1'>");$sbFullHTMLStr.Append("<tr>");
if ($splitByRegEx.Length -eq 0) {#BUILD $ht_HdrColPos -> Flat File from Customer Assumed $arr=$headerRow -split '\s+' #Split by White Space } else {#use Delimiters to split columns for every row including header. $arr=$headerRow -split $splitByRegEx }
$colNum=0; foreach($cell in $arr) { $colNum++; $ht_HdrColNames.Add($colNum, $cell); if ($OutputClipboardFormat -eq "EXCEL") { $SQLRowsWkSht.Cells.Item(1,$colNum) = $cell;#EXCEL $SQLRowsWkSht.Cells.Item(1,$colNum).BorderAround(1,2); $SQLRowsWkSht.Cells.Item(1,$colNum).Font.Italic=$True; $SQLRowsWkSht.Cells.Item(1,$colNum).Font.Bold=$True; $SQLRowsWkSht.Cells.Item(1,$colNum).Font.Size=$GregsExcelFontSize; $SQLRowsWkSht.Cells.Item(1,$colNum).Interior.ColorIndex = 6; }#end of if ($OutputClipboardFormat -eq "EXCEL")
if ($SQLInsertSkipFirstColVal -eq 'Y' -and $colNum -eq 1) { continue; }
$sbFullHTMLStr.Append(" <th>$cell</th>") | out-null; }#end of foreach($cell in $arr)
$sbFullHTMLStr.Append("</tr>") | out-null;#$FullHTMLStr = $FullHTMLStr + ""
if ($splitByRegEx.Length -eq 0) {#BUILD $ht_HdrColPos -> Flat File from Customer Assumed foreach($colNum in $ht_HdrColNames.Keys | sort) { $clmn = New-Object colmn; $clmn.colNum=$colNum; $clmn.colName = $ht_HdrColNames[$colNum];
if ($SQLInsertRowStr.Length -eq 0) { $SQLInsertSkipFirstColVal='Y'; $SQLInsertRowStr = 'Insert Into ' + $clmn.colName;#Assumne First Col is table Name }
[string] $thisColName = $clmn.colName; $thisColName
$Matches = Select-String -InputObject $headerRow -Pattern $thisColName -AllMatches [int] $occurenceCnt = $Matches.Matches.Count; if ($occurenceCnt -gt 1) { #If we here colname is duplicated in table with other text appended or prepended, lets try to find with space before and after, if at start of line or end of line this won't work $thisColName=" $thisColName "; }
[int] $startIndex = $headerRow.IndexOf($thisColName, 0); $clmn.startIndex=$startIndex;
[int] $nextColNum=$colNum+1; if ($ht_HdrColNames.ContainsKey($nextColNum) -eq $true) { [string] $nextColName = $ht_HdrColNames[$nextColNum]; $Matches2 = Select-String -InputObject $headerRow -Pattern $nextColName -AllMatches [int] $occurenceCnt2 = $Matches.Matches.Count;
if ($occurenceCnt2 -gt 1) { #If we here colname is duplicated in table with other text appended or prepended, lets try to find with space before and after, if at start of line or end of line this won't work $nextColName=" $nextColName "; }
$clmn.endIndex=$headerRow.IndexOf($nextColName, $startIndex); $clmn.lastColFlag='N'; } else {#PROBLEM: Header is not reliable here to indicate max length of values. To Extract Values better off grabbing to end of Values line. $clmn.endIndex=$headerRow.Length; $clmn.lastColFlag='Y'; }
$clmn.length=$clmn.endIndex-$clmn.startIndex; $clmn.endIndex=$clmn.endIndex-1; $ht_HdrColPos.Add($colNum, $clmn);
}#end of foreach($colNum in $ht_HdrColNames.Keys)
$ht_HdrColPos.Values; #Display to output }#end of if ($splitByRegEx.Length -eq 0)
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>#ITERATE EACH ROW IN CLIPBOARD#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>foreach($row in $clipboard){$row
#SKIP ROW 0, WE HANDLE HEADER ABOVE! if ($counter -eq 0) { $counter++ continue; }
#SKIP EMPTY ROWS if ($row.Length -lt 2) { $counter++ continue; }
if ($splitByRegEx.Length -eq 0 -and $counter -eq 1) {#SKIP ---------------- line $counter++ continue; }
if ($OutputClipboardFormat -eq "SQLINSERT") { $sbFullSQLInsertStr.Append($SQLInsertRowStr + " VALUES(") | Out-Null; }
$sbFullHTMLStr.Append("<tr>") | out-null;
$counter
if ($splitByRegEx.Length -eq 0) { $arr=@();
foreach($colNum in $ht_HdrColNames.Keys | sort) { $clmn = $ht_HdrColPos[$colNum]; if ($clmn.lastColFlag -eq 'Y') { $colVal=$row.Substring($clmn.startIndex); } else { $colVal=$row.Substring($clmn.startIndex, $clmn.length); } $colVal=$colVal.Trim(); if ($colVal.EndsWith("00:00:00.000") -eq $true) { $colVal=$colVal.Remove($colVal.Length-12); $colVal=$colVal.Trim(); } $arr+=$colVal; } }#end of if ($splitByRegEx.Length -eq 0) else { #SPLIT BY TABS if ($splitByRegEx -ne '\t') { #Strip 00:00:00 $row=$row.Replace("00:00:00.000", ""); }
$arr=$row -split $splitByRegEx }
$colNum=0; foreach($cell in $arr) { $colNum++;
if ($OutputClipboardFormat -eq "EXCEL") { $SQLRowsWkSht.Cells.Item($counter+1,$colNum) = $cell;#EXCEL $SQLRowsWkSht.Cells.Item($counter+1,$colNum).BorderAround(1,2); $SQLRowsWkSht.Cells.Item($counter+1,$colNum).Font.Size=$GregsExcelFontSize;
[string] $hdrNameStr = $ht_HdrColNames[$colNum]; $hdrNameStr=$hdrNameStr.ToLower(); if ($hdrNameStr.Contains("date") -eq $true) { $SQLRowsWkSht.Cells.Item($counter+1,$colNum).NumberFormat = "yyyy-mm-dd"; } }#end of if ($OutputClipboardFormat -eq "EXCEL")
if ($SQLInsertSkipFirstColVal -eq 'Y' -and $colNum -eq 1) { continue; }
if ($OutputClipboardFormat -eq "SQLINSERT") {
if ($cell -eq 'NULL') { $sbFullSQLInsertStr.Append("NULL") | Out-Null; } else { $sbFullSQLInsertStr.Append("'" + $cell + "'") | Out-Null; }
if ($colNum -lt $arr.Length) { $sbFullSQLInsertStr.Append(",") | Out-Null; } }#end of if ($OutputClipboardFormat -eq "SQLINSERT")
#The 4th COLUMN is XML MSG. LETS Encode it# if ($colNum -eq 4)# {# $cellStr = $cell.Trim();# $ch = $cellStr[0];#Get First CHAR# if ($ch -eq "<")# {# #If here treat as XML# [xml] $xmlObj = $cell;#Cast as XML# $formattedXMLStr = Format-XML $xmlObj;#Make FORMATTING NICE# $encodedCell = [System.Web.HttpUtility]::HtmlEncode($formattedXMLStr);# $FullHTMLStr = $FullHTMLStr + " <td><pre>$encodedCell</pre></td>";# }# else # {# if ($ch -eq "{")# {# $FullHTMLStr = $FullHTMLStr + " <td><pre>$cellStr</pre></td>";# }# else# { #NORMAL STRING, NO SPECIAL PROCESSING# $FullHTMLStr = $FullHTMLStr + " <td>$cell</td>";# }# }# }#end of if ($colNum -eq 4)# else# { #NORMAL STRING, NO SPECIAL PROCESSING $sbFullHTMLStr.Append(" <td>$cell</td>") | Out-Null;# }
} $sbFullHTMLStr.Append("</tr>") | Out-Null;
if ($OutputClipboardFormat -eq "SQLINSERT") { $sbFullSQLInsertStr.Append(")") | Out-Null; }
$counter++}#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>#END OF ITERATE EACH ROW IN CLIPBOARD#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>$sbFullHTMLStr.Append("</table>") | out-null;
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>#HTML FILE#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>[string] $htmlFilePath=$targetFolder+"SQLRows.html";$retVal = Test-Path -LiteralPath $htmlFilePath -PathType Leaf #LiteralPath is required to stop interpreting special characters like ![], etc.if ($retVal -eq $True){ Remove-Item $htmlFilePath;}
[string] $FullHTMLStr =$sbFullHTMLStr.ToString();$FullHTMLStr | Out-File -FilePath $htmlFilePath
if ($OutputClipboardFormat -eq "HTMLTEXT"){ Set-Clipboard -Value $FullHTMLStr; & $htmlFilePath;}
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>#SQL FILE#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>[string] $sqlFilePath=$targetFolder+"SQLInserts.sql";$retVal = Test-Path -LiteralPath $sqlFilePath -PathType Leaf #LiteralPath is required to stop interpreting special characters like ![], etc.if ($retVal -eq $True){ Remove-Item $sqlFilePath;}
[string] $FullSQLInsertStr = $sbFullSQLInsertStr.ToString();if ($OutputClipboardFormat -eq "SQLINSERT"){ Set-Clipboard -Value $FullSQLInsertStr; $FullSQLInsertStr | Out-File -FilePath $sqlFilePath & $htmlFilePath; & $sqlFilePath;}
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>#EXCEL FILE#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>[string] $ExcelFilePath = $targetFolder+"SQLRows.xlsx";$retVal = Test-Path -LiteralPath $ExcelFilePath -PathType Leaf #LiteralPath is required to stop interpreting special characters like ![], etc.if ($retVal -eq $True){ Remove-Item $ExcelFilePath;}
if ($OutputClipboardFormat -eq "EXCEL"){ #adjusting the column width so all data's properly visible $usedRange = $SQLRowsWkSht.UsedRange; $usedRange.EntireColumn.AutoFit() | Out-Null; $workbook.SaveAs($ExcelFilePath);
#Copy to Clipboard $usedRange.Select(); $usedRange.Copy();
$workbook.Close; #$excel.Quit();
& $ExcelFilePath}