Matcbing two tables

I have a customer table and a phone number table that is a subset of customer table. . This is created via sms text responses dumping into a csv.

Customer
Phone,  fname,  lname,  addr1, addr2, city,  state,  zip, route

Phone number table
Phone,  response

I need to do a simple compare and return a results table that contains the matching address records.  

Can this be done in a vb script?  Trying to keep this in a csv without using excel.
OnsiteSupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Yes, can be done in VBS.  I'm out of time tonight, but can provide a solution tomorrow if none shows up.

Can you provide a sample of the two input files (with examples of matching, and nonmatching data), and then a sample of the output you want the script to generate for those test files?  That would be very helpful.

~bp
0
OnsiteSupportAuthor Commented:
Incoming-Messages-1.csv is a file of sms text responses that contains the customer phone number that should cross referrence the Customer file.

incoming texts just a subset of Customers.

hope this is enough information.   Probably simple enough for anyone other than myself who never codes :)

Thanks again.
Craig
Customers.csv
incoming-messages-1.csv
0
OnsiteSupportAuthor Commented:
The output file should customer recs whose phone number is in incoming message file.
0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Bill PrewIT / Software Engineering ConsultantCommented:
There will not be a header on the customers file, correct?

And I assume that if a phone number appears in multiple messages, you only want one customer rec output for that?

~bp
0
Bill PrewIT / Software Engineering ConsultantCommented:
Based on what you provided this should do the job.  Save as a VBS file, and run as follows:

cscript EE28669197.vbs files\customers.csv files\messages.csv files\output.csv

Specifying the files that you want to process, customer, then messages, and the output file for the results.

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TristateTrue = -1
Const TristateFalse = 0
Const TristateUseDefault = -2

' Get input parms
If (WScript.Arguments.Count > 0) Then
   strCustomerFile = WScript.Arguments(0)
Else
   DisplayUsage
   WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
   strMessagesFile = WScript.Arguments(1)
Else
   DisplayUsage
   WScript.Quit
End If
If (WScript.Arguments.Count > 2) Then
   strOutputfile = WScript.Arguments(2)
Else
   DisplayUsage
   WScript.Quit
End If

' Set up objects needed
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objCustomerDict = CreateObject("Scripting.Dictionary")
Set objOutputDict = CreateObject("Scripting.Dictionary")

' Open customer file
On Error Resume Next
Set objCustomerFile = objFSO.OpenTextFile(strCustomerFile, ForReading)
If Err.Number <> 0 Then 
   ShowError "Error opening customer file.", "Filename = " & strCustomerFile
   Wscript.Quit
End If
On Error Goto 0
 
' Open messages file
On Error Resume Next
Set objMessagesFile = objFSO.OpenTextFile(strMessagesFile, ForReading)
If Err.Number <> 0 Then 
   ShowError "Error opening messages file.", "Filename = " & strMessagesFile
   Wscript.Quit
End If
On Error Goto 0
 
' Open output file
On Error Resume Next
Set objOutputFile = objFSO.OpenTextFile(strOutputFile, ForWriting, True)
If Err.Number <> 0 Then 
   ShowError "Error opening output file.", "Filename = " & strOutputFile
   Wscript.Quit
End If
On Error Goto 0
 
' Load entries from customer file into dictionary for lookup later
intLine = 0
strHeader = ""
Do Until objCustomerFile.AtEndOfStream
   intLine = intLine + 1
   strLine = objCustomerFile.Readline

   ' Grab header line from first record (SKIPPED CURRENTLY ASSUMING NO HEADER LINE)
   If intLine = 0 Then
      strHeader = strLine
   Else
      ' Skip blank lines
      If strLine <> "" Then
         arrLine = Split(strLine, ",")
         ' Add this mapping to the dictionary (new, or extend existing entry)
         If objCustomerDict.Exists(arrLine(0)) Then
            Wscript.Echo "Duplicate phone number [" & arrLine(0) & "] found on line [" & intLine & "] in customer file, skipped."
         Else
            objCustomerDict.Add arrLine(0), strLine
         End If
      End If
   End If
Loop
 
objCustomerFile.Close
 
' Read messages from message file
intLine = 0
Do Until objMessagesFile.AtEndOfStream
   intLine = intLine + 1
   strLine = objMessagesFile.Readline

   ' Skip header line from first record
   If intLine > 1 Then
      ' Skip blank lines
      If strLine <> "" Then
         arrLine = Split(strLine, ",")
         If objOutputDict.Exists(arrLine(1)) Then
            ' If we already output this line then skip it
         Else
            ' If we have a customer record for this phone save it for dupe checking, and output it
            If objCustomerDict.Exists(arrLine(1)) Then
               objOutputFile.WriteLine objCustomerDict.Item(arrLine(1))
               objOutputDict.Add arrLine(1), 1
            End If
         End If
      End If
   End If
Loop
 
' Done
objMessagesFile.Close
objOutputFile.Close

Sub ShowError(strLocation, strMessage)
   WScript.StdErr.WriteLine "==> ERROR at [" & strLocation & "]"
   WScript.StdErr.WriteLine "    Number:[" & Err.Number & "], Source:[" & Err.Source & "], Desc:[" &  Err.Description & "]"
   WScript.StdErr.WriteLine "    " & strMessage
   Err.Clear
End Sub

Sub DisplayUsage()
   WScript.Echo "Missing parms."
   WScript.Echo "Usage: CSCRIPT " & Wscript.ScriptName & " [customer-file] [messages-file] [output-file]"
End Sub

Open in new window

~bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OnsiteSupportAuthor Commented:
This is perfect.  I also like how it is robust enough to change the Customer file.  I'm assuming that phone number has to be in the first column?  Since I don't have the real customer file (my customer is being slow getting it to me.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Here's a slightly updated version that makes it easier to change the columns where the phone number is located in each file.  Makes the code a little more efficient and readable inside the loops too.  Let me know if this doesn't make sense.

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TristateTrue = -1
Const TristateFalse = 0
Const TristateUseDefault = -2

' Define what column the phone number is located in in each file (relative to 0)
Const CustomerPhoneColumn = 0
Const MessagesPhoneColumn = 1

' Get input parms
If (WScript.Arguments.Count > 0) Then
   strCustomerFile = WScript.Arguments(0)
Else
   DisplayUsage
   WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
   strMessagesFile = WScript.Arguments(1)
Else
   DisplayUsage
   WScript.Quit
End If
If (WScript.Arguments.Count > 2) Then
   strOutputfile = WScript.Arguments(2)
Else
   DisplayUsage
   WScript.Quit
End If

' Set up objects needed
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objCustomerDict = CreateObject("Scripting.Dictionary")
Set objOutputDict = CreateObject("Scripting.Dictionary")

' Open customer file
On Error Resume Next
Set objCustomerFile = objFSO.OpenTextFile(strCustomerFile, ForReading)
If Err.Number <> 0 Then 
   ShowError "Error opening customer file.", "Filename = " & strCustomerFile
   Wscript.Quit
End If
On Error Goto 0
 
' Open messages file
On Error Resume Next
Set objMessagesFile = objFSO.OpenTextFile(strMessagesFile, ForReading)
If Err.Number <> 0 Then 
   ShowError "Error opening messages file.", "Filename = " & strMessagesFile
   Wscript.Quit
End If
On Error Goto 0
 
' Open output file
On Error Resume Next
Set objOutputFile = objFSO.OpenTextFile(strOutputFile, ForWriting, True)
If Err.Number <> 0 Then 
   ShowError "Error opening output file.", "Filename = " & strOutputFile
   Wscript.Quit
End If
On Error Goto 0
 
' Load entries from customer file into dictionary for lookup later
intLine = 0
strHeader = ""
Do Until objCustomerFile.AtEndOfStream
   intLine = intLine + 1
   strLine = objCustomerFile.Readline

   ' Grab header line from first record (SKIPPED CURRENTLY ASSUMING NO HEADER LINE)
   If intLine = 0 Then
      strHeader = strLine
   Else
      ' Skip blank lines
      If strLine <> "" Then
         arrLine = Split(strLine, ",")
         ' Add this mapping to the dictionary (new, or extend existing entry)
         strCustomerPhoneNumber = arrLine(CustomerPhoneColumn)
         If objCustomerDict.Exists(strCustomerPhoneNumber) Then
            Wscript.Echo "Duplicate phone number [" & strCustomerPhoneNumber & "] found on line [" & intLine & "] in customer file, skipped."
         Else
            objCustomerDict.Add strCustomerPhoneNumber, strLine
         End If
      End If
   End If
Loop
 
objCustomerFile.Close
 
' Read messages from message file
intLine = 0
Do Until objMessagesFile.AtEndOfStream
   intLine = intLine + 1
   strLine = objMessagesFile.Readline

   ' Skip header line from first record
   If intLine > 1 Then
      ' Skip blank lines
      If strLine <> "" Then
         arrLine = Split(strLine, ",")
         strMessagesPhoneNumber = arrLine(MessagesPhoneColumn)
         If objOutputDict.Exists(strMessagesPhoneNumber) Then
            ' If we already output this line then skip it
         Else
            ' If we have a customer record for this phone save it for dupe checking, and output it
            If objCustomerDict.Exists(strMessagesPhoneNumber) Then
               objOutputFile.WriteLine objCustomerDict.Item(strMessagesPhoneNumber)
               objOutputDict.Add strMessagesPhoneNumber, 1
            End If
         End If
      End If
   End If
Loop
 
' Done
objMessagesFile.Close
objOutputFile.Close

Sub ShowError(strLocation, strMessage)
   WScript.StdErr.WriteLine "==> ERROR at [" & strLocation & "]"
   WScript.StdErr.WriteLine "    Number:[" & Err.Number & "], Source:[" & Err.Source & "], Desc:[" &  Err.Description & "]"
   WScript.StdErr.WriteLine "    " & strMessage
   Err.Clear
End Sub

Sub DisplayUsage()
   WScript.Echo "Missing parms."
   WScript.Echo "Usage: CSCRIPT " & Wscript.ScriptName & " [customer-file] [messages-file] [output-file]"
End Sub

Open in new window

~bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.