Extract data from a powershell scripts and move it to different worksheets

[gates2010]

Hi,


This may be a VBA operation, I have a 90,000 line config file, which is similar to a c/powershell/code.


Ex:

function1{

blah{

blah{

}

}

}

function2{

blah{

blah{

}

}

}

function3{

blah{

blah{

}

}

}

function4{

blah{

blah{

}

}

}


I want to move each function to a worksheet in excel and name hte worksheet with the function name.


I am not sure where to start on this, I am familiar with scripting but this is the first time I am trying to do it in excel.


Any directions on where to start will greatly help. Thank you in advance for the info.

 

[SirJB7]

Hi, gates2010!


How would you like storing the resulting data? One line per cell? If one instruction per cell, you'll have to consider continuation lines and its codes.


If you're familiar with scripting just parse the functions (function, sub, procedure, I don't know the keyword at the heading -take care of comments previous to function header-) from its first line thru the last one (end, return...?).


A VBA structure code will look like this:


-----

[pre]

Option Explicit

Sub ImportingStrangeCode()
' constants
Const ksFile = "full path name"
' declarations
Dim iSheet As Integer, iFunction As Integer, bStart As Boolean, bEnd As Boolean
Dim I As String, A As String, sCode As String
' start
I = FreeFile()
Open ksFile For Input As #I
iSheet = 0
iFunction = 0
sCode = ""
' process
Line Input #I, A
Do While Not (EOF(I))
'
bStart = ImportingStrangeCodeStartDetected(A)
bEnd = ImportingStrangeCodeEndDetected(A)
'
If bStart Then
sCode = A
sfunction = ImportingStrangeCodeFunctionName(A)
ElseIf bEnd Then
sCode = sCode & A
If Len(sCode) > 0 Then
Worksheets.Add , , xlWorksheet
ActiveSheet.Name = sfunction
Cells(1, 1).Value = sCode
End If
Else
sCode = sCode & A
End If
'
Line Input #I, A
Loop
' end
Close #I
End Sub

Private Function ImportingStrangeCodeStartDetected(psLine As Text) As Boolean
Dim b As Boolean
' ... your stuff here, assigning b
ImportingStrangeCodeStartDetected = b
End Function

Private Function ImportingStrangeCodeEndDetected(psLine As Text) As Boolean
Dim b As Boolean
' ... your stuff here, assigning b
ImportingStrangeCodeEndDetected = b
End Function

Private Function ImportingStrangeCodeFunctionName(psLine As Text) As String
Dim s As String
' ... your stuff here, assigning s
ImportingStrangeCodeFunctionName = s
End Function

[/pre]
-----


This is the version for one function in first cell of each sheet. Adjust it to one line per cell, if function length exceeds Excel limit or want to.


Regards!

 

[gates2010]

Thank you SirJB7..

I would prefer one line per cell


I am familiar with bash scripting but not excel scripting, each function would have its own name. I was hoping I could track the number of open brackets and closed brackets to determine end of a function and to move to a new worksheet from there.


Thanks!

 

[SirJB7]

Hi, gates2010!

The code is nearly the same, in the function that checks the "function" or something alike, you can begin counting open brackets, and the code is nearly identical in its structure. Please tell if need any adjustments that you can't implement.

Regards!