How to search in databases spread all over the company

The problem

Knowledge is spread all over the company and hidden inside excel files. During the development of new products it is crucial to have a state of the art of what has been already done to save money.

The context and some contraints

• Engineering department
• Each engineer manages its own formatted list of product datas using excel

  

• The headers may have different names (example : list 1 refers to temperature as  « Max Temperature » while list 2 refers as « T »)
• The values may be in expressed in different units (exemple : list 1 expresses the temperature in °C while list 2 in Kelvin)
• No extra work for the engineers

My solution

1. Using a scheduled task, the different excel lists are uploaded from the windows file system onto the company server

   

2. Using a server scheduled task (cron), the excel files are converted into sql format

   

   

3. A conversion sql database helps to uniform the parameters designations and units
4. The result is quite powerful

   

Conclusion

With this method, we authorise the engineers to work with their own excel files and still provide a solution to search among all the different files.

Future improvements

• Make sure that there is an unique identifier for each excel lines so that any project can make reference to it.
• Auto update the sql databases structure to match structure changes in the excel files.