convert excel/word hierarchical column to microsoft word list

user_10773710 0
Viewed 40

I have some base data that looks like this:

Parent  Sub A   AAB
Parent  Sub A   AAU
Parent  Sub B   AAP
Parent  Sub B   AA1
Parent  Sub B   AAK

I would like to convert to a hierarchical list in word format like this:

desired result

I have tried recording a macro to do this but can't get it to work. My other thought is some kind of command prompt on a text file but not sure where to start with that.

Any help would be appreciated thanks.

ms-word vba excel python
1 Answers

Let's say you have a .txt file containing the following:

Parent  Sub A   AAB
Parent  Sub A   AAU
Parent  Sub B   AAP
Parent  Sub B   AA1
Parent  Sub B   AAK

Then, you can run the following Python script:

# Open the file data.txt and read line by line
with open('data.txt', 'r') as f:
    # Create an empty list
    data = []
    # Read the file line by line
    for line in f:
        # Remove the newline character and add the line to the list
        temp = line.rstrip()

        # Split the temp string into a list, except for second element and third element
        temp = temp.split()
        # Concatenate second and third element and make it the second element
        temp[1] = temp[1] + ' ' + temp[2]
        del temp[2]
        data.append((temp))

structure = {}

def add_to_structure(structure, path):
    if len(path) == 1:
        structure[path[0]] = {}
    else:
        if path[0] not in structure:
            structure[path[0]] = {}
        add_to_structure(structure[path[0]], path[1:])

for i in data:
    add_to_structure(structure, i)



def write_to_file(structure, level=0):
    for key in structure:

        # Write the key to the file
        f.write(' ' * level * 5 + key)

        f.write( '\n')
        write_to_file(structure[key], level + 1)

        
with open('result.txt', 'w') as f:
    # Write the structure to the file
    write_to_file(structure)

The result.txt file would look like this:

Parent
     Sub A
          AAB
          AAU
     Sub B
          AAP
          AA1
          AAK

I hope it solves your problem. Recursion in Python can be really helpful when dealing with a tree type structure of data as in your case, and so can be Python dictionaries for storing such data. Do look up more into them if you want to make amendments in the given script.

user_197658380
Related