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

Posted on 2003-03-14
Medium Priority
Last Modified: 2010-05-01
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!

Question by:jaschlueter
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
LVL 11

Expert Comment

ID: 8137422
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...

Author Comment

ID: 8138357
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.

Expert Comment

ID: 8139265
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...

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 8139982
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.

Accepted Solution

pauloaguia earned 2000 total points
ID: 8142251
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

LVL 11

Expert Comment

ID: 8142357
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
LVL 11

Expert Comment

ID: 8142369
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

Author Comment

ID: 8160512
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!

Expert Comment

ID: 8161500
You should test it before accepting the answer... I prefer to wait and have a right on answer...

But thanks for the points anyway :)

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month11 days, 22 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question