Data Organizer & Formatter Prototype

The Original Vision

The export department of the company receives quarterly to yearly reports of the sales of their distributors. Each of these has their own style of noting down the information in a spreadsheet and thus getting a complete overview of the total sales made was inconvenient. For this reason, my supervisor was looking into a solution and eventually asked me if I knew of a way to streamline this process.

My proposed solution was to create a webpage with PHP and SQL that would take these Excel files (exported as CSV files) and automatically add them to the grand overarching database. Once there, the data could be sorted, filtered and searched conveniently.

What went wrong?

Due to an unforeseen and unannounced strengthening of security measures pushed by the Spanish branch of the company (being the head of all departments globally), several issues arose: The key functionality of uploading files into this website was blocked on all work devices due to privacy and sensitive data concerns, along with the firewall blocking the server I was running for my development process. Because of these factors, it was agreed that the development would no longer continue, but my supervisor had no objections to me including this recreation in my portfolio website with reference to him.

The Development Process

From the initial briefing, functionality had been indicated as the core concern regarding the project, with little to no cares regarding the aesthetics. This meant that I did not draft any page design before I jumped into the programming phase, deciding to stick to the company colours and a very minimal styling. Because of the way my employment was handled, I had limited time to work on the project, so I decided to distribute all my hours carefully, thus deciding not to have a design phase.

My client had already outlined certain steps for me to take since he did know how tight of a time window I was working with, and so offered me the core functionalities he would like to see in the order of importance. He asked me to prioritize the handling of data sourced from the biggest distributor first, a file that consisted of over 22 000 lines. Because of this, the code had to be significantly optimized for wait times to be reasonable. Unfortunately, once I had gotten to the point where after the uploading the file a searchable table was created, the aforementioned security update happened and I could no longer continue.

How I ended up writing the code is as follows: Since every dataset would be sent to the company through an Excel file, I looked into how to process those and found they could be exported as a CSV (Comma Seperated Values) file. These would then be uploaded into the page and processed into the correct format to be added to the database. Through the reasoning that the same company would format their data the same way every time, I added the requirement to check the data source before upload so it could be processed correctly. From there on out I could display the processed data in a table that had search and filtering functions, as requested.

The Final Result

The original project was lost, as I could not send it over to my personal laptop, but since I had recently written the code and still recalled all the logic behind it, the reconstruction took rather little time.

The file below is a zip file with 3 different sources of dummy sales data in CSV format that can be used to test the prototype. If you would prefer not to download this file, there is also a recording of myself using this program.