Script-Based Automated Excel to SQL Generation Guide

In database development or testing, it is common for customers to frequently change test data. Each time a new Excel file is received, manually editing SQL is time‑consuming and error‑prone. This guide introduces a script‑based automation solution: simply place the Excel file in a designated directory and run a single command to automatically generate the corresponding DDL and DML scripts.

Script Origin and Location

The generator script originates from a post on cnblogs and is located at scripts/generate_v2.py in the project. Note that this script is not a generic, out‑of‑the‑box tool; it is a custom script generated with AI for the specific project structure. As long as the customer's file structure remains largely unchanged, the script can be reused when the data changes, without needing to regenerate it each time.

Conventions to Understand Before Use

To ensure correct parsing, follow these conventions:

Filename as table name: e.g., user.xlsx corresponds to the table user.
First row must be field names: The script treats this row as column identifiers.
Only the first worksheet is read: If the Excel file contains multiple worksheets, only the first is processed.
All rows from the second row onward are data: Every row after the first is considered data to be inserted.
All .xlsx files in the reference/ directory participate: No need to specify each file individually; just place them in the directory.

Step‑by‑Step Procedure

1. Prepare the Excel File

Place the updated .xlsx file in the project's reference/ directory, ensuring it follows the conventions above, especially that the first row contains the field names.

2. Run the Generator

Make sure the required dependencies are installed (e.g., Python 3 and the necessary third‑party libraries), then execute from the project root:

python3 scripts/generate_v2.py

3. Check the Generated Output

After execution, the terminal will display the number of tables and total rows generated, in the format:

Generated &lt;table_count&gt; tables and &lt;total_rows&gt; rows

The exact values depend on the contents of the reference/ directory. Additionally, the script overwrites the following files under scripts/v2/:

01_ddl.sql — table creation statements
02_dml.sql — data insertion statements
03_verify.sql — data validation script
run_all.sql — a comprehensive script containing all the above steps
生成清单.md — a manifest recording each file's hash, column count, and row count

It is recommended to first review the terminal output, then open scripts/v2/生成清单.md to verify file hashes, column counts, and row counts, helping to detect any data or formatting issues early.

4. Execute the Generated SQL Scripts

Once the generation is confirmed correct, run these scripts against the target database. Important: The generated V2 scripts drop and recreate tables with the same name, which is a destructive operation that may cause data loss. Therefore:

Verify the target database before execution and back up data if necessary.
Prefer testing in a staging environment before running in production.
Prepare a rollback plan if needed.

Note that the original scripts/*.sql files are not modified; they remain unchanged.

Summary

With this script, you only need to replace the Excel files in reference/ and run a single command to automate the generation of SQL from data, significantly reducing manual effort and error risk. Offloading repetitive tasks to a script is a viable path to increased efficiency.

If you encounter issues or wish to integrate the script into a more automated workflow, you can further explore error handling, logging, and other extensions.