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,
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/ScriptRegu...
How to disable Standard Mode: https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/LoadData/disable-standard...
Note: This does expose the File System of the Qlik Sense environment
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.
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
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
# Save & close the Workbook as XLSX.
Out-File -FilePath "C:\_Consultants\ARY\PS1\test.txt"