Revision history [back]

One way to achieve this can be by using Python's os and pandas libraries. Here are the high-level steps that can be followed:

1. Import the required libraries:

import os
import pandas as pd
import sqlite3

1. Define the folder path where the SQL files are located:

folder_path = 'path/to/folder'

1. Get a list of all SQL files in the folder:

sql_files = [file for file in os.listdir(folder_path) if file.endswith('.sql')]

1. Iterate through the list of SQL files and execute each file using a sqlite3 connection, and save the results as a pandas DataFrame:

for file in sql_files:
    # Read SQL query from file
    with open(os.path.join(folder_path, file), 'r') as f:
        query = f.read()

    # Connect to SQLite database and execute query
    conn = sqlite3.connect('database.db')
    df = pd.read_sql_query(query, conn)

    # Export DataFrame to Excel file
    excel_file = os.path.splitext(file)[0] + '.xlsx'
    df.to_excel(excel_file, index=False)
    conn.close()

This code will execute each SQL file in the folder, save the results in a pandas DataFrame, and export the data to an Excel file with the same name as the SQL file. The resulting Excel files will be saved in the same folder as the SQL files. Note that this approach assumes that the SQL files are written to retrieve data from a SQLite database. Adaptations would need to be made if a different type of database is being used.