Scenario

A directory contains multiple Excel files with multiple sheets that need to be converted to individual CSV files for processing.

Task

Write a Python script at /home/interview/convert_excel.py that reads all Excel files from /home/interview/excel_files/, converts each sheet to a separate CSV file, and saves them to /home/interview/csv_output/ with the naming convention filename_sheetname.csv.

Example

Input:

excel_files/
  ├── sales_data.xlsx (3 sheets: Q1_Sales, Q2_Sales, Q3_Sales)
  └── inventory.xlsx (2 sheets: Electronics, Furniture)

Expected output:

csv_output/
  ├── sales_data_Q1_Sales.csv
  ├── sales_data_Q2_Sales.csv
  ├── sales_data_Q3_Sales.csv
  ├── inventory_Electronics.csv
  └── inventory_Furniture.csv

Step 1: List the Excel files in the directory

ls /home/interview/excel_files/

Identify all .xlsx files that need to be processed.

Step 2: Create the Python script

nano /home/interview/convert_excel.py

Write a script that processes all Excel files and converts each sheet to CSV:

import pandas as pd
import os
from pathlib import Path

# Define directories
excel_dir = '/home/interview/excel_files/'
output_dir = '/home/interview/csv_output/'

# Get all Excel files
excel_files = [f for f in os.listdir(excel_dir) if f.endswith('.xlsx')]

total_sheets = 0

for excel_file in excel_files:
    file_path = os.path.join(excel_dir, excel_file)
    filename = Path(excel_file).stem
    
    # Read all sheets from Excel file
    excel_data = pd.read_excel(file_path, sheet_name=None)
    
    # Convert each sheet to CSV
    for sheet_name, df in excel_data.items():
        output_filename = f"{filename}_{sheet_name}.csv"
        output_path = os.path.join(output_dir, output_filename)
        df.to_csv(output_path, index=False)
        total_sheets += 1

print(f"Converted {len(excel_files)} Excel files with {total_sheets} total sheets to CSV")

The pd.read_excel() with sheet_name=None returns a dictionary where keys are sheet names and values are DataFrames.

Step 3: Run the script

python3 /home/interview/convert_excel.py

Step 4: Verify the output

ls /home/interview/csv_output/

Should show 9 CSV files with naming convention filename_sheetname.csv.