Useful scripts by Amit Chauhan

Thursday, February 15, 2024

chick-fil-e rewards to excel

go to URL : https://order.chick-fil-a.com/my-rewards
Click past rewards
Click see more
paste script in chrome developer tools >> console

var result="";var i=1;document.querySelectorAll('html >body > div > div > div > div > table > tbody > tr').forEach((x)=>{ if(i%2==0){ x.childNodes[0].childNodes[0].childNodes[1].querySelectorAll('td').forEach((y)=> {result+='\t'+y.textContent.replace(':','\t')}) } else { result+='\r\n'+x.innerText;} i++; });

type result (press enter)
to see generated output

colonist userstat to excel (tab separated values)

Go to any userprofile on colonist.io
click history tab on left (do not expand any history)
paste below script in chrome developer tools >> console

var result="";var i=1;document.querySelectorAll('html >body > div > div > div > div > table > tbody > tr').forEach((x)=>{ if(i%2==0){ x.childNodes[0].childNodes[0].childNodes[1].querySelectorAll('td').forEach((y)=> {result+='\t'+y.textContent.replace(':','\t')}) } else { result+='\r\n'+x.innerText;} i++; });

type result press enter(to see generated output)

Tuesday, July 24, 2018

Document SSAS Cube Database ( Data Dictionary )

Simple script to generate SSAS Cube Documentation.
This script generates three separate CSV files : DSV, dimensions and attributes.
Can be further extended to document Measures, Perspectives, Roles or Mining Structures.

cls

##########################################################

# Created By : Amit Chauhan

# Created On : 20170215

# Purpose : List Cube dimension and attributes with source query for cube documentation

# Parameters : None

# Test Stub : None

##########################################################

Function Read-HostSpecial {

[cmdletbinding(DefaultParameterSetName="_All")]

Param(

[Parameter(Position = 0,Mandatory,HelpMessage = "Enter prompt text.")]

[Alias("message")]

[ValidateNotNullorEmpty()]

[string]$Prompt,

[Alias("foregroundcolor","fg")]

[consolecolor]$PromptColor,

[string]$Title,

[Parameter(ParameterSetName = "SecureString")]

[switch]$AsSecureString,

[Parameter(ParameterSetName = "NotNull")]

[switch]$ValidateNotNull,

[Parameter(ParameterSetName = "Range")]

[ValidateNotNullorEmpty()]

[int[]]$ValidateRange,

[Parameter(ParameterSetName = "Pattern")]

[ValidateNotNullorEmpty()]

[regex]$ValidatePattern,

[Parameter(ParameterSetName = "Set")]

[ValidateNotNullorEmpty()]

[string[]]$ValidateSet

)

Write-Verbose "Starting: $($MyInvocation.Mycommand)"

Write-Verbose "Parameter set = $($PSCmdlet.ParameterSetName)"

Write-Verbose "Bound parameters $($PSBoundParameters | Out-String)"

#combine the Title (if specified) and prompt

$Text = @"

$(if ($Title) {

"$Title'n$("-" * $Title.Length)"

})

$Prompt :

"@

#create a hashtable of parameters to splat to Write-Host

$paramHash = @{

NoNewLine = $True

Object = $Text

}

if ($PromptColor) {

$paramHash.Add("Foregroundcolor",$PromptColor)

}

#display the prompt

Write-Host @paramhash

#get the value

if ($AsSecureString) {

$r = $host.ui.ReadLineAsSecureString()

}

else {

#read console input

$r = $host.ui.ReadLine()

}

#assume the input is valid unless proved otherwise

$Valid = $True

#run validation if necessary

if ($ValidateNotNull) {

Write-Verbose "Validating for null or empty"

if($r.length -eq 0 -OR $r -notmatch "\S" -OR $r -eq $Null) {

$Valid = $False

Write-Error "Validation test for not null or empty failed."

}

}

elseif ($ValidatePattern) {

Write-Verbose "Validating for pattern $($validatepattern.ToString())"

If ($r -notmatch $ValidatePattern) {

$Valid = $False

Write-Error "Validation test for the specified pattern failed."

}

}

elseif ($ValidateRange) {

Write-Verbose "Validating for range $($ValidateRange[0])..$($ValidateRange[1]) "

if ( -NOT ([int]$r -ge $ValidateRange[0] -AND [int]$r -le $ValidateRange[1])) {

$Valid = $False

Write-Error "Validation test for the specified range ($($ValidateRange[0])..$($ValidateRange[1])) failed."

}

else {

#convert to an integer

[int]$r = $r

}

}

elseif ($ValidateSet) {

Write-Verbose "Validating for set $($validateset -join ",")"

if ($ValidateSet -notcontains $r) {

$Valid = $False

Write-Error "Validation test for set $($validateset -join ",") failed."

}

}

If ($Valid) {

Write-Verbose "Writing result to the pipeline"

#any necessary validation passed

$r

}

Write-Verbose "Ending: $($MyInvocation.Mycommand)"

} #end function

#define an alias

Set-Alias -Name rhs -Value Read-HostSpecial

$serverName = ""

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null

$server = new-Object Microsoft.AnalysisServices.Server

do

{

$Error.Clear()

$serverName=Read-HostSpecial "Enter SSAS Server Name with Instance'r'ne.g. SERVERNAME\INSTANCENAME or 10.152.1.10\INSTANCENAME" -PromptColor Green -ValidateNotNull

$server.Connect($serverName)

if($Error){$done=1}

}

while($done -eq 0)

$server.Databases | Format-Wide

$dbname=Read-HostSpecial "'r'nEnter SSAS Database Name to be exported from above list" -PromptColor Green -ValidateNotNull

# try{

$CubeList = @()

$DSVList = @()

$DimList = @()

$AttributeList = @()

ForEach($db in $server.Databases)

{

if( $db.Name.ToLower() -eq $dbname.ToLower() )

{

Write-Host "Matching cube found : " $db.Name

foreach ($cube in $db.Cubes)

{

"DSV found : 'r'n "

$prevDSVName=""

foreach($dsvSchemaTable in $cube.DataSourceView.Schema.Tables)

{

$dsvname=$cube.DataSourceView.Name

if($dsvname -ne $prevdsvname) {" " + $dsvname ; $prevdsvname=$dsvname}

#$dsv = New-Item PSObject

foreach($p in $dsvSchemaTable.ExtendedProperties)

{

$dsvTemp= New-Object PSObject

$dsvTemp | add-member -membertype NoteProperty -name "Type" -value "DSVTable"

$dsvTemp | add-member -membertype NoteProperty -name "Cube" -value $cube.Name

$dsvTemp | add-member -membertype NoteProperty -name "DSVName" -value $cube.DataSource.Name

$dsvTemp | add-member -membertype NoteProperty -name "DSVTable" -value $dsvSchemaTable.TableName

$dsvTemp | add-member -membertype NoteProperty -name "SourceSchemaQuery" -value $p.QueryDefinition

$DSVList+=$dsvTemp

}

}

#Write-Host "Dimension","DimensionID","DimensionName","SourceTable","AttributeList","HierarchyList","AttributeCount","HeirarchyCount"

foreach ($dim in $cube.Dimensions)

{

$attListName=""

$hieListName=""

foreach($a in $dim.Attributes)

{

$attTemp= New-Object PSObject

$attTemp | add-member -membertype NoteProperty -name "Type" -value "Attribute"

$attTemp | add-member -membertype NoteProperty -name "Cube" -value $cube.Name

$attTemp | add-member -membertype NoteProperty -name "ParentDimension" -value $dim.DimensionID

$attTemp | add-member -membertype NoteProperty -name "AttributeID" -value $a.AttributeID

$attTemp | add-member -membertype NoteProperty -name "AttributeIDName" -value $a.Attribute.name

$attTemp | add-member -membertype NoteProperty -name "Usage" -value $a.Attribute.Usage

$attTemp | add-member -membertype NoteProperty -name "DerivedFromTableId" -value $a.Attribute.DerivedFromTableId

$attTemp | add-member -membertype NoteProperty -name "DerivedFromColumnId" -value $a.Attribute.DerivedFromColumnId

$attTemp | add-member -membertype NoteProperty -name "NameColumn" -value $a.Attribute.NameColumn

$keyColumnList=""

foreach($attributeKeyColumn in $a.Attribute.KeyColumns)

{

$keyColumnList+= $attributeKeyColumn.Source.TableID +"."+ $attributeKeyColumn.Source.ColumnID +","

}

$attTemp | add-member -membertype NoteProperty -name "KeyColumn" -value $keyColumnList

$attTemp | add-member -membertype NoteProperty -name "ValueColumn" -value $a.Attribute.ValueColumn

$AttributeList+=$attTemp

$attListName+=$a.Attribute.name +","

} # end of attribute

foreach($dsvSchemaTable in $dim.Hierarchies)

{

#"Hierarchy," + $dim.DimensionID +","+ $dsvSchemaTable.DataSet. +","+$a.Attribute.name +","+ $a.Attribute.DerivedFromTableId +","+ $a.Attribute.DerivedFromColumnId +$a.Attribute.NameColumn +","+$a.Attribute.KeyColumns

$hieListName+=$dsvSchemaTable.Hierarchy.ID +","

}

$dimTemp= New-Object PSObject

$dimTemp | add-member -membertype NoteProperty -name "Type" -value "Dimension"

$dimTemp | add-member -membertype NoteProperty -name "Parent" -value $dim.Parent

$dimTemp | add-member -membertype NoteProperty -name "DimensionID" -value $dim.Dimension.ID

$dimTemp | add-member -membertype NoteProperty -name "DimensionName" -value $dim.Dimension.Name

$dimTemp | add-member -membertype NoteProperty -name "DimensionDesc" -value $dim.Dimension.Description

$dimTemp | add-member -membertype NoteProperty -name "DimensionKeyAttribute" -value $dim.Dimension.KeyAttribute

$dimTemp | add-member -membertype NoteProperty -name "DimensionDSV" -value $dim.Dimension.DataSourceView.ID

$dimTemp | add-member -membertype NoteProperty -name "DimensionDS" -value $dim.Dimension.DataSourceView.DataSource

$dsvname=$dim.Dimension.Annotations.Item(0).Value.ddscontrol.layoutobject.ddsxmlobj.property.value

$dimTemp | add-member -membertype NoteProperty -name "DimensionSourceTable" -value $dsvname

$dimTemp | add-member -membertype NoteProperty -name "AttributeCount" -value $dim.Attributes.Count

$dimTemp | add-member -membertype NoteProperty -name "HierarchyCount" -value $dim.Hierarchies.Count

$dimTemp | add-member -membertype NoteProperty -name "AttributeList" -value $attListName

$dimTemp | add-member -membertype NoteProperty -name "HierarchyList" -value $hieListName

$dsvquery = ($DSVList.Where{$_.DSVTable -eq $dsvname }) | Select-Object -ExpandProperty "SourceSchemaQuery"

$dimtemp | add-member -membertype NoteProperty -name "SourceQuery" -value $dsvquery

$DimList+= $dimTemp

$dimTemp = $null

$dsvName =""

} # end of dimension

} # end of cube

} ##endif dbname match

} #end of foreach database

#} #end of try

#catch { "Error occurred :"+ $Error }

do

{

$Error.Clear()

$inputfile=Read-HostSpecial "Files ready!'r'nPlease select output path " -PromptColor Green -ValidateNotNull

}

while( (Test-Path $inputfile) -eq $false )

try{

$filename=$inputfile+"\"+ $cube.Name + "-DSV.csv"

If (Test-Path $filename){ Remove-Item $filename }

$DSVList | Export-Csv $filename -NoTypeInformation -NoClobber -Force

Write-Information "Exported $filename"

}

catch{ "Failed export : "+ $Error.ToString(); }

if($Error.Count -eq 0){ "Exported : $filename"; $Error.Clear();}

try{

$filename=$inputfile +"\"+ $cube.Name + "-DimensionList.csv"

If (Test-Path $filename){ Remove-Item $filename }

$DimList | Export-Csv $filename -NoTypeInformation -NoClobber -Force

Write-Information "Exported $filename"

}

catch{ "Failed export : "+ $Error.ToString(); }

if($Error.Count -eq 0){ "Exported : $filename"; $Error.Clear();}

try{

$filename=$inputfile+"\"+ $cube.Name + "-AttributeList.csv"

If (Test-Path $filename){ Remove-Item $filename }

$AttributeList | Export-Csv $filename -NoTypeInformation -NoClobber -Force

Write-Information "Exported $filename"

}

catch{ "Failed export : "+ $Error.ToString(); }

if($Error.Count -eq 0){ "Exported : $filename"; $Error.Clear();}

"Press any key to exit."

cmd /c pause | out-null

Export SSAS diagram as single image

Visual studio doesn't give option to SSAS Database / Cube diagram in JPG, until micrsoft come with their own option to export it to image - here is the workaround.

1. Export to PDF
2. PDF to JPGs
3, Crop jpgs (Irfanview Portable)
4. Create HTML page to view all images in table. (assuming diagram name is mydiagram)
code

<html> <head> <style> td, tr, img { padding: 0px; margin: 0px; border: none; } td { width:1694;height:2149;} table { border-collapse: collapse; background-color:red; border-spacing: 0px; } </style> <script language="javascript" type="text/javascript" > function padDigits(number, digits) { return Array(Math.max(digits - String(number).length + 1, 0)).join(0) + number; } document.write('\r\n\t<table'); for (var i = 0; i < 10; i++) { document.write('\r\n\t<tr>'); for (var j = 0; j < 6; j++) { document.write('\r\n\t\t<td style="border: 0px none black "><img src="mydiagram-' + padDigits(((i * 6) + j) + 1, 2) + '.jpg" class="cropimg" /> </td>'); } document.write('\r\n\t</tr>'); } document.write('\r\n\t</table>'); </script> </head> <body> </body> </html>

5. Export web page with chrome extension Full Page Screen Capture offered by mrcoles.com ( Grant local page file permission)

Thursday, July 19, 2018

get wallmart page search result in csv clipboard

If you are running chrome copy below text
press F12 and select console
press Ctrl+L
copy and paste below text (available on https://jsfiddle.net/52kjy9zg/1/ )

and results are in your clipboard

var p1 = $x('//*[@id="searchProductResult"]/ul/li');
var result = "";
for (i = 0; i < p1.length; i++) {
var name = "\"" + $x('//*[@id="searchProductResult"]/ul/li[' + i + ']/div/div/div/div/span/a/span')
.map(e => e.textContent)
.join('') + "\"";
var price = "\"" + $x('//*[@id="searchProductResult"]/ul/li[' + i + ']/div/div/div/div/span/div/div/div/span/span')
.map(e => e.textContent)
.join('') + "\"";
var originalprice = "\"" + $x('//*[@id="searchProductResult"]/ul/li[' + i + ']/div/div/div/div/span/div/span/span/span/span/span')
.map(e => e.textContent)
.join('') + "\"";
result += name + "," + price + "," + originalprice + "\r\n";
}
var copy = function(e) {
e.preventDefault();
console.log('copy');
var text = "blabla"
if (e.clipboardData) {
e.clipboardData.setData('text/plain', result);
} else if (window.clipboardData) {
window.clipboardData.setData('Text', result);
}
}
window.addEventListener('copy', copy);
document.execCommand('copy');
window.removeEventListener('copy', copy);

Wednesday, December 13, 2017

Merge Same Name Worksheet to single worksheet

It so happens, we have several excel that has multiple worksheet following same format.

You are in hurry and want to just merge them in to one excel.

Here is what you might do manually.

Select a folder that has all excel that needs to be merged.
Target output excel file will be "\Output\Merged.xlsx"
For each excel
- For each worksheet
- Copy data
- Lookup in merged excel
- - if same worksheet exists then paste at end
- - else create new worksheet and paste
- repeat it.(until you are exhausted)

Below is the VBA macro that exactly what it does for you.

Create new blank worksheet/excel and run it.
If you new to VBA/Macro and don't know where to place below code and run it only using your excel, click here. (at step 4 paste below code and press F8 and then F5, which will prompt source folder)

This is my first blog entry here.
Kindly put thanks note if you find it useful.

Sub MergeSameNamedWorkSheets()

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Dim masterwbk As Workbook

Dim childwbk As Workbook

Dim wbk1 As Workbook

Dim filename As String

Dim Path As String

Dim NewWkbk As Workbook

NewWkbk = Workbooks.Add

Dim mywkb As Workbook

mywkb = ThisWorkbook

ThisWorkbook.Sheets(1).Copy Before:=NewWkbk.Sheets(1)

Dim fldr As FileDialog

Dim sItem As String

fldr = Application.FileDialog(msoFileDialogFolderPicker)

With fldr

.Title = "Select Source Folder"

.AllowMultiSelect = False

If .Show <> -1 Then GoTo NextCode

sItem = .SelectedItems(1)

End With

NextCode:

GetFolder = sItem

Path = GetFolder & "\" 'CHANGE PATH

Dim fdObj As Object

fdObj = CreateObject("Scripting.FileSystemObject")

If fdObj.FolderExists(Path & "Output") = False Then

fdObj.CreateFolder(Path & "Output")

End If

NewWkbk.SaveAs Path & "Output\Merged.xlsx"

fldr = Nothing

filename = Dir(Path & "*.xlsx")

Do While Len(filename) > 0

Application.ScreenUpdating = True

mywkb.Activate()

ActiveSheet.Cells(ActiveCell.Row + 1, 1).Select()

ActiveCell.value = Path & filename

ActiveSheet.Cells(ActiveCell.Row, 2).Select()

ActiveCell.value = "Started"

Application.ScreenUpdating = False

masterwbk = Workbooks.Open(Path & "output\Merged.xlsx")

masterwbk.Activate()

Dim masterws() As Object

Dim str As Object

For I = 0 To masterwbk.Worksheets.Count - 1

str = masterwbk.Worksheets(I + 1)

ReDim Preserve masterws(I + 1) As Variant

masterws(I) = str.Name

Next I

childwbk = Workbooks.Open(Path & filename)

childwbk.Activate()

For I = 1 To childwbk.Worksheets.Count - 1

Dim ws As Worksheet

Sheets(I).Select()

ws = childwbk.ActiveSheet

Dim childwsname As String

childwsname = ws.Name

If LCase(Left(childwsname, 5)) = "sheet" Then GoTo continue

If Not containsvalue(masterws, childwsname) Then

ws = masterwbk.Sheets.Add(After:= _

masterwbk.Sheets(masterwbk.Sheets.Count))

ws.Name = childwsname

Dim ubmasterws As Integer

ubmasterws = UBound(masterws)

ReDim Preserve masterws(ubmasterws + 1) As Variant

masterws(ubmasterws) = childwsname

End If

Range("A1").Select()

Range(Selection, Selection.End(xlToRight)).Select()

Range(Selection, Selection.End(xlDown)).Select()

Selection.Copy()

'Set wbk1 = ThisWorkbook

Windows("Merged.xlsx").Activate()

masterwbk.Activate()

Sheets(childwsname).Select()

Cells.SpecialCells(xlCellTypeLastCell).Select()

ActiveSheet.Cells(ActiveCell.Row, 1).Select()

ActiveSheet.Cells(ActiveCell.Row + 1, ActiveCell.Column).Select()

If (Len(Trim(Selection.Text)) = 0) Then

ActiveSheet.Paste()

'Application.ScreenUpdating = True

mywkb.Activate()

ActiveSheet.Cells(ActiveCell.Row, 3).Select()

ActiveCell.value = CInt(ActiveCell.value) + 1

Application.ScreenUpdating = False

End If

Windows(filename).Activate()

Continue For

Range("A1").Select()

Selection.Copy()

ActiveSheet.Paste()

Application.CutCopyMode = False

childwbk.Close False

masterwbk.Close True

Application.ScreenUpdating = True

mywkb.Activate()

ActiveSheet.Cells(ActiveCell.Row, 4).Select()

ActiveCell.value = "Completed"

Application.ScreenUpdating = False

filename = Dir

Loop

Application.ScreenUpdating = True

Application.DisplayAlerts = True

MsgBox "All source files are merged and stored at " & Path & "Output\Merged.xlsx"

End Sub

Function containsvalue(values As Object, value As String) As Boolean

containsvalue = False

For I = 0 To UBound(values)

If values(I) = value Then

containsvalue = True

Exit For

End If

If values(I) = vbEmpty Then

Exit For

End If

Next I

End Function