0

I have what I'm calling a sparse "interaction matrix" of data in MS Excel that I wish to convert into a "ternary relation" (table) in Access.

The spreadsheet data are in this pattern:

Name   Act-1  Act-2  Act-3  ... Act-n
name-1         week
name-2                week
name-3  week                     week
  :                         week
name-m         week   week

There are n activities as Excel headings, m names in the first column of each data row, and zero or more specific weeks entered wherever a person (name) did an activity. If the n were small and fixed, I could keep the relation as a matrix, but n can get quite large and the matrix will be very sparse.

Thus the desired relational table structure (I'll call it Assignment) is like this:

Assignment: Name, Activity, Week

Is there an easy tool in either Excel or Access for facilitating this data conversion?

A psuedocode algorithm would be

for each Name (row)
    for each Activity (column)
        if Week, add Assignment: Name, Activity, Week

While I've coded in VBA (years ago), I've never used it in conjunction with Excel. I'm leaning towards using Python and CSV files. Maybe there's a good SQL technique (even if non-Access-specific)? Or is it something to do with "pivoting" (I never understood what that was about)?

Do you have any experience you'd like to share with this sort of conversion?

  • Perhaps this question is better suited to SO, since it is more about data structure conversion than explicit SQL? Although i'm new here, i'm a little surprised by the lack of interest... – Martin F Feb 7 '15 at 3:50
  • Could the downvoter please explain what is wrong with the Q? – Martin F Feb 7 '15 at 23:47
0

My solution is to save the spreadsheet as a tab-delimited "matrix" file, run the following "data structure conversion" program, then import the resulting tab-delimited "relation" file into Access.

The Python 3.4 code:

import csv
source_path = 'matrix.txt'
target_path = 'relation.txt'
source_deli = '\t'
target_deli = '\t'
target_head = ("Name", "Activity", "Week")
fin = open (source_path, 'r', newline='')
fout = open (target_path, 'w', newline='')
reader = csv.DictReader (fin, delimiter=source_deli)
writer = csv.writer (fout, delimiter=target_deli)
writer.writerow (target_head)
for row in reader:
    for key in row:
        if key != "Name" and row[key]:
            target_row = (row["Name"], key, row[key])
            writer.writerow (target_row)
fin.close()
fout.close()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.