Start of a "data collection and processing" run.

It has been decided that we need to find or collect thousands of files including 1-20 line of information crucial for manufacturing of goods at my workplace. Our firm produces wire harnesses (cables and wires tailored at given lengths, equipped with different type of connectors or plugs) that the Customers ask for. The required information which includes cable/wire type, length, end modifications such as contacts (ferrules, terminals, slide-shoes, connectors with or without housing,...) is defined by the Customer and consequently they provide these information to us in a form of a bill of material (BOM, in format of excel) file and the drawing of the final product (in format of pdf made by some computer aided drawing software abbreviated as CAD software). We have to follow these two definition files but the order of manufacturing steps how we proceed is left to us to plan and customise for each product.

So thousands of data related to over 1500 product is planned to be found and organized in a way that we may use it for loading into a new (unified) Enterprise Resource Planning (ERP) system. The data will serve as we call as production resource list (otherwise recipe), which frames basically all necessary information for the production, such as production processes along with the used raw materials and parts, the machines and tools that we apply in addition to the above mentioned BOM data.

There was a Administrative Management System used that was used almost since the foundation of the firm, ages back in time. This software was able to create BOMs for the final products, so that Warehouse could keep quantity of parts and raw materials up-to-date. Of course the materials and parts were not well named and the identification (item number) was also not carefully and precisely defined, but the administrators could deal with it because they got used to the system and naming and codes of the items in the form as they were input.

On the other hand we have faced another challenge. In spite of the fact that ISO9001 Quality Management System was already in work at this time and consequently the required (BOM and CAD) files were saved in a well defined folder structure so resources of data could be easily located (and used meanwhile production), we knew that a large part of BOMs were received from the Customers in "print screen" version, so as images. An excel sheet or a csv format is easy to extract but images require optical character recognition (OCR) softwares. So either 

• we set up an OCR system that is able to extract data without error (or at low error level) from the images, 
• or we extract them manually (by reading it)
• or we ask the Customer to send those BOMs in extractable file format

and all have to do in mass as more than 200 products were involved in this question. A reasonable mixture of above mentioned options would also be acceptable taking into account the benefit of each and comparig with the time that we have to invest in the method.

This was the point where I started to think about a software based (semi)automated system which could be able to go through a list of products (item codes), find and process the data of the connected BOMs and other related files and give back an importable production resource list(s) as it is defined by the new ERP system. The requested content in the predefined data structure.

Footnote: I know by experience that all above mentioned options require time and precision. I will check all options as thouroughly as I can. I am aware of the well known fact that when the missing data will be available still we will have the long run of extract-transfer-load (ETL) process ahead us. I presume at least a year for the project taking into account the human resources that we have for the project.