Friday, 18 October 2013

Extract Records with Multiple Columns by searching Full String or a Partial String on any Column in a Flat (Excel) File-Similar to VLOOKUP

How to get this Script

Please send Email with Subject "Extract Records with Multiple Columns" to for Zip File

Note: Customization can be done on Request


Extract Records with Multiple Columns by searching Full String or a Partial String on any Column in a Flat File

This is complex to explain in terms of Flat Files. So I am taking a Database Example and relate it with this VBScript in handling the Flat Files


Suppose we have an Employee Table EMP in Database which has Employee details (Multiple Records) and for each employee there are Columns such as EmpFirstName,EmpMiddleName, EmpLastName,EmpAddress,EmpCity,EmpState

 So if we need to identify Employees which has EmpFirstName containing


Then we write a Query as


EmpFirstName LIKE '%Ker%' OR 
EmpFirstName LIKE '%Joh%' OR 
EmpFirstName LIKE '%Mar%' OR
EmpFirstName LIKE '%Ros%'

Using the above Quey we can extract all the Records who have Names such as Kerry,John,Johnson,Mary,Mariah,Rosy,Rosannah etc

Challenge with Flat Files (Excel File)

So to acheive the same kind of Result from a flat File (Excel Sheet) is impossible as we cannot select the EmpFirstName Filter with Lot of Values with 

"Contains" Option as the Excel will allow only 2 Values with "Contains" Option

This VBScript and its Importance

So this VBScript is desgined for these kind of situations where we can extract Records with any number of Values with "Contains" Condition

How to Use it
1) Download the zip file (Please send Email with Subject "Extract Records with Multiple Columns"  to for Zip File)
2) Unzip the Files
3) Open the Original.xlsx File (I have just placed my sample data for explanation. You can have your Data in the same way with any number of Records and any number of Columns)
4) Simply Copy the Data from Excel (Just leave the Column Names) and directly Paste it in File2.txt. Save the File2.txt and Close it
5) In the File1.txt. Enter all the string Values which need to be searched. You can have the Complete String like "John" or Part of the String Like "Joh" or "oh" etc. Save the File1.txt and Close it
7) In the "Enter the Number of Columns" Popup. Enter the Total Number of Columns. In this Example we have 7 Columns. So we Enter "7"
8) In the "Enter the Column Position which has to be searched" Popup. Enter the Column in which the Search has to be Done. In this Example we are searching for Employee FirstNames which is in 1st Position. So we Enter "1"
9) VBScripts Starts the Execution and Completes the Execution which you will see as Messages on the Screen.
10) Now Open the OUTPUT.TXT File and you will see only those Records which have Complete EmpFirstName Matching or Partial EmpFirstName Records

Visit My Other Blogs

Developed By: Vijay Uppara (Vijaya Bhasker M Uppara)

Please send your comments or Feedback to


No comments:

Post a Comment