Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiple Arrays written to text file 1

Status
Not open for further replies.
johnpayback

johnpayback

IS-IT--Management
Oct 2, 2006
110
US
I believe I've gotten most of the code but I cannot figure out a way to get my text file formatted the way I would like it. Below is what I want my text file to look like.

Code: 
Server Name			Free Space
server1				24%
server2				34%
server3				87%
server4				73%
server5				75%

Here is my code so far. I'm at a loss right now as how to write the output to look like the text above. How can I do this?

Code: 
Dim objConn, objrs, resultSet, SQLStmt
Dim objFSO, objFile

'Database Connection String
Set objConn = CreateObject("ADODB.Connection")
Set objrs = CreateObject("ADODB.Recordset")
    objConn.Open "Driver={SQL Server};" & _
                 "Server=SvrName\Instance;" & _
                 "Database=DBName;" & _
                 "user id=username;" & _
                 "password=password;"

'Create text file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\Documents and Settings\username\Desktop\output.txt", True)

'Create Array from SQL
Dim SQLArray    'SQLArray(Capacity, Available)
    
'Populate SQLArray
Set SQLStmt = CreateObject("ADODB.Command")
SQLStmt.CommandText = "select distinct system_id, capacity, available, cast(round(cast(available as decimal)/cast(capacity as decimal) * 100, 0) as integer) as FreeSpace from storage_info(nolock)"
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = objConn
objrs.Open SQLStmt
	SQLArray = objrs.GetRows(,,"FreeSpace")
objrs.Close

'Create Array from list
Dim SVRArray(4)
SVRArray(0) = "server1"
SVRArray(1) = "server2"
SVRArray(2) = "server3"
SVRArray(3) = "server4"
SVRArray(4) = "server5"

'Begin writing text file
objFile.Writeline ("Server Name" & vbTab & vbTab & vbTab & "Free Space")

Dim i, j
For i=0 to ubound(SVRArray,1)
	For j=0 to ubound(SQLArray,1)
		objFile.Writeline SVRArray(i) & SQLArray(j)
	Next
Next

'Clean up
objrs.Close
Set objrs = Nothing
Set objConn = Nothing

BS/Computer Science
 
Sort by date Sort by votes
Format your output to have a fixed length - same with your header. Before writing to the text file, append enough whitespace to the array data and take the left x chars (20 in the example below).

Code: 
intLength = 20
strServer = left(SVRArray(i) & space(intLength), intLength)


-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding
 
Upvote 0 Downvote
That is good information Geates but my biggest problem is getting the data from both arrays on the same line with the correct data. I'm having a hard time figuring this out and it should be simple.

BS/Computer Science
 
Upvote 0 Downvote
I'm not sure what your question is. Can you ellaborate?

With only two columns in your text file, there is only need to format one column. Apply the same process to the other columns - or use a basic function

Code: 
function fixStrLen(strValue, intLength)
   fixStrLen = left(strValue & space(intLength), intLength)
end function

then use the function to format your output

Code: 
objFile.WriteLine fixStrLen(SVRArray(i), 20) & fixStrLen(SQLArray(i), 10)

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding
 
Upvote 0 Downvote
This part of my code isn't working correctly. I get subscript out of range. How do I loop through both arrays in order to write the data to my text file? Below is how I thought it would work but it is not.

Code: 
Dim i, j
For i=0 to ubound(SVRArray,1)
    For j=0 to ubound(SQLArray,1)
        objFile.Writeline SVRArray(i) & SQLArray(j)
    Next
Next

BS/Computer Science
 
Upvote 0 Downvote
You're pretty close. Ubound returns the number of elements an array has. Because your loop starts i = 0 you need to substract 1 from the number of elements - or start your loop with i = 1

Code: 
For i = 0 to ubound(SVRArray,1)[red] - 1[/red]
    For j=0 to ubound(SQLArray,1)[red] - 1[/red]
        objFile.Writeline SVRArray(i) & SQLArray(j)
    Next
Next

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are selmon a good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon
 
Upvote 0 Downvote
Now the script is executing but it isn't writing anything to the text file. The text file now looks like this.

Code: 
Observe Server			Free Space

BS/Computer Science
 
Upvote 0 Downvote
post your code so I can look at it.

- Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are selmon a good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon
 
Upvote 0 Downvote
It isn't much different than before.

Code: 
Dim objConn, objrs, resultSet, SQLStmt
Dim objFSO, objFile

'Database Connection String
Set objConn = CreateObject("ADODB.Connection")
Set objrs = CreateObject("ADODB.Recordset")
    objConn.Open "Driver={SQL Server};" & _
                 "Server=SvrName\Instance;" & _
                 "Database=DBName;" & _
                 "user id=username;" & _
                 "password=password;"

'Create text file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\Documents and Settings\username\Desktop\output.txt", True)

'Create Array from SQL
Dim SQLArray    'SQLArray(Capacity, Available)
    
'Populate SQLArray
Set SQLStmt = CreateObject("ADODB.Command")
SQLStmt.CommandText = "select distinct system_id, capacity, available, cast(round(cast(available as decimal)/cast(capacity as decimal) * 100, 0) as integer) as FreeSpace from storage_info(nolock)"
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = objConn
objrs.Open SQLStmt
	SQLArray = objrs.GetRows(,,"FreeSpace")
objrs.Close

'Create Array from list
Dim SVRArray(4)
SVRArray(0) = "server1"
SVRArray(1) = "server2"
SVRArray(2) = "server3"
SVRArray(3) = "server4"
SVRArray(4) = "server5"

'Begin writing text file
objFile.Writeline ("Server Name" & vbTab & vbTab & vbTab & "Free Space")

Dim i, j
For i=0 to ubound(SVRArray,1) - 1
	For j=0 to ubound(SQLArray,1) - 1
		objFile.WriteLine fixStrLen(SVRArray(i), 20) & fixStrLen(SQLArray(j), 10)
	Next
Next

'Format Output
function fixStrLen(strValue, intLength)
   fixStrLen = left(strValue & space(intLength), intLength)
end function

'Clean up
objrs.Close
Set objrs = Nothing
Set objConn = Nothing

BS/Computer Science
 
Upvote 0 Downvote
I see the problem. The method .GetRows returns a two-dimensional array (rows and columns).
SQLArray = objrs.GetRows(,,"FreeSpace")

example of .GetRows return

Code: 
SQLArray = array(row1(col1, col2, col3)
                 row2(col1, col2, col3)
                 row3(col1, col2, col3))

Later on, you're trying to pass a whole row object to the function fixStrLen which operates on a string - not an object. Make sure to include the row and column.

Code: 
objFile.WriteLine fixStrLen(SVRArray(i), 20) & fixStrLen(SQLArray(j[red], 0[/red]), 10)

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom a good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon
 
Upvote 0 Downvote
Perfect! Here is the code that I changed and it works like a charm. I'm sure there is a better/easier way to do it but this works.

Code: 
Dim i, j, k
k=0
For i=0 to ubound(SVRArray,1)
	For j=0 to ubound(SQLArray,1)
		objFile.WriteLine fixStrLen(SVRArray(i), 32) & fixStrLen(SQLArray(j,k), 10)
		k=k+1
	Next
Next

BS/Computer Science
 
Upvote 0 Downvote
perhaps something a little more elegant? Eliminate the SQLArray and use objrs instead.

Include system_id in you recordset and filter freespace based on system_id.

Code: 
...

objrs.Open SQLStmt
[s]SQLArray = objrs.GetRows(,,"FreeSpace")
objrs.Close[/s]


...

For i=0 to ubound(SVRArray,1)
    For j=0 to ubound(SQLArray,1)
        [red]objrs.filter = "system_id = " & SVRArray(i)[/red]
        objFile.WriteLine fixStrLen(SVRArray(i), 32) & fixStrLen([red]objrs.fields(3)[/red], 10)
    Next
Next

Notice we had to use [red]objrs.field(3)[/red]. This is because FreeSpace is the 4th field (0-system_id, 1-capacity, 2-available, 3-FreeSpace) your sql query returns.

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon
 
Upvote 0 Downvote
That doesn't work because there is no longer a SQLArray.

Code: 
For j=0 to ubound(SQLArray,1)

BS/Computer Science
 
Upvote 0 Downvote
oh right, eliminate that for loop altogether

Code: 
For i=0 to ubound(SVRArray,1)
    objrs.filter = "system_id = " & SVRArray(i)
    objFile.WriteLine fixStrLen(SVRArray(i), 32) & fixStrLen(objrs.fields(3), 10)
Next

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon
 
Upvote 0 Downvote
One more question. In order to send the results in an email would I need to write to the text file and then read it to send it in an email? Or can I write directly to an email and send it?

BS/Computer Science
 
Upvote 0 Downvote
Code: 
function sendEmail (strTo, strFrom, strSubject, strBody)
   set objEmail = CreateObject("CDO.Message")
   objEmail.From = strFrom
   objEmail.To = strTo
   objEmail.Subject = strSubject
   objEmail.Textbody = strBody
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "fully.qualified.server.name"
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
   objEmail.Configuration.Fields.Update
   objEmail.Send
end function

sendEmail to@domain.ext, from@domain.ext, "test email", "This is a test email"

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon
 
Upvote 0 Downvote
Ok so I used this function but now the data that is sent in the email is not correct. How do I get the data to send in the strBody correctly?

BS/Computer Science
 
Upvote 0 Downvote
Never mind. Since I am no longer writing to the text file I needed to add a vbCr for a carriage return. Now it looks and works perfectly.

BS/Computer Science
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Rick_Stanich
  • Locked
  • Question
Save xls file to cvs file 1
Replies
3
Views
195
Rick_Stanich
Rick_Stanich
Palito1916
  • Locked
  • Question
Disable multiple computer accounts using TXT file with computer names
Replies
2
Views
184
Palito1916
Palito1916
PiotrMP006
  • Locked
  • Question
How to quickly check if a file has 1 or more lines of text?
Replies
1
Views
168
combo
combo
paullem
  • Locked
  • Question
vbscript to read properties of JPEG file
Replies
4
Views
305
paullem
paullem
Alexvb6
  • Question
Deprecation of VBScript + Classic ASP: How to Ask Microsoft to cancel their planned craziness
Replies
0
Views
75
Alexvb6
Alexvb6

Part and Inventory Search

Sponsor

Back
Top