python.py

# import requests
# from bs4 import BeautifulSoup
# import pandas as pd
# import re

# def clean_text(text):
#     return re.sub(r'\s+', ' ', text).strip()

# def scrape_website_tables(url):
#     try:
#         # Send HTTP request
#         headers = {
#             'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
#         }
#         response = requests.get(url, headers=headers)
#         response.raise_for_status()
        
#         # Parse HTML
#         soup = BeautifulSoup(response.content, 'html.parser')
        
#         # Dictionary to store parameter-value pairs
#         data_dict = {}
        
#         # Find all tables
#         tables = soup.find_all('table')
        
#         for table in tables:
#             rows = table.find_all('tr')
#             for row in rows:
#                 cols = row.find_all(['td'])
#                 if len(cols) >= 2:
#                     param = clean_text(cols[0].get_text())
#                     value = clean_text(cols[1].get_text())
#                     data_dict[param] = value
        
#         return data_dict
        
#     except requests.exceptions.RequestException as e:
#         print(f"Error fetching the webpage: {e}")
#         return None
#     except Exception as e:
#         print(f"An error occurred: {e}")
#         return None

# def process_multiple_urls(urls):
#     # List to store all data dictionaries
#     all_data = []
    
#     # Process each URL
#     for url in urls:
#         data = scrape_website_tables(url)
#         if data:
#             # Add URL to the data dictionary
#             data['Source URL'] = url
#             all_data.append(data)
    
#     if all_data:
#         # Convert list of dictionaries to DataFrame
#         df = pd.DataFrame(all_data)
        
#         # Move URL column to front
#         cols = ['Source URL'] + [col for col in df.columns if col != 'Source URL']
#         df = df[cols]
        
#         return df
#     return None

# def save_to_excel(df, filename='extracted_data.xlsx'):
#     try:
#         df.to_excel(filename, index=False)
#         return filename
#     except Exception as e:
#         print(f"Error saving to Excel: {e}")
#         return None

# def main():
#     urls = []
#     while True:
#         url = input("Enter a URL (or press Enter to finish): ")
#         if not url:
#             break
            
#         # Validate URL format
#         if not url.startswith(('http://', 'https://')):
#             url = 'https://' + url
        
#         urls.append(url)
    
#     if not urls:
#         print("No URLs provided.")
#         return
    
#     print(f"\nProcessing {len(urls)} URLs...")
#     df = process_multiple_urls(urls)
    
#     if df is not None:
#         output_file = save_to_excel(df)
#         if output_file:
#             print(f"\nData has been saved to: {output_file}")
#             print("\nPreview of extracted data:")
#             print(df.head().to_string())
#     else:
#         print("Failed to extract data from the websites.")

# if __name__ == "__main__":
#     main()











# import requests
# from bs4 import BeautifulSoup
# import pandas as pd
# import re
# import time

# def clean_text(text):
#     return re.sub(r'\s+', ' ', text).strip()

# def scrape_website_tables(url):
#     try:
#         # Send HTTP request with retry mechanism
#         max_retries = 3
#         for attempt in range(max_retries):
#             try:
#                 headers = {
#                     'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
#                 }
#                 response = requests.get(url, headers=headers, timeout=10)
#                 response.raise_for_status()
#                 break
#             except requests.RequestException:
#                 if attempt == max_retries - 1:  # Last attempt
#                     raise
#                 time.sleep(2)  # Wait before retrying
        
#         # Parse HTML
#         soup = BeautifulSoup(response.content, 'html.parser')
        
#         # Dictionary to store parameter-value pairs
#         data_dict = {}
        
#         # Find all tables
#         tables = soup.find_all('table')
        
#         for table in tables:
#             rows = table.find_all('tr')
#             for row in rows:
#                 cols = row.find_all(['td'])
#                 if len(cols) >= 2:
#                     param = clean_text(cols[0].get_text())
#                     value = clean_text(cols[1].get_text())
#                     data_dict[param] = value
        
#         return data_dict
        
#     except requests.exceptions.RequestException as e:
#         print(f"Error fetching {url}: {e}")
#         return None
#     except Exception as e:
#         print(f"Error processing {url}: {e}")
#         return None

# def process_urls_from_excel(excel_file='urls.xlsx'):
#     try:
#         # Read existing Excel file
#         try:
#             existing_df = pd.read_excel(excel_file)
#             print(f"Found existing file with {len(existing_df)} entries")
#         except FileNotFoundError:
#             # Create new DataFrame with URL column if file doesn't exist
#             existing_df = pd.DataFrame(columns=['URL'])
#             print("Creating new Excel file")
        
#         # Get unprocessed URLs
#         processed_urls = set(existing_df['URL'].values) if 'URL' in existing_df.columns else set()
#         urls_to_process = []
        
#         # Read URLs from 'urls' sheet
#         try:
#             urls_df = pd.read_excel(excel_file, sheet_name='urls')
#             urls_list = urls_df['URL'].dropna().tolist()
#             urls_to_process = [url for url in urls_list if url not in processed_urls]
#         except Exception as e:
#             print(f"Error reading URLs sheet: {e}")
#             return
        
#         if not urls_to_process:
#             print("No new URLs to process")
#             return
        
#         print(f"Found {len(urls_to_process)} new URLs to process")
        
#         # Process new URLs
#         new_data = []
#         for i, url in enumerate(urls_to_process, 1):
#             print(f"Processing URL {i}/{len(urls_to_process)}: {url}")
#             data = scrape_website_tables(url)
#             if data:
#                 data['URL'] = url
#                 new_data.append(data)
#             time.sleep(1)  # Delay between requests
        
#         if new_data:
#             # Convert new data to DataFrame
#             new_df = pd.DataFrame(new_data)
            
#             # Combine existing and new data
#             if len(existing_df) > 0:
#                 # Get all unique columns
#                 all_columns = list(set(existing_df.columns) | set(new_df.columns))
                
#                 # Ensure all columns exist in both DataFrames
#                 for col in all_columns:
#                     if col not in existing_df:
#                         existing_df[col] = None
#                     if col not in new_df:
#                         new_df[col] = None
                
#                 # Combine DataFrames
#                 combined_df = pd.concat([existing_df, new_df], ignore_index=True)
#             else:
#                 combined_df = new_df
            
#             # Move URL column to front
#             cols = ['URL'] + [col for col in combined_df.columns if col != 'URL']
#             combined_df = combined_df[cols]
            
#             # Save to Excel
#             combined_df.to_excel(excel_file, index=False, sheet_name='data')
            
#             # Save unprocessed URLs to 'urls' sheet
#             remaining_urls_df = pd.DataFrame({'URL': urls_list})
#             with pd.ExcelWriter(excel_file, mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
#                 remaining_urls_df.to_excel(writer, sheet_name='urls', index=False)
            
#             print(f"\nProcessed {len(new_data)} new URLs")
#             print(f"Total entries in file: {len(combined_df)}")
            
#         else:
#             print("No new data was collected")
            
#     except Exception as e:
#         print(f"Error processing Excel file: {e}")

# def main():
#     print("Starting URL processing from Excel file...")
#     process_urls_from_excel()
#     print("Processing complete!")

# if __name__ == "__main__":
#     main()










import requests
from bs4 import BeautifulSoup
import pandas as pd
import re
import time
from openpyxl import load_workbook

def clean_text(text):
    return re.sub(r'\s+', ' ', text).strip()

def scrape_website_tables(url):
    try:
        # Send HTTP request with retry mechanism
        max_retries = 3
        for attempt in range(max_retries):
            try:
                headers = {
                    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
                }
                response = requests.get(url, headers=headers, timeout=10)
                response.raise_for_status()
                break
            except requests.RequestException:
                if attempt == max_retries - 1:  # Last attempt
                    raise
                time.sleep(2)  # Wait before retrying
        
        # Parse HTML
        soup = BeautifulSoup(response.content, 'html.parser')
        
        # Dictionary to store parameter-value pairs
        data_dict = {}
        
        # Find all tables
        tables = soup.find_all('table')
        
        for table in tables:
            rows = table.find_all('tr')
            for row in rows:
                cols = row.find_all(['td'])
                if len(cols) >= 2:
                    param = clean_text(cols[0].get_text())
                    value = clean_text(cols[1].get_text())
                    data_dict[param] = value
        
        return data_dict
        
    except requests.exceptions.RequestException as e:
        print(f"Error fetching {url}: {e}")
        return None
    except Exception as e:
        print(f"Error processing {url}: {e}")
        return None

def ensure_sheet_exists(excel_file, sheet_name, columns=None):
    try:
        wb = load_workbook(excel_file)
    except FileNotFoundError:
        # Create new workbook with both sheets
        df = pd.DataFrame(columns=['URL'])
        with pd.ExcelWriter(excel_file, engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name='data', index=False)
            df.to_excel(writer, sheet_name='urls', index=False)
        wb = load_workbook(excel_file)
    
    if sheet_name not in wb.sheetnames:
        # Add the missing sheet
        wb.create_sheet(sheet_name)
        ws = wb[sheet_name]
        if columns:
            for col_num, header in enumerate(columns, 1):
                ws.cell(row=1, column=col_num, value=header)
        wb.save(excel_file)

def process_urls_from_excel(excel_file='urls.xlsx'):
    try:
        # Ensure both sheets exist
        ensure_sheet_exists(excel_file, 'data', ['URL'])
        ensure_sheet_exists(excel_file, 'urls', ['URL'])
        
        # Read existing data
        existing_df = pd.read_excel(excel_file, sheet_name='data')
        print(f"Found existing file with {len(existing_df)} entries")
        
        # Read URLs from 'urls' sheet
        urls_df = pd.read_excel(excel_file, sheet_name='urls')
        if 'URL' not in urls_df.columns:
            print("No URL column found in urls sheet. Please add URLs under a 'URL' column.")
            return
            
        urls_list = urls_df['URL'].dropna().tolist()
        
        # Get unprocessed URLs
        processed_urls = set(existing_df['URL'].values) if 'URL' in existing_df.columns else set()
        urls_to_process = [url for url in urls_list if url not in processed_urls]
        
        if not urls_to_process:
            print("No new URLs to process")
            return
        
        print(f"Found {len(urls_to_process)} new URLs to process")
        
        # Process new URLs
        new_data = []
        for i, url in enumerate(urls_to_process, 1):
            print(f"Processing URL {i}/{len(urls_to_process)}: {url}")
            data = scrape_website_tables(url)
            if data:
                data['URL'] = url
                new_data.append(data)
            time.sleep(1)  # Delay between requests
        
        if new_data:
            # Convert new data to DataFrame
            new_df = pd.DataFrame(new_data)
            
            # Combine existing and new data
            if len(existing_df) > 0:
                # Get all unique columns
                all_columns = list(set(existing_df.columns) | set(new_df.columns))
                
                # Ensure all columns exist in both DataFrames
                for col in all_columns:
                    if col not in existing_df:
                        existing_df[col] = None
                    if col not in new_df:
                        new_df[col] = None
                
                # Combine DataFrames
                combined_df = pd.concat([existing_df, new_df], ignore_index=True)
            else:
                combined_df = new_df
            
            # Move URL column to front
            cols = ['URL'] + [col for col in combined_df.columns if col != 'URL']
            combined_df = combined_df[cols]
            
            # Save to Excel (preserve both sheets)
            with pd.ExcelWriter(excel_file, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
                combined_df.to_excel(writer, sheet_name='data', index=False)
                urls_df.to_excel(writer, sheet_name='urls', index=False)
            
            print(f"\nProcessed {len(new_data)} new URLs")
            print(f"Total entries in file: {len(combined_df)}")
            
        else:
            print("No new data was collected")
            
    except Exception as e:
        print(f"Error processing Excel file: {e}")
        raise  # This will show the full error trace

def main():
    print("Starting URL processing from Excel file...")
    process_urls_from_excel()
    print("Processing complete!")

if __name__ == "__main__":
    main()