topic Re: How to Run Power shell script in Qlik Sense in App Development

How to Run Power shell script in Qlik Sense

dineshraj — Mon, 25 Nov 2019 13:10:45 GMT

Hi All,

I have power shell script file(.ps1). I need to run the file from Qlik Sense . I tried with Batch file. While Running the batch file manually i am getting the expected result. When i am running the batch with Execute command it is not working as expected. But script load completed without any error

Note : My power shell script converts different excels into single excel in different sheets

I ll appreciate the immediate reply.

Thank and Regards,

Dineshraj R

Re: How to Run Power shell script in Qlik Sense

tresB — Mon, 25 Nov 2019 13:41:58 GMT

This could be privilege issue. Check here: https://community.qlik.com/t5/QlikView-Documents/QlikView-and-Windows-PowerShell/ta-p/1497315

Re: How to Run Power shell script in Qlik Sense

dineshraj — Mon, 25 Nov 2019 14:46:37 GMT

I have seen this already. It didn't help me. Is there any other way to run the (.ps1) file?

Thanks

Re: How to Run Power shell script in Qlik Sense

arychener — Wed, 25 May 2022 14:54:18 GMT

Hi @dineshraj ,

I've the same issue.. did you find a solution ?

Thanks !

Re: How to Run Power shell script in Qlik Sense

Qlik_Eric_Thomas — Wed, 25 May 2022 16:09:16 GMT

Have you ensured that the Qlik Sense environment is running 'Legacy Mode' and 'OverrideScriptSecurity' has been enabled?

Please see this article on the 'EXECUTE' function: https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularStatements/Execute.htm

How to disable Standard Mode: https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/LoadData/disable-standard-mode.htm

Note: This does expose the File System of the Qlik Sense environment

Re: How to Run Power shell script in Qlik Sense

arychener — Wed, 25 May 2022 16:51:55 GMT

Hi,

Yes I'm in legacy mode, and I'm already using the Execute command for another ps1 without any problems. It seems that it is the ps1 that uses Excel (well installed on the Qlik Sense server) that is not working.

Re: How to Run Power shell script in Qlik Sense

Qlik_Eric_Thomas — Wed, 25 May 2022 21:17:40 GMT

It's hard to say why including excel functionality in your powershell script causes different behaviour. I would suggest attempting to run this powershell script as the Qlik Sense service account on the Qlik Sense server.

If you are dealing with multiple nodes, there are additional considerations such as making sure the file location is accessible from any server that may perform the reload (I would suggest using UNC paths or even Folder Data Connections). Also make sure Excel is on any server that may perform the reload.

Could be useful to log powershell error during execution with something like

*>&1 > output.txt

at the end of the script

Re: How to Run Power shell script in Qlik Sense

arychener — Mon, 30 May 2022 08:59:35 GMT

Hi,

All these points are ok:
- execution of the script with the QS service account
- Excel on the QS server
- Single node configuration

I also tested the execution of the script on my computer, via QS Desktop, and it's ok.

When I run the script below via external tasks, the txt file is created, but not the Excel file.
Sorry but I am not comfortable with the powershell scripting, where can I add the instructions to log?

Thanks a lot!

#Define locations and delimiter
$csv = "C:\_Consultants\ARY\PS1\conversionexcel.csv" #Location of the source file
$xlsx = "C:\_Consultants\ARY\PS1\conversionexcel.xlsx" #Desired location of output
$delimiter = ";" #Specify the delimiter used in the file

# Create a new Excel workbook with one empty sheet
$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

# Build the QueryTables.Add command and reformat the data
$TxtConnector = ("TEXT;" + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType = 1
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

# Execute & delete the import query
$query.Refresh()
$query.Delete()

# Save & close the Workbook as XLSX.
$workbook.SaveAs($xlsx,51)
$excel.Quit()

Out-File -FilePath "C:\_Consultants\ARY\PS1\test.txt"