How to get this Script
Please send Email with Subject "Extract Records with Multiple Columns" to vijkid143@gmail.com for Zip File
Note: Customization can be done on Request
Description
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
Database
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
Ker
Joh
Mar
Ros
Then we write a Query as
SELECT * FROM EMP
WHERE
(
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 vijkid143@gmail.com 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
6) Now Run the VBScript "EXTRACT SPECIFIC RECORDS BY SEARCHING DIFFERENT STRINGS ON ANY COLUMN.vbs"
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
http://exceljoins.blogspot.in/
http://wordcount-vbscript.blogspot.com/
Developed By: Vijay Uppara (Vijaya Bhasker M Uppara)
Please send your comments or Feedback to vijkid143@gmail.com
Please send Email with Subject "Extract Records with Multiple Columns" to vijkid143@gmail.com for Zip File
Note: Customization can be done on Request
Description
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
Database
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
Ker
Joh
Mar
Ros
Then we write a Query as
SELECT * FROM EMP
WHERE
(
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 vijkid143@gmail.com 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
6) Now Run the VBScript "EXTRACT SPECIFIC RECORDS BY SEARCHING DIFFERENT STRINGS ON ANY COLUMN.vbs"
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
http://exceljoins.blogspot.in/
http://wordcount-vbscript.blogspot.com/
Developed By: Vijay Uppara (Vijaya Bhasker M Uppara)
Please send your comments or Feedback to vijkid143@gmail.com
No comments:
Post a Comment