Select range from many identical non tabular excel dimensional report

[labmgf]

Good day,

the Q.dept. of the company I'm working for has produced many dimensional analisys reports of hoses in non tabular form like this one  below. 

I'm struggling trying to exctract hose code and batch n.

and the dimensional measurements organized in sub batches (1A-1B etc..) in order to later analytics.

What woul be Your approach?

Many Thanks

Alessio

[rubayatyasmin · Accepted solution]

Hi, @labmgf 

To extract specific data like hose code, batch number, and dimensional measurements from a non-tabular report like the one you've provided, I would suggest the following approach:

1. Data Identification:

   • Manually review the report to identify the pattern or location where the hose code and batch numbers are consistently located. This might be in the header, footer, or a specific section of the report.

2. Structured Extraction:

   • If the report is in a consistent format, you can use a script to extract the information. For example, if the report is a PDF, you could use a PDF parsing library in Python, such as PyPDF2 or PDFMiner, to extract text.
   • For an Excel file, you could use libraries like openpyxl or pandas to read the Excel file and extract the relevant cells based on their location.

3. Regular Expressions:

   • Use regular expressions to match and extract hose codes and batch numbers if they follow a specific pattern.

4. OCR and Text Recognition:

   • If the report is scanned or if you have to work with images, Optical Character Recognition (OCR) tools like Tesseract can convert images of text into actual text data that can be further processed. Tesseract can be used via the pytesseract Python library.

5. Data Organization:

   • Once you've extracted the raw data, you can organize it into a structured format like a CSV file or an Excel workbook. You could use the pandas library to handle and organize the data efficiently.