{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\ProgramData\\Anaconda3\\envs\\py310\\lib\\site-packages\\openpyxl\\workbook\\child.py:99: UserWarning: Title is more than 31 characters. Some applications may not be able to read the file\n",
      "  warnings.warn(\"Title is more than 31 characters. Some applications may not be able to read the file\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Excel file 'mega_sheet.xlsx' has been created with 5 sheets.\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.utils.dataframe import dataframe_to_rows\n",
    "\n",
    "# Set the path to the \"csv\" folder\n",
    "csv_folder = \"csv\"\n",
    "\n",
    "# Create a new Excel workbook\n",
    "workbook = Workbook()\n",
    "\n",
    "# Remove the default sheet created by openpyxl\n",
    "workbook.remove(workbook.active)\n",
    "\n",
    "# Iterate through all CSV files in the folder\n",
    "for filename in os.listdir(csv_folder):\n",
    "    if filename.endswith(\".csv\"):\n",
    "        # Read the CSV file\n",
    "        csv_path = os.path.join(csv_folder, filename)\n",
    "        df = pd.read_csv(csv_path)\n",
    "        \n",
    "        # Create a new sheet with the CSV filename (without extension)\n",
    "        sheet_name = os.path.splitext(filename)[0]\n",
    "        sheet = workbook.create_sheet(title=sheet_name)\n",
    "        \n",
    "        # Write the DataFrame to the sheet, including column names\n",
    "        for row in dataframe_to_rows(df, index=False, header=True):\n",
    "            sheet.append(row)\n",
    "\n",
    "# Save the Excel workbook\n",
    "output_filename = \"mega_sheet.xlsx\"\n",
    "workbook.save(output_filename)\n",
    "\n",
    "print(f\"Excel file '{output_filename}' has been created with {len(workbook.sheetnames)} sheets.\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py310",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}