Excel to SQL Script Generator

A specialized .NET Console Application designed to automate the process of generating SQL UPDATE scripts from highlighted rows in an Excel spreadsheet. This tool is particularly useful for bulk data updates where specific changes are manually identified in Excel.

🚀 Key Features

• Highlighted Row Detection: Automatically identifies and extracts rows with highlighted cells for processing.
• Dynamic SQL Generation: Generates standard SQL UPDATE statements based on Excel data and pre-defined mappings.
• Configurable Identity Mapping: Map multiple Excel columns to database identifiers (Primary Keys) via App.config.
• Flexible Column Mapping: Dynamically handles mapping between Excel column names and Database column names.
• Safe Value Handling: Automatically handles single quotes in data to prevent SQL syntax errors.
• Automatic Script Export: Saves generated SQL scripts with timestamps in a dedicated Scripts directory.

📋 Prerequisites

• Runtime: .NET Framework 4.8
• Dependencies: ExcelHighlight library (Internal/Private dependency for Excel parsing).

⚙️ Configuration

The application is configured via the App.config file. Update the following settings before running:

<appSettings>
  <!-- Path to your Excel source file -->
  <add key="ExcelPath" value="C:/Path/To/Your/Excel/File.xlsx" />
  
  <!-- (Optional) Connection string for database reference -->
  <add key="ConnectionString" value="Server=YOUR_SERVER;Database=YOUR_DB;Integrated Security=True;" />
  
  <!-- Mapping of Excel Columns to Database PK columns. 
       Format: ExcelColumn1:DbColumn1,ExcelColumn2:DbColumn2 -->
  <add key="IdentifierColumns" value="Material:MDG_MaterialNumber,Plant:MDG_Plant" />
  
  <!-- Target Database Table Name -->
  <add key="TableName" value="dbo.ZINIBPT_MATERIAL" />
</appSettings>

🛠️ Usage

1. Open the project in Visual Studio.
2. Edit App.config to point to your Excel file and define your identifier mappings.
3. Build and Run the application.
4. The application will scan the Excel file, detect highlighted changes, and notify you of the processed rows.
5. Find your generated SQL script in the bin/Debug/Scripts (or bin/Release/Scripts) folder.

📁 Project Structure

• Program.cs: Main logic for row processing and SQL generation.
• App.config: Configuration settings for file paths and column mappings.
• ExcelSQL.csproj: Project configuration and dependencies.
• Scripts/: Default output directory for generated .sql files.

🤝 Contribution

Contributions are welcome! Please feel free to submit a Pull Request or open an issue for any bugs or feature requests.