# Convert an interaction matrix into a ternary relation

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... Commented Feb 7, 2015 at 3:50
• Could the downvoter please explain what is wrong with the Q? Commented Feb 7, 2015 at 23:47

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'
fin = open (source_path, 'r', newline='')
fout = open (target_path, 'w', newline='')
writer = csv.writer (fout, delimiter=target_deli)
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()