MS Access 03 - Normalizing Data from an excel spreadsheet

Question

Hey guys I have a question about a method to break something apart. I get this excel spreadsheet that provides me with data that I need to do one report. Its pretty simple and straight forward, however there is one particular part of it that is giving me some grief.

In the excel spreadsheet there is a column that lists "parties envolved" in one column. it usually is about 12 people's names seperated by commas, but also has orgID in parenthesis behind it:

joe smith (DIV32), john doe (DIV12), roger andrews (DIV14, DIV67, DIV01), etc

and I need to break these into individual columns so that they will be individual fields once i import them into access. I know how to "text to columns" in excel, but this get screwed up when jon doe (DIV13, DIV54, etc), has more than one division.

not sure how to do this in access but would love to know.

anyone got either an excel forumla, or access method for this please?

thanks

Answer 1

I assume you are importing this into your database. If not, it is probably easier if you do, even if it's temporary; and redone every time you have to run the report

You are going to end up having three tables to represent this situation

1. theParty (person)
2. theOrganisation
3. thePartyOrg

theParty with have an ID column

theOrganisation will have it's own ID column

thePartyOrder will have it's own ID column, one for theParty, and one for theOrganisation

Whenever you want to represent a Party as bring a member of and Organisation, you have to make sure the Party exists/is created; the Organisation exists/is created, and hen create an entry in the thePartyOrg table which points at both.

Because this connection information just stored as text within a single column, it's probably a easiest to first read it all into a staging table and then parse that column in VBA

Answer 2

Here's a solution:

I've written a function that replaces all occurrences of a string strFind by strReplace, but only if strFind occurs within parentheses. So you can replace all of the "," characters by something else (e.g. "*"), then run Excel's text to columns, then replace the "*"'s with "," again.

Function replace_paren(strSource As String, strFind As String, strReplace As String) As String
    ' Within strString, replaces any occurrence of strFind with strReplace *IF* strFind occurs within parentheses '

    Dim intOpenParenIndex As Integer
    Dim intCloseParenIndex As Integer

    Do
        intOpenParenIndex = InStr(intCloseParenIndex + 1, strSource, "(")
        intCloseParenIndex = InStr(intOpenParenIndex + 1, strSource, ")")
        If intOpenParenIndex = 0 Then
            Exit Do
        End If

        Mid(strSource, intOpenParenIndex, intCloseParenIndex - intOpenParenIndex) = Replace(Mid(strSource, intOpenParenIndex, intCloseParenIndex - intOpenParenIndex), strFind, strReplace)
    Loop

    replace_paren = strSource

End Function

So the steps are:

1) copy this macro into a module in your Excel workbook

2) Let's say your string is in column A. In column B, set up the function to replace the commas like this

=replace_paren(A1,",","*")

3) Fill the formula down the column

4) Copy and paste the column as values

5) Use Excel's text to columns to parse the column using "," as a delimiter

6) Use replace_paren again to replace all occurrences of "*" by ","