racking-data-conversion/excel_to_csv.py at main · laccore/racking-data-conversion · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
# excel_to_csv.py
#
# Gemini-generated script to convert specified sheet of an Excel file to CSV.
# Added __main__ to convert all .xls and .xlsx files in a dir to .csv of the same name.
#
# Requires pandas and openpyxl.

import os, sys
import pandas as pd

def excel_to_csv(excel_file_path, csv_file_path, sheet_name=0):
    """
    Converts a specific sheet from an Excel file to a CSV file.

    Args:
        excel_file_path (str): The path to the input Excel file (.xls or .xlsx).
        csv_file_path (str): The path where the output CSV file will be saved.
        sheet_name (str or int, optional): The name or index of the sheet to convert.
                                            Defaults to 0 (the first sheet).
    """
    try:
        # Read the Excel file into a pandas DataFrame
        df = pd.read_excel(excel_file_path, sheet_name=sheet_name)

        # Convert the DataFrame to a CSV file
        # index=False prevents writing the DataFrame index as a column in the CSV
        df.to_csv(csv_file_path, index=False, encoding='utf-8')

        print(f"Successfully converted '{excel_file_path}' (Sheet: '{sheet_name if isinstance(sheet_name, str) else 'Sheet ' + str(sheet_name + 1)}') to '{csv_file_path}'.")
    except FileNotFoundError:
        print(f"Error: Excel file not found at '{excel_file_path}'.")
    except Exception as e:
        print(f"An error occurred: {e}")

# Example usage:
# Assuming 'input.xlsx' is in the same directory as your script
# excel_to_csv('input.xlsx', 'output.csv', sheet_name='Sheet1')
# Or to convert the first sheet by default:
# excel_to_csv('another_input.xlsx', 'another_output.csv')

def convert_dirs(inputdir, outputdir):
    excel_files = [f for f in os.listdir(inputdir) if
                   f.endswith('.xls')
                   or f.endswith('.xlsx')
                   and not f.startswith('~')] # ignore Excel tilde(~)-prefixed backup files

    input_abspath = os.path.abspath(inputdir)
    output_abspath = os.path.abspath(outputdir)
    for ef in excel_files:
        ef_no_ext = ef.split('.')[0]
        csv_file = f'{os.path.basename(ef_no_ext)}.csv'
        excel_to_csv(os.path.join(input_abspath, ef), os.path.join(output_abspath, csv_file))

if __name__ == "__main__":
    arg_count = len(sys.argv)
    if arg_count < 2:
        print("Usages:")
        print("python excel_to_csv.py [dir containing .xls/.xlsx input files; .csv output files will be written here as well]")
        print("python excel_to_csv.py [dir containing .xls/.xlsx input files] [dir to write .csv output files]")
    elif arg_count == 2: # create CSVs in Excel dir
        convert_dirs(inputdir=sys.argv[1], outputdir=sys.argv[1])
    elif arg_count == 3: # create CSVs in specificed output dir
        convert_dirs(inputdir=sys.argv[1], outputdir=sys.argv[2])