OnsiteSupport
asked on
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.
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.
ASKER
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
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
ASKER
The output file should customer recs whose phone number is in incoming message file.
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
And I assume that if a phone number appears in multiple messages, you only want one customer rec output for that?
~bp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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