How to Convert Excel Data to SQL INSERT Scripts

SQL INSERT statements are widely used to populate tables manually or programmatically, especially in software development and deployment pipelines. Converting Excel to SQL inserts is useful when delivering data to clients, testing without a live database, or seeding demo environments.

1. Launch Advanced ETL Processor Enterprise
2. Create a Directory connection that points to the folder containing your Excel file(s).
3. Create a new Transformation:
   • Right-click on a Transformation group and choose New
4. Remove the default Validator object for a cleaner layout.
5. Add a Data Reader:
   • Select your Excel file (.xls or .xlsx)
   • Choose the relevant sheet, table, or range
   • Use file masks like *.xlsx to support batch processing

   The processor supports dynamic sheets and file handling with no need to reformat the Excel files.

6. Add a SQL Script Writer:
   • This writer outputs standard SQL INSERT INTO ... VALUES (...) statements
   • Choose the output directory and specify the file name (e.g., insert_data.sql)
7. Configure the Transformer:
   • Map Excel fields to SQL columns using drag-and-drop or AutoMap
   • Transform values if needed (e.g., escaping characters, formatting dates)
8. Run the Transformation:
   • Click Execute to generate the SQL file
   • Open the output file and review the generated INSERT statements
9. Automate the Script Creation:
   • Use the built-in scheduler to regenerate scripts periodically
   • Trigger jobs based on file availability or other conditions