Spaces:

engrtm
/

ReadFile

Runtime error

Update app.py

5b8de2c verified over 1 year ago

7.25 kB

# =========================================================
# 🚩 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

	# =========================================================
	# 🚩 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