aev2061
asked on
looking for vbscript for reading text file info then compare excel columns and write back to another text file.
I've been searching around but thought I would ask.
I'm looking for a way to loop through a text file (servernames) and find the same name
in an excel file. Once the name is found (like in column B) then jump over to a certain column
(like column E) and take that info (column E) and write it back into another text file.
Thanks
I'm looking for a way to loop through a text file (servernames) and find the same name
in an excel file. Once the name is found (like in column B) then jump over to a certain column
(like column E) and take that info (column E) and write it back into another text file.
Thanks
ASKER
Well there is no way to control that. It could be 20 or 200. One thing I also have is multiple worksheets (tabs). See an example xls I uploaded.
I'm looking to read the servername in the text file then find the server in column B (who knows which tab) and once the server is found output Column E to another text file.
Inventory.xls
I'm looking to read the servername in the text file then find the server in column B (who knows which tab) and once the server is found output Column E to another text file.
Inventory.xls
So are you saying that the script would actually have to search all tabs in the excel file?
~bp
~bp
ASKER
Yes.
Okay, here's a VBS approach, save as a VBS file and adjust the paths at the top.
One thing I'm not clear on is if the server names can be the same on different tabs. In your example they were, but if that is true then how would be know which one to match to the lines read from the servers text file? I'm think you may have just done that for quick sample data, but let me know.
One thing I'm not clear on is if the server names can be the same on different tabs. In your example they were, but if that is true then how would be know which one to match to the lines read from the servers text file? I'm think you may have just done that for quick sample data, but let me know.
Option Explicit
' Define Needed constants
Const cForReading = 1
Const cForWriting = 2
Const xlUp = -4162
Const cExcelFile = "X:\EE\EE26910401\Inventory.xls"
Const cTextFile = "X:\EE\EE26910401\servers.txt"
Const cOutFile = "X:\EE\EE26910401\merged.txt"
' Define needed variables
Dim oFSO, oExcel, oMaster, oSheet, oInFile, oOutFile, dCount, iSheet, iRow, sKey, sData
' Create file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")
' Create dictionary to load excel data into
Set dCount = CreateObject("Scripting.Dictionary")
' Open Excel worksheet
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
Set oMaster = oExcel.Workbooks.Open(cExcelFile, False, False)
' Load data from all sheets in excel workbook
For iSheet = 1 To oMaster.Sheets.Count
Set oSheet = oMaster.Sheets(iSheet)
For iRow = 2 To oSheet.Cells(65536, "B").End(xlUp).Row
sKey = oSheet.Cells(iRow, "B").Value
sData = oSheet.Cells(iRow, "E").Value
if dCount.Exists(sKey) Then
' What do we do about duplicates???
Else
dCount.Add sKey, sData
End If
Next
Next
oExcel.Quit
' Process list of servers from text file
Set oInFile = oFSO.OpenTextFile(cTextFile, cForReading)
Set oOutFile = oFSO.OpenTextFile(cOutFile, cForWriting, True)
' Read input lines, look for matching key from excel data, output corresponding data
Do Until oInFile.AtEndOfStream
sKey = oInFile.Readline
If dCount.Exists(sKey) Then
oOutFile.WriteLine sKey & "," & dCount.Item(sKey)
Else
oOutFile.WriteLine sKey & ",MISSING"
End If
Loop
' Close input and output files
oInFile.Close
oOutFile.Close
~bp
ASKER
No, the servers will be different from tab to tab and yes, It was a quick example.
So looking forward once the script found a match how would I grab 2 or more items to pull out into the merged.txt? Would I just add in another sData = oSheet.Cells(iRow, "Whatever Row I needed").Value
Also this is great. I can't thank you enough!
So looking forward once the script found a match how would I grab 2 or more items to pull out into the merged.txt? Would I just add in another sData = oSheet.Cells(iRow, "Whatever Row I needed").Value
Also this is great. I can't thank you enough!
To pull data from multiple columns just concatenate the values together as in this example:
sData = oSheet.Cells(iRow, "E").Value & "," & _
oSheet.Cells(iRow, "A").Value & "," & _
oSheet.Cells(iRow, "C").Value
This will output the values from columns E, A and C for each server, as in:Server1,1-4,2,Dell
Server2,1-5,3,Dell
Server3,1-6,4,HP
Server4,1-7,5,Compaq
Server5,1-8,6,Dell
Server6,1-9,7,Dell
Server7,1-10,8,HP
Server8,1-11,9,Compaq
Server9,1-12,10,Dell
Server10,1-13,11,Dell
~bpASKER
Cool!... Thanks so very much...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is correct... I accepted the solution.. and I did add this to the knowledge base..
Are there any header rows in the Excel file?
Can you provide sample files for testing?
~bp