• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

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.
0
OnsiteSupport
Asked:
OnsiteSupport
  • 4
  • 3
1 Solution
 
Bill PrewCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Bill PrewCommented:
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 PrewCommented:
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
 
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 PrewCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now