How to read in server name, software name and version from a text file

[cp713a]

Hi,

I have a huge file contains servers, software name and its version. The data is a text file and I'd like to read in for each of the name of the Server , software and software version. I need it to loop through the entire text file until eof.

For ex:
Serv1
sw1
ver1
sw1_1
ver1_1
sw1_1_1_1
ver1_1_1
Serv2
sw2
ver2
sw2_2
ver2_2
sw2_2_2_2
ver2_2_2
Serv3
sw3
ver3
sw3_3
ver3_3
sw3_3_3_3
ver3_3_3
Serv4
sw4
ver4
sw4_4
ver4_4
sw4_4_4_4
ver4_4_4

The output will list all the servers as the header in a column and below each row are the sw, ver...etc...like this output:

Serv1 Serv2 Serv3 Serv4
swswname swname swname swname
ver ver2 ver3 ver4
swname sw2_2 sw3_3 sw4_4
ver_ ver2_2 ver3_3 ver4_4
swname _swname _swname_swname
vers_ swname _swname ver2_2_2

each time it search for all the software and version listed under the Serv, then moves on to the next server and read all the sw and version until it reaches another Server etc...

Hope it makes sense

[SamT]

Assumes list is currently in column "A" of Excel Sheet. Code goes in Worksheet Code page


Sub TransposeBySeven()
Dim Rw as Long, Col As Long
Col = 3
Application.ScreenUpdating = False

For Rw = 1 To Cells(Rows.Count, "A").End(xlUp).Row Step 7
Cells(Rw, "A").Resize(1, 7).Copy Cells(1, Col)
Col = Col + 2
Next Rw

Application.ScreenUpdating = True
End Sub

[snb]

What makes sense is a sample file in which you enter 5 lines of the desired result manually.
And a piece of the sample txt file.

[cp713a]

Assumes list is currently in column "A" of Excel Sheet. Code goes in Worksheet Code page


Sub TransposeBySeven()
Dim Rw as Long, Col As Long
Col = 3
Application.ScreenUpdating = False

For Rw = 1 To Cells(Rows.Count, "A").End(xlUp).Row Step 7
Cells(Rw, "A").Resize(1, 7).Copy Cells(1, Col)
Col = Col + 2
Next Rw

Application.ScreenUpdating = True
End Sub

SamT, thank you for your reply.

Your code, it spits out in one long row instead of separate columns with Server1, Server2, Server3...etc as the headers for each column. Also, if this explanation help...is to read server name, name of the sw and its version until it reaches the next servername then make it the next column header. So, the key i'm looking at is to find the next servername and start a new column.

Something like this:

Server: A
sw name
ver
sw name
ver
sw name
ver
sw name
ver
etc
etc
etc
Server B
sw name
ver
sw name
ver
sw name
ver
sw name
ver
etc
etc
etc


Output:
Server: A Server B
sw name sw name
ver ver
sw name sw name
ver ver
sw name sw name
ver ver
sw name sw name
ver ver
etc etc
etc etc
etc etc
it dataset can be over 100+ long or could be less than 20 for each server...it depends, so something like a for-loop to read until the next servername.

[SamT]

:banghead:
My "Resize" is bassackwards. Habit :bug:

Resize(7, 1)

For Rw to End Step 7 is like a For loop

[arnelgp]

you may also try this.
oooop... saw the date, that was 3 Months ago...arghhh!!!

[Ytrt]

:banghead:
My "Resize" is bassackwards. Habit :bug:

Resize(7, 1)

For Rw to End Step 7 is like a For loop
Agree