Automate Data Entry in Seconds

I know what it’s like to be completely lost among folders, heavy PDF files, and HTML pages, manually entering the same information for every employee into Excel over and over again. It’s exhausting work, prone to human error, and consumes hours that you could be using on tasks that actually add value. You feel like you’re wasting time on a mechanical process that shouldn’t even exist.

1. Source Standardization: Organizing all downloaded files (HTML or PDF) into a single master folder, with consistent names so our search engine knows exactly where to start without any guesswork.
2. Bulk Connection and Reading: Setting up a workflow that scans the entire folder, opens each file one by one, and detects text patterns (such as “Arrival Date” or “Project Completion”) where the key data resides.
3. Transformation and Consolidation: Cleaning up the visual noise from the files, extracting only the important numerical values or dates, and dumping everything into a single Excel table that updates with a single click every time you add a new profile.

4. Detailed Implementation

To achieve this without writing complex code or using complicated macros that break with any change, we are going to use the Power Query engine that you already have in Excel. Forget about “hand jamming”; we’re going to set up a data pipeline.

Step 1: Preparing the Ground Create a single folder (for example, C:\Projects\Employee_Profiles). Every time you download a profile from the web, save it there using the format you already planned: Name-surname-profile.pdf or .html. The key is that they all live in the same place.

Step 2: Connecting Excel to the Folder

1. Open a new Excel file.
2. Go to the Data tab > Get Data > From File > From Folder.
3. Browse and select your Employee_Profiles folder.
4. A window will open with the list of detected files. Do not click “Combine”; click the Transform Data button. This will open the Power Query Editor.

Step 3: The Heart of Automation (The Extraction) This is where the magic happens. You now have a column called Content that contains the binary files. We need to “open” these files.

• If your files are HTML:

  1. Add a Custom Column (Add Column tab).
  2. In the formula, type: = Html.Table([Content], {{"Text", "body"}})
  3. This will create a new column with the HTML content. Expand this column by clicking the two arrows in the header.

• If your files are PDF:

  1. Add a Custom Column.
  2. In the formula, type: = Pdf.Tables([Content])
  3. Expand the resulting column. You will see that Power Query detects “Pages” or “Tables”. Choose the Pages option.

Step 4: Locating Specific Data (Filtering) Once you have the messy text, we need to search for your labels (e.g., “Arrival Date”). Don’t search the entire file; look for specific rows:

1. Use the Filter Rows function in the column containing the text to keep only the rows that contain keywords like “Arrival”, “Departure”, or “Start”.
2. If the data is in a “Label” and “Value” structure (one above the other), use the Index Column function so you can identify which value belongs to which label.
3. Once you have the data, apply Transform > Data Type > Date to ensure Excel doesn’t treat it as plain text.

Step 5: Final Load When you see in the preview that you have one row per person with their correct dates, go to the Home tab and click Close & Load. Now, every time you paste a new file into the folder, all you have to do is go to your Excel, right-click the table, and click Refresh. Done! The tool handles the heavy lifting.

5. Suggested Tool

The ideal tool is Power Query (built into Microsoft Excel). You don’t need to install anything new or pay for subscriptions. It is a professional data transformation engine that you already have available. It is perfect for this use case because it is specifically designed to “clean” messy files and consolidate multiple sources into a single structured table, eliminating the need for VBA macros that are difficult to maintain.

6. Prompt to copy

If you get stuck with the exact formula because your HTML or PDF has a very particular structure, copy this prompt and paste it into ChatGPT or Claude along with a snippet of the text you want to extract:

Act as a Power Query expert (M Language). I have a folder with [PDF or HTML] files containing employee information. I need a "Custom Column" formula to specifically extract the value that appears after the text "[Write the label here, e.g., Joining Date]". My file format is [Briefly describe if it's a table or plain text]. Please provide me with the technical step-by-step and the exact formula to use in the Power Query Editor.