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
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.
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?