Extract All Links from Excel Sheet Using Python

Extracting hyperlinks from an Excel sheet is an incredibly useful skill, especially for businesses and data analysts working with extensive datasets. Links might contain additional data sources, references, or essential resources. Here, we'll guide you through a comprehensive Python solution that uses libraries like openpyxl and pandas to parse Excel files and extract hyperlinks seamlessly.

Getting Started

Before diving into the code, ensure you have the necessary libraries installed:

• openpyxl: To read and write Excel 2010 xlsx/xlsm/xltx/xltm files.
• pandas: For handling data structures and operations.

You can install these libraries using pip:

pip install openpyxl pandas

💡 Note: Ensure your Python environment is updated to avoid version conflicts.

Understanding Excel Hyperlinks

Hyperlinks in Excel can be found as:

• Cell Values where hyperlinks are visible
• Cell Attributes where links are hidden

Extracting Hyperlinks from Excel

Here’s how you can extract hyperlinks from an Excel sheet:

from openpyxl import load_workbook
import pandas as pd



wb = load_workbook(‘your_excel_file.xlsx’)
sheet = wb.active



links = {}



for row in sheet.iter_rows(values_only=True):
    for cell in row:
        if cell.hyperlink:
            links[cell.coordinate] = cell.hyperlink.target



df = pd.DataFrame.from_dict(links, orient=‘index’, columns=[‘Hyperlink’])



print(df)

🔍 Note: This script focuses on hyperlinks attached to cells. If hyperlinks are within the cell content itself, you would need additional processing steps.

Advanced Extraction Techniques

To handle more complex scenarios, consider:

• Extracting links from cell values
• Dealing with hyperlinks in merged cells
• Filtering or categorizing links based on certain criteria

Categorizing Links

If you want to categorize the links, you might do something like:

def categorize_link(url):
    if ‘example.com’ in url:
        return ‘Company Website’
    elif ‘blogspot.com’ in url:
        return ‘Blog’
    else:
        return ‘Other’

categorized_links = {cell: {‘url’: link, ‘category’: categorize_link(link)} for cell, link in links.items()}
df = pd.DataFrame.from_dict(categorized_links, orient=‘index’)

Finalizing Your Extraction

Now you have a DataFrame containing hyperlinks. Here are some final tips:

• Save your DataFrame to CSV or Excel file for analysis
• Check for duplicates or broken links
• Use the extracted links for further processing or data enrichment

To conclude, extracting hyperlinks from Excel using Python has practical applications in data analysis, especially when dealing with large, link-heavy datasets. With this guide, you can automate the process, categorize links for better organization, and leverage the power of Python to streamline your workflow.

Can I extract only external links?

+

Yes, you can filter out internal links by checking if the URL starts with “http” or “https”.

What if my Excel sheet contains merged cells?

+

When dealing with merged cells, ensure you capture the hyperlink from the top-left cell of the merged range as this cell typically contains the hyperlink data.

How can I verify if the extracted links are still active?

+

You can use Python libraries like ‘requests’ to send a HEAD request to the links and check for response status codes (e.g., 200 means the link is active).