Last week I had an interesting one come up. A friend who works admin at a local Fire Prevention company mentioned they had a new report format and they had hundreds of old documents that needed data moved into new documents with a new format. As the columns didn't line up exactly, they were having to futz around with the original table so they could manually copy-paste it into the new one. Even a small one with 5-10 entries would take nearly 5 minutes to integrate, and as we later discovered, some had upwards of *3000 entries* which could take a full day or more as the new document has space for only 20 entries per page so would require a bunch of new pages added. And there were over 700 of these reports. My pal was belly-aching about how the boss wanted them to go through and prepare all these report templates ahead of time manually. They'd have to set aside at least two weeks for two people to sit there doing this most tedious of tasks until their **eyes would bulge out of their head**, leaving their face a **smoking ruin** with **eyeball-goop dripping out of the empty sockets**.
![image.png](https://files.peakd.com/file/peakd-hive/coderad/Eo6Apn6Z6zC92Ri4Jouw4P33riwzpYYPHMvEWDwjpoykh6kPPqQvCZF7e5wNmerf1Gn.png)
Well needless to say, they were really bummed because they had a ton of other work they needed to be doing, and could not imagine just dropping everything for the time it would take to get this task done. Knowing the kind of work I do, they asked me the golden question, *"Can you automate this?"* ***Yes, yes, I can.*** They talked to their boss, and then got back to me immediately asking for a quote. I quickly scribbled something out and two days later I had a USB with hundreds of documents on it and a copy of the new 15 page blank report template.
# Break It Down Contract secured, I was ready to pull up my sleeves and take a peek. As with any problem, the first thing you need to do is break it down into simpler steps. After taking a look at the old documents (each was a list of devices, ie fire alarm pull stations, smoke alarms and their circuits) and the new report template, I determined what I would need my code to do: 1. ***Load** a folder of docx documents* 2. ***Loop** through each file and extract the filename and relevant data* 3. ***Insert** the data into the proper place in a blank template* 4. ***Output** a new properly named file with the old data on the new template* Because of the sheer number of documents and some inconsistencies in the file naming, I decided that I would handle this in two stages; an ***Input Stage*** where I would extract and sanitize the data/filename from each docx and write those to individual .csv files, and an ***Output Stage*** where I would merge the .csv data into a new template and save it with a proper filename.
# Why Python? Python is perfect for one-shot programs. We don't need to create a permanent piece of software with any bells or whistles, so while Java, C++ and C# are also great choices for working with external libraries and the filesystem, nothing is going to beat Python here for implementation speed. Now, if I wanted to make this an actual import and merging program for the general public or was optimising for run speed, I'd absolutely be picking C++ or C#, but since I'm essentially running this once, I just need to launch this from the Command Line and watch it spit out my files. Javascript is also a decent choice for one-shot scripts, especially if you're going to share the script -- a one-shot .js script can be run by anyone with a browser, whereas you need to have Python installed to run a .py script. Since I already have Python installed, and intended to be the only one running the script, this was a no-brainer. > ***SIDE NOTE**: If you've never used Python before and are interested in learning it, I can't recommend Al Sweigart's [Automate the Boring Stuff with Python](https://automatetheboringstuff.com/) book nearly enough. While it has a well deserved reputation as a great starting point for people who have never programmed anything before, it moves along quickly enough that experienced non-python programmers can get up to speed very rapidly as well. It's been years, but as I recall one of the first lessons had you pulling specific data from individual emails from an outlook mailbox file in under ten lines of code, instantly demonstrating the kind of power Python allows you to wield.*
# Input Stage First things first, we need to assess the original document and see what we're dealing with. Here's a made-up example for a file called "*7777 Wizard Crescent - Device Location Sheet 2024.docx*": ![image.png](https://files.peakd.com/file/peakd-hive/coderad/Eo1vTqKn7HkQJS8Ee1roNnpL5Uc23Bb6HumAsqiHJb3ysMtfLCftd9sbBAB3HjatYtr.png)
After this table there are a series of other tables, with legends for the columns, appropriate fire codes and such... but everything we need to extract is on this first table (or tables). This example is just a tiny shop with 4 pull stations and 2 bells, but when you get into bigger sites like hospitals, schools, malls and residential towers, you're easily looking at dozens or hundreds of devices. Thankfully those other tables were all only 3-4 columns, so I knew that if I scanned the document and found a 9 column table, it would have information I need. So lets create a new document called FetchData.py in a new folder, and open it in our favorite IDE. We know we'll need to *read the file system*, we'll need to *read the document*, we'll need to *sanitize the file names* and we'll need to *output csvs*. So we need some **imports**, and since we want to keep things organized we're going to establish a couple folders: ```python import os import docx import re import csv input_folder = "./source" output_folder = "./processed" ``` - **import os** gives us access to the Operating System to read a directory and see all the files in it - **import docx** gives us the ability to read and manipulate Word(docx) files. - **import re** for Regex so we can clean up the filenames; some say Device Location Sheets, some just say Device Sheet etc. and most have a year attached which we don't need. - **import csv** gives us the ability to read and write .csv *(comma separated value)*. and then **source** and **processed** will contain our original files and our pared-down .csv files containing just the extracted data.
Next we need a function to find the relevant table(s) in our document, and grab our data. In our case we just need the Location (column 0), the Device Type (column 1) and the Circuit Address (column 6). Additionally, some files have an extra third column with additional information, in that case we're looking for tables with 10 columns, and Circuit Address on column 7 instead: ```python def extract_table_data(doc_path): doc = docx.Document(doc_path) extracted_data = [] for table in doc.tables: for row_index, row in enumerate(table.rows): cells = [cell.text.strip() for cell in row.cells] if len(cells) in [9, 10]: # Check for 9 or 10 columns if row_index == 0 and cells[0].lower() == "location": # Skip header row continue location = cells[0] device = cells[1] circuit = cells[6] if len(cells) == 9 else cells[7] if any([location, device, circuit]): # Only keep rows with data extracted_data.append([location, device, circuit]) return extracted_data ``` If we find a row with "location" in it, we want to skip that since it's just a legend row, and we're also removing any blank rows we encounter to minimize page count on our final reports.
Now we need a function to convert the filename *7777 Wizard Crescent - Device Location Sheet 2024.docx* to a new filename, al la *777 Wizard Crescent - Device Data.csv*. ```python def sanitize_filename(filename): # Normalize all "Device Location" variations to "Device Data" filename = re.sub(r"(?i)device locations?( sheets?)?", "Device Data", filename) # Remove specific year patterns with a leading space for year in [" 2022", " 2023", " 2024", " 2025"]: filename = filename.replace(year, "") # Ensure proper extension return filename.replace(".docx", ".csv") ```
and finally a function to run those two functions in a loop over the entire contents of our *source* folder: ```python def process_folder(input_folder, output_folder): if not os.path.exists(output_folder): os.makedirs(output_folder) for filename in os.listdir(input_folder): if filename.lower().endswith(".docx"): doc_path = os.path.join(input_folder, filename) extracted_data = extract_table_data(doc_path) new_filename = sanitize_filename(filename) csv_path = os.path.join(output_folder, new_filename) with open(csv_path, "w", newline="", encoding="utf-8") as csvfile: csv_writer = csv.writer(csvfile, quoting=csv.QUOTE_ALL) csv_writer.writerows(extracted_data) print(f"Processed: {filename} -> {new_filename}") ```
Now we simply need one last command to run the process and we're gravy: ```python process_folder(input_folder, output_folder) ``` Voila! We save our script, make a folder called ***source*** and populate it with documents, and drop to command line to run *python3 FetchData.py*. This leaves us with a nice folder full of 3-column .csv files containing the three bits of data we need to import to their new templates! ![image.png](https://files.peakd.com/file/peakd-hive/coderad/23uQNa6vbjzUXv5aAmkZVxYRsBJiGYnGpUBvQEjC8THgxhuGWcpV8VWdcRhkceFr7vrKb.png)
# Output Stage This has gotten quite a bit longer than I expected, so I think I'm going to leave it at that for now. Tune in next week, and I'll run through the second half of the process where we build out our templates and merge our data with a new script, *MergeData.py*. We'll be dealing with a bunch of edge cases, inconsistent file names and a couple dead end experiments, so should be a real ride. In the meantime, feel free to hit me with any questions about this in the comments below. And ofc, if you ever need any of this type of work done to save your sanity or the sanity of your family, friends or co-workers, you can always reach out directly to me at *coderaddesign[dot]gmail.com* or by DM to *Scarflax#8455* on Discord.

word

Experts

Show more authors