terminate / kill stopping TM1 Windows service

If your TM1 service is in "Stopping" status (in Windows Services), you can use the following cmd commands to kill it: Get the service PID: sc queryex TM1_SERVICE_NAME Kill the service with PID: taskkill/PID XXXX /F

Convert all Excel sheets to CSV files (VB script)

The only thing I noticed, when you try to call this script from TM1: cmd = '"cscript.exe" //B //NOLOGO "' | scripts_path | 'xls2csv.vbs " "' | xlsx | '" "' | temp_path | '"'; ExecuteCommand( cmd, 1 ); It doesn't work, although it works when you execute this command manually in cmd. The solution I found is to create just an empty folder: c:\windows\syswow64\config\systemprofile\desktop

## ARGUMENTS ##################################################################

rem  xls2csv.vbs
rem =============================================================
rem  convert all NON-empty worksheets in an Excel file to csv
rem  CSV file names will default to Sheet names
rem  output folder defaults to the folder where the worksheet resides
rem  
rem  input parameter 1:  excel_file_path\excel_file in argument 1 
rem          (if excel_file_path is not specified, the current path is defaulted)
rem  input parameter 2:  target_path in argument 2
rem          (if target_path is not specified, excel_file_path is defaulted)
rem ============================================================

Dim strExcelFileName
Dim strCSVFileName

strExcelFileName = WScript.Arguments.Item(0)  'file name to parses
strExcelFileName = Replace(strExcelFileName,"/","\")

rem get path where script is running
Set fso = CreateObject ("Scripting.FileSystemObject")  'use this to find current path
strScript = Wscript.ScriptFullName
strScriptPath = fso.GetAbsolutePathName(strScript & "\..")

rem If the Input file is NOT qualified with a path, default the current path
LPosition = InStrRev(strExcelFileName, "\") 
if LPosition = 0 Then 'no folder path
    strExcelFileName = strScriptPath & "\" & strExcelFileName
strScriptPath = strScriptPath & "\" 
else                 'there is a folder path, use it for the output folder path also
strScriptPath = Mid(strExcelFileName, 1, LPosition) 
End If
rem msgbox LPosition & " - " & strExcelFileName & " - " & strScriptPath  ' use this for debugging

Set objXL = CreateObject("Excel.Application")
Set objWorkBook = objXL.Workbooks.Open(strExcelFileName)
objXL.DisplayAlerts = False

rem check if specified target folder for worksheets
if( WScript.Arguments.Length = 2) then
 strSheetPath = WScript.Arguments.Item(1)
Else
 strSheetPath = strScriptPath
End If
strSheetPath = Replace(strSheetPath,"/","\")

if( Right(strSheetPath,1) <> "\") then 
    strSheetPath = strSheetPath & "\"
End If

rem loop over worksheets
  For Each sheet In objWorkBook.Sheets  
      'only saveAS sheets that are NOT empty
if objXL.Application.WorksheetFunction.CountA(sheet.Cells) <> 0 Then 
rem             sheet.Rows(1).delete  ' this will remove Row 1 or the header Row
sheet.SaveAs strSheetPath & sheet.Name & ".csv", 6  'CSV
   End If
  Next

rem clean up  
objWorkBook.Close 
objXL.quit
Set objXL = Nothing 
Set objWorkBook = Nothing
Set fso = Nothing

rem end script

Report Studio multi-header/footer list

Sometimes we need to add grouping columns to list header or footer, if need only 1 column extra, we can add overall list header / footer. But this is much easier to add just a list row. Select your header or footer, click Structure -> Headers & Footers -> Insert List Row Cells Above / Bellow. You can insert multiple rows at the same time.


Then select the required row cells and use Merge / Split buttons (or Table menu) to design your header / footer.

Example of using PSFTP (Putty) in batch mode without adding the key to the cache (registry)

Hi. Just a small example how to use Putty in Powershell, running psftp in batch mode without adding the key to the cache (registry), this is regarding the prompt: "... The server's rsa2 key fingerprint is ... If you trust this host, enter "y" to add the key to PuTTY's cache and carry on connecting. If you want to carry on connecting just once, without adding the key to the cache, enter "n"

## ARGUMENTS ##################################################################
$sftpHost=$args[0]
$sftpUser=$args[1]
$sftpPsw=$args[2]

$sftpDir=$args[3]
$sftpFile=$args[4]
$targetDir=$args[5]

## DEBUGGING ##################################################################
#$sftpHost="ftp.cognos.com"
#$sftpUser="didenko"
#$sftpPsw="123!@#"

#$sftpDir = "myTestFolder/"
#$sftpFile = "test.txt"
#$sftpTargetDir = "D:\TM1\tmp"

## SET VARIABLES ##############################################################
$connectString = "$sftpUser@$sftpHost"
$targetFileFullPath = "$targetDir$sftpFile"

## GET MODEL FOLDER (TM1 DATA PARENT FOLDER)
$scriptDir = Split-Path -parent $MyInvocation.MyCommand.Path

## PRINT PARAMETERS ###########################################################
"########## Copy SFTP file tool log ########################"
"-----------------------------------------------------------"
"## Copy file: ($connectString) $sftpDir/$sftpFile"
"## To folder: $targetDir"
"-----------------------------------------------------------"

## REMOVE OLD DATA FILE #######################################################
If (Test-Path $targetFileFullPath){Remove-Item $targetFileFullPath}

## PREPARE SFTP SCRIPT ########################################################
$sftpScriptFile = "$scriptDir\script.psftp"
If (Test-Path $sftpScriptFile){Remove-Item $sftpScriptFile}
"## Preparing sftp script:"
$sftpScriptFile
"cd ""$sftpDir/""" | set-content $sftpScriptFile -Encoding Ascii
"lcd ""$targetDir""" | add-content $sftpScriptFile -Encoding Ascii
"mget ""$sftpFile""" | add-content $sftpScriptFile -Encoding Ascii
"ls" | add-content $sftpScriptFile -Encoding Ascii
"exit" | add-content $sftpScriptFile -Encoding Ascii

"------------------------------------------------------"

## EXECUTE SFTP SCRIPT ###############################################
"## Running sftp script:"
"$scriptDir\putty\PSFTP.exe $connectString -pw $sftpPsw -b $sftpScriptFile"
$ExecsftpScriptFile = "n" | & $scriptDir\putty\PSFTP.exe $connectString -pw $sftpPsw -b $sftpScriptFile

$ExecsftpScriptFile
"------------------------------------------------------"

## CHECK IF FILE WAS COPIED ###################################################
If (Test-Path $targetFileFullPath){"===> File succesfully copied:"}
else {"===> ERROR: file was not copied:"}
$targetFileFullPath
exit

Test TM1

Test TM1