Check each separate word/or value in each field in each recordset

I need to write a function that can will read each recordset from either an Excel file or a Text file and perform value checking for each word within each field of the recordset. The trick is that I need to reassemble the input file in the same format which it was given while using the values themselves (fields not the tokens). The other function that I am performing is value comparisons against another database in which I will replace a given value with another if it encounters a match.

I cannot compare an entire field from within the record set as they may contain sentences for which it is the individual words that I am interested in comparing and changing.

For example:

This is a single recordset that I will encounter...

item      description1       description2        description?? ...
ABC       APPLEZ for me      ORANGEZ for you     PEARZ for everyone

I need to "clean" each word against a database I am matching each word against. If it finds a match, I will replace it will it's association in that database. Then return the new information back in the same format will maintaining control over each field so I can update separate databases.

So I should get:

item      description1       description2        description?? ...
ABC       APPLES for me      ORANGES for you     PEARS for everyone

The important thing is that I am able to return the same record(s) CLEANED to an output file(excel and/or tab delimited text) and "item" and "description1" to one database and "description2" and "description??" until the end of the field set to another.

Good luck you fabulous programmers!

Who is Participating?
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.

Ok... More info please ....

How do you know an APPLEZ is an APPLES ?

Will each field be checked (incl. ITEM) or only every field after item?

'Pseude Code

oInput = Input textstream
oOut = Output textstream

dim arLine() as string
while not oInput.atendofstream
  arLine = split (oInput.readline,vbtab)
  for i = 0 (or 1) to ubound(arLine)
    arLine(i) = checkWordFunction(arline(i)) 'returns APPLES for APPLEZ

Hope this helps ;)

Exceptions are not included...
jaschlueterAuthor Commented:
Here is a bit more clarification:

I have a separate table in which each row contains both a list of possible values and an approved associated value. It looks something like this:

PossibleValues       ApprovedValue
appils, APPLEZ       APPLES
oringins, ORANGEZ    ORANGES

So I need to check each field in the record set with values associated in the "Possible Values" column and replace each occurence (there may be more than one of each or the string may contain hits on each row in the database.)

Remember each field needs to be retained in the recordset but each word within each field need to be checked against every record in the table that I described above.
This is VBA right? Can you import the two data tables to Excel? (as two separate ranges)

Or do you also need help with this part?

Just to know where do I need to start...

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jaschlueterAuthor Commented:
While I am ultimately going to write a VB function for this, a VBA function for use in Access is alright for now. I cannot use an Excel macro for this project.
Sorry. Your first reference to Excel triggered my VBA senses :)

Anyway, I compiled a VBA procedure in Access to do what you wanted. It creates an Excel file where each row takes the replaced record from the database. Take a look and see if you can make sense out of it:

Option Compare Database
Option Explicit

Sub ExportApproved()
    Dim rsApproved As DAO.Recordset
    Dim rs As DAO.Recordset
    Dim colApproved As Collection
    Dim fld As DAO.Field
    Dim strPossible As String, strApproved As String
    Dim strWords As String, strWord As Variant
    Dim str As String
    Dim Combination As Variant
    Dim intRow As Long, intCol As Integer
    Dim XLapp As Excel.Application
    Dim wbk As Excel.Workbook
    Dim ws As Excel.Worksheet
    Set colApproved = New Collection
    Set rsApproved = Application.CurrentDb.OpenRecordset("SELECT * FROM Aprovements")
    Set rs = Application.CurrentDb.OpenRecordset("SELECT * FROM Table1")
    Do While Not rsApproved.EOF
        strWords = rsApproved("PossibleValues")
        For Each strWord In Split(strWords, ",")
            colApproved.Add Array(strWord, CStr(rsApproved("AprovedValues")))
    Set XLapp = New Excel.Application
    Set wbk = XLapp.Workbooks.Add
    Set ws = wbk.Worksheets(1)
    intRow = 1
    Do While Not rs.EOF
        intCol = 1
        For Each fld In rs.Fields
            str = fld.Value
            For Each Combination In colApproved
                strPossible = Trim(Combination(LBound(Combination)))
                strApproved = Trim(Combination(UBound(Combination)))
                str = Replace(str, strPossible, strApproved, 1, -1, vbTextCompare)
            ws.Cells(intRow, intCol).Value = str
            intCol = intCol + 1
        intRow = intRow + 1
    wbk.SaveAs "C:\test.xls"
    Set XLapp = Nothing
    Set colApproved = Nothing
End Sub

Of course, for this to work, you need to add References to Microsoft Excel object library and Microsoft ADO object library (under Tools->References)

Hope this helps


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
Ok since my pseudo code was only missing the replace function try this ;)

appils, APPLEZ       APPLES
oringins, ORANGEZ    ORANGES
dim oDic as new dictionary 'Form wide dictionary object

private sub fillDic
dim arWords() as string
'get RS from somewhere
while not oRS.eof
 arWords = split(oRS(0).value, ", ")
 for i = 0 to ubound(arWords)
end sub

private function checkWordFunction(cIN as string ) as string
if odic.exists(Cin) then
  checkWordFunction = odic(cin)
  checkWordFunction = cin
end if

end function

'end code

I assume you know how to get the data from the DB so i left this code out...

To use the dictionary object you need at add a refference to the scripting runtime to your project... Its an improved collection and it is very fast also ;)

I hope this helps
P.s: Just noticed you have a sentence per field and not a word per field ;)

change my function in this way

private function checkWordFunction(cIN as string ) as string
dim cLine() as string
dim i as integer
cLine = split(cIn, " ")
for i = 0 to ubound(cLine)
if odic.exists(cline(i)) then
 cline(i)= odic(cline(i))
end if
checkWordFunction = join(cline," ")
end function
jaschlueterAuthor Commented:
I haven't been able to test this yet as my versions of VB are currently being updated, but it looks right on the money.

Thanks again!
You should test it before accepting the answer... I prefer to wait and have a right on answer...

But thanks for the points anyway :)
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
Visual Basic Classic

From novice to tech pro — start learning today.

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.