# ========================================================= # 🚩 1. Install dependencies # ========================================================= # ========================================================= # 🚩 2. Configuration #========================================================= ONEDRIVE_LINK = "https://onedrive.live.com/personal/c6536ba97412fbf8/_layouts/15/Doc.aspx?sourcedoc=%7Bae455bc0-7a7d-42a1-b346-22cb5e9d3753%7D&action=default&redeem=aHR0cHM6Ly8xZHJ2Lm1zL3gvYy9jNjUzNmJhOTc0MTJmYmY4L0VjQmJSYTU5ZXFGQ3MwWWl5MTZkTjFNQlBEb1hPV1VpbUNrTzlvQ1BEYURNZmc_ZT1YOHdPSTY&slrid=57d6e86a-2f3f-4dc8-9633-1eefdc5cc720&originalPath=aHR0cHM6Ly8xZHJ2Lm1zL3gvYy9jNjUzNmJhOTc0MTJmYmY4L0VjQmJSYTU5ZXFGQ3MwWWl5MTZkTjFNQlBEb1hPV1VpbUNrTzlvQ1BEYURNZmc_cnRpbWU9VjUweUdWLVYzVWc&CID=08b8850a-8ded-4b00-8e7a-5fae895488ef&_SRM=0:G:247" OPENAI_API_KEY = "sk-proj-EQTDxpzroHcMO5uqytSmoM3VDBxC9OiRS9heyFSzG90fvKK1ffcFCrAbhXQm12NhYxIiOIjDdyQT3BlbkFJ5Xz2cTGDOtgACBFeqNKoWPgEj_9E-0cmE0oDN61yBgciBFF7td_rqlIdbozVJhYnksY5FHoNoA" # ========================================================= # 🚩 3. Helpers # ========================================================= import pandas as pd, requests, openai from io import BytesIO import os # Import the os module to save the file import zipfile # Import zipfile to check if the content is a zip file def to_direct_download(url: str) -> str: """Convert OneDrive view/redir link to direct download link (.xlsx).""" # This conversion might not be universally reliable for all OneDrive link types. # It works for common "?e=XXXXX" share links. return ( url.replace("view.aspx?", "download.aspx?") .replace("redir?", "download?") ) def load_excel(url: str) -> pd.DataFrame: dl_url = to_direct_download(url) print(f"Attempting to download from: {dl_url}") # Print the direct download URL temp_file_path = "downloaded_content.bin" # Define temp file path outside try/except try: resp = requests.get(dl_url) resp.raise_for_status() # ⬅️ will fail if link is wrong (e.g., 404) # --- Added extensive diagnostic code --- content_bytes = BytesIO(resp.content) content_bytes.seek(0) # Reset stream position after creating BytesIO # Check the content type content_type = resp.headers.get('Content-Type', '').lower() print(f"Content-Type received: {content_type}") # Save the content to a file for inspection with open(temp_file_path, "wb") as f: f.write(resp.content) print(f"Content saved to {temp_file_path} for inspection.") print("Please check this file. It should be a valid .xlsx file.") # Additional checks for common issues if 'html' in content_type: print("Error: Content-Type is HTML. The link likely returned a web page instead of the file.") print("Inspect 'downloaded_content.bin' to see the HTML content.") raise ValueError("Downloaded content is HTML, expected Excel file.") # Check if the downloaded content is a zip file (Excel files are zip archives) # A more robust check is to see if it's a zip *and* contains typical Excel files try: # Use BytesIO to treat the content as a file for zipfile check with zipfile.ZipFile(content_bytes, 'r') as zf: zip_contents = zf.namelist() print(f"Content appears to be a zip file with contents: {zip_contents}") # Check for typical Excel structure files if '[Content_Types].xml' not in zip_contents or '_rels/.rels' not in zip_contents: print("Warning: Zip file does not contain expected Excel structure files.") # You might consider raising an error here if these are missing # Reset stream position before passing to pandas content_bytes.seek(0) except zipfile.BadZipFile: print("Error: Downloaded content is NOT a valid zip file. This is the most likely cause of the BadZipFile error.") # Re-raise the BadZipFile error with a more helpful message raise zipfile.BadZipFile("Downloaded content is not a valid zip file, expected an Excel (.xlsx) file.") from None except Exception as e: print(f"An unexpected error occurred while checking if the content is a zip file: {e}") # Still try to read as Excel, but note the potential issue # Attempt to read the excel file from the BytesIO object print("Attempting to read content as Excel...") # Pass the BytesIO object directly df = pd.read_excel(content_bytes, engine="openpyxl") # If successful, clean up the temp file if os.path.exists(temp_file_path): os.remove(temp_file_path) print(f"Cleaned up temporary file: {temp_file_path}") return df except requests.exceptions.RequestException as e: print(f"Error during file download: {e}") print("Please check the ONEDRIVE_LINK and your internet connection.") # Clean up temp file on download error if os.path.exists(temp_file_path): os.remove(temp_file_path) print(f"Cleaned up temporary file: {temp_file_path}") raise e # Re-raise the exception to see the full traceback except Exception as e: # Catch any other exceptions during read_excel or zipfile checks print(f"Error reading or processing downloaded file: {e}") print("This often happens if the downloaded content is not a readable Excel file even if it was a zip.") print(f"Inspect the file '{temp_file_path}' to understand what was downloaded.") # Do not clean up temp file on processing error, keep it for inspection raise e # Re-raise the exception after providing diagnostic info openai.api_key = OPENAI_API_KEY def ask_gpt(df: pd.DataFrame, question: str) -> str: context = df.to_string(index=False) prompt = f"Use only this table to answer.\n\n{context}\n\nQuestion: {question}" resp = openai.ChatCompletion.create( model="gpt-3.5-turbo", # or gpt-4 / gpt-4o messages=[{"role": "user", "content": prompt}] ) return resp.choices[0].message.content.strip() # ========================================================= # 🚩 4. Load Excel # ========================================================= # Note: This will still fail if the file is not a valid Excel file, # but the added diagnostics will provide more information about *why*. try: df = load_excel(ONEDRIVE_LINK) print("✅ Excel loaded! Preview:") display(df.head()) # ========================================================= # 🚩 5. Ask a question # ========================================================= question = "Replace this with your question" print("\nQ:", question) print("A:", ask_gpt(df, question)) except Exception as e: print(f"\nExecution stopped due to error loading Excel: {e}") # You might want to add error handling or alternative logic here # if the Excel loading fails. pass # Or handle the error gracefully