[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

looking for vbscript for reading text file info then compare excel columns and write back to another text file.

Posted on 2011-03-24
10
Medium Priority
?
516 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:aev2061
  • 5
  • 5
10 Comments
 
LVL 59

Expert Comment

by:Bill Prew
ID: 35212191
Approximately how many rows will be in the Excel file?

Are there any header rows in the Excel file?

Can you provide sample files for testing?

~bp
0
 

Author Comment

by:aev2061
ID: 35212421
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
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 35212481
So are you saying that the script would actually have to search all tabs in the excel file?

~bp
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:aev2061
ID: 35212494
Yes.
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 35218612
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.

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

Open in new window

~bp
0
 

Author Comment

by:aev2061
ID: 35219868
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!
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 35220064
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

Open in new window

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

Open in new window

~bp
0
 

Author Comment

by:aev2061
ID: 35220135
Cool!... Thanks so very much...
0
 
LVL 59

Accepted Solution

by:
Bill Prew earned 2000 total points
ID: 35220145
I think the poster has closed this question wrong, and likely meant to accept my last post as the solution.

~bp
0
 

Author Closing Comment

by:aev2061
ID: 35220225
That is correct... I accepted the solution.. and I did add this to the knowledge base..
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question