Export substitution parameters to CSV

[chethan_lingaraju]

Why do we need substitution parameters in excel? :???:

In designer, we see substitution parameters in a grid view.

Whereas, when we have to export it, we will only have XML and ATL as option. These are not the straight forward information for humans to understand.

If there is a wider audience who wants to take a look at substitution parameters, instead of allowing everyone to login to designer, You can email them the substitution parameters in excel file :cool:

Idea behind the approach :wink:

Plan was to create a utility to export substitution parameters to CSV file from the repository. VB-Script was the easy way we could think of, as we were using Windows machine. Repository databases are hosted on SQL server.

Idea was to read repository database credentials from user. Export substitution parameters to XML file through al_engine.exe, and then convert it to CSV file.

Issues with comma separated values :shock:

• If there is a comma in SP value, cell value gets to split and span to multiple columns in excel. Tab separator was ideal.
• Date value will automatically undergo format change in excel upon opening the file. Cell value has been formatted as text.

VB-Script Code:

' Just copy paste the code in notepad, save it with vbs as extension and double click :cool:

' Or download it from attachment.

Option Explicit

Dim SQLHost, SQLDB, SQLUN, SQLPWD   

SQLHost = InputBox ("Enter target SQL Host,port:", "Export SP to tab delimited text file","")

SQLDB = InputBox ("Enter target SQL database:", "Export SP to tab delimited text file","")

SQLUN = InputBox ("Enter target SQL username:", "Export SP to tab delimited text file","")

SQLPWD = InputBox ("Enter target SQL password:", "Export SP to tab delimited text file","")

build_and_execute_command

SP_XML_to_CSV "SP.xml", "SP.txt"

Msgbox "Open generated tab delimited text file SP.txt in Excel." & vbCrLf & "If required, format it as table with header.",  vbInformation  ,"Export SP to tab delimited text file"

Function build_and_execute_command()

    Dim command, objShell, filesys

    set filesys=CreateObject("Scripting.FileSystemObject")

    Set objShell = WScript.CreateObject ("WScript.shell")

    command = """%LINK_DIR%\bin\al_engine.exe"" -NMicrosoft_SQL_Server -passphraseATL -z""" & "SP_error.log"" -U" & SQLUN & " -P" & SQLPWD & " -S" & SQLHost & " -Q" & SQLDB & " -XX@" & "v" & "@""" & "SP.xml"""

    export_execution_command "%LINK_DIR%\log\", "SP",command

    'objShell.run "%LINK_DIR%\log\" & "SP" & ".bat",0,true

    objShell.run "SP.bat",0,true

    filesys.DeleteFile "SP.bat", true

    if filesys.FileExists("SP_error.log") then

        msgbox ("Encountered issue while exporting SP from repo")

        build_and_execute_command = -1

    End if

    Set filesys = Nothing

End Function

Function export_execution_command(FilePath, FileName, FileContent)

    Dim objFSO, objFile, outFile

    Set objFSO=CreateObject("Scripting.FileSystemObject")

    'outFile = FilePath & FileName & ".bat"

    outFile = FileName & ".bat"

    Set objFile = objFSO.CreateTextFile(outFile,True)

    objFile.Write FileContent & vbCrLf

    objFile.Close

    export_execution_command = 0

End Function

Function SP_XML_to_CSV (xmlFile, csvFile)

    Dim ConfigList, SubParamList, objXMLDoc, Root, Config, SubParam, Matrix(1000,50)

    Dim i, j, iMax, jMax, Text, sessionFSO, OutFile, objShell

    Set sessionFSO = CreateObject("Scripting.FileSystemObject")

    Set OutFile    = sessionFSO.CreateTextFile(csvFile, 1)

    Set objShell = WScript.CreateObject ("WScript.shell")

    Set objXMLDoc = CreateObject("Microsoft.XMLDOM")

    objXMLDoc.async = False

    objXMLDoc.load(xmlFile)

    Set ConfigList = objXMLDoc.documentElement.getElementsByTagName("SVConfiguration")

    i = 1

    Matrix(0,0) = "Substitution Parameter"

    For Each Config In ConfigList

        Set SubParamList = Config.getElementsByTagName("SubVar")

        j = 1

        Matrix(0,i) = Config.getAttribute("name")

        For Each SubParam In SubParamList

            If i = 1 Then Matrix(j,0) = SubParam.getAttribute("name")

            Matrix(j,i) = "=""" & SubParam.text & """"

            j = j + 1

        Next

        i = i + 1

    Next

    iMax=i

    jMax=j

    For i=0 to jMax-1

        Text = ""

        For j=0 to iMax-1

            Text = Text & Matrix(i,j) & vbTab

        Next

        OutFile.WriteLine Text

    Next

    OutFile.Close

End Function

Usage screenshots:

In Excel, open the text file:

Select all the data cells and format it as table

Finally, Data looks like this: :cool:

If you don't have access to repository database or Jobserver, you can export substitution parameters to XML file manually from designer and use the function SP_XML_to_CSV from the given VB-Script.

Cheers :smile: