Skip to main content
simplified code via DictReader
Source Link
Martin F
  • 311
  • 1
  • 3
  • 15

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.readerDictReader (fin, delimiter=source_deli)
writer = csv.writer (fout, delimiter=target_deli)
r = 0
for row in reader:
    if not r:
        head = row
#        print (target_head)
        writer.writerow (target_head)
 for row in elsereader:
        c = 0
        for valkey in row:
            if not c:
                casekey != val
            else:
               "Name" ifand valrow[key]:
                    target_row = (caserow["Name"], head[c]key, val)
#                    print (target_rowrow[key])
                    writer.writerow (target_row)
            c = c+1
    r = r+1
fin.close()
fout.close()

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.reader (fin, delimiter=source_deli)
writer = csv.writer (fout, delimiter=target_deli)
r = 0
for row in reader:
    if not r:
        head = row
#        print (target_head)
        writer.writerow (target_head)
    else:
        c = 0
        for val in row:
            if not c:
                case = val
            else:
                if val:
                    target_row = (case, head[c], val)
#                    print (target_row)
                    writer.writerow (target_row)
            c = c+1
    r = r+1
fin.close()
fout.close()

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()
made code output headings match those in Question
Source Link
Martin F
  • 311
  • 1
  • 3
  • 15

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 = ("case""Name", "variable""Activity", "value""Week")
fin = open (source_path, 'r', newline='')
fout = open (target_path, 'w', newline='')
reader = csv.reader (fin, delimiter=source_deli)
writer = csv.writer (fout, delimiter=target_deli)
r = 0
for row in reader:
    if not r:
        head = row
#        print (target_head)
        writer.writerow (target_head)
    else:
        c = 0
        for val in row:
            if not c:
                case = val
            else:
                if val:
                    target_row = (case, head[c], val)
#                    print (target_row)
                    writer.writerow (target_row)
            c = c+1
    r = r+1
fin.close()
fout.close()

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 = ("case", "variable", "value")
fin = open (source_path, 'r', newline='')
fout = open (target_path, 'w', newline='')
reader = csv.reader (fin, delimiter=source_deli)
writer = csv.writer (fout, delimiter=target_deli)
r = 0
for row in reader:
    if not r:
        head = row
#        print (target_head)
        writer.writerow (target_head)
    else:
        c = 0
        for val in row:
            if not c:
                case = val
            else:
                if val:
                    target_row = (case, head[c], val)
#                    print (target_row)
                    writer.writerow (target_row)
            c = c+1
    r = r+1
fin.close()
fout.close()

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.reader (fin, delimiter=source_deli)
writer = csv.writer (fout, delimiter=target_deli)
r = 0
for row in reader:
    if not r:
        head = row
#        print (target_head)
        writer.writerow (target_head)
    else:
        c = 0
        for val in row:
            if not c:
                case = val
            else:
                if val:
                    target_row = (case, head[c], val)
#                    print (target_row)
                    writer.writerow (target_row)
            c = c+1
    r = r+1
fin.close()
fout.close()
Source Link
Martin F
  • 311
  • 1
  • 3
  • 15

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 = ("case", "variable", "value")
fin = open (source_path, 'r', newline='')
fout = open (target_path, 'w', newline='')
reader = csv.reader (fin, delimiter=source_deli)
writer = csv.writer (fout, delimiter=target_deli)
r = 0
for row in reader:
    if not r:
        head = row
#        print (target_head)
        writer.writerow (target_head)
    else:
        c = 0
        for val in row:
            if not c:
                case = val
            else:
                if val:
                    target_row = (case, head[c], val)
#                    print (target_row)
                    writer.writerow (target_row)
            c = c+1
    r = r+1
fin.close()
fout.close()