EPT | Excel PowerTools Pro

Version | 1.0 - April-2025

🚀 About EPT | Excel PowerTools Pro

Clean, Merge, and Convert Excel Files to SQL with One Click (WinForms Application | C#/.NET | EPPlus Powered)

A professional Windows desktop application that automates Excel file processing with:

  • ✔ Remove empty rows & duplicate data
  • ✔ Merge multiple Excel files into one
  • ✔ Convert to SQL (simple tables or relational databases)
  • ✔ 100% standalone – No Excel installation required
  • ✔ Enterprise-ready – Handles 500,000+ rows efficiently

Excel Data Processor ✨ Key Features

Feature Description
🧹 Smart Data Cleaning
Remove blanks/duplicates with configurable rules including advanced options for handling NULL values, whitespace, and custom empty value definitions. Preserves original formatting while cleaning.
🔄 File Merging
Combine sheets/workbooks with intelligent column mapping. Supports merging by column name or position with conflict resolution options. Handles different file formats seamlessly.
💾 SQL Export
Generate ready-to-use CREATE TABLE and INSERT scripts with database-specific syntax for SQL Server, MySQL, and PostgreSQL. Includes data type auto-detection and proper value escaping.
🏗️ Relational DB Builder
Auto-detect relationships for normalized SQL schemas with multi-table support. Includes foreign key constraint generation and lookup table creation with wizard-guided setup.

🔥 5 Game-Changing Features (Enhanced)

1️⃣ Intelligent Empty Row Cleaner

  • Advanced detection: Removes rows with hidden blanks, #N/A, NULL, or "-" values
  • Progress tracking: Real-time UI updates during large file processing
  • Undo functionality: Preserves original data until final export

2️⃣ Precision Duplicate Remover

  • Column-based control: Select specific columns to check (e.g., ignore timestamps)
  • Case-insensitive matching: "APPLE" = "Apple" = "apple"
  • Data validation: Skip rows with missing critical fields

3️⃣ Lightning-Fast File Merger

  • Two-file merging: Perfect for combining monthly/quarterly reports
  • Smart column alignment: Matches "Order_ID" ↔ "OrderID" automatically
  • Data type preservation: Keeps dates, currencies, and formulas intact

4️⃣ Excel-to-SQL Converter (Enhanced)

  • Multi-database support:
    • SQL Server: IDENTITY keys
    • MySQL: AUTO_INCREMENT
    • PostgreSQL: SERIAL
  • Type inference: Detects VARCHAR(255) vs TEXT based on content length
  • Bulk INSERT optimization: Generates batched statements for 10x faster imports

5️⃣ Relational Database Wizard

  • PK/FK autodetection: Identifies CustomerID → Customers relationships
  • Lookup table generation: Creates normalized schemas from flat Excel data
  • Constraint options: Configurable ON DELETE CASCADE rules

📦 Included Dependencies

  • EPPlus 7.7 (Excel I/O engine) - Lightning-fast Excel I/O
  • RecyclableMemoryStream (60% lower memory usage)
  • .NET Framework 4.8.1 (Included in Windows 10+)

📦 Required Packages

EPPlus version="7.7.0" targetFramework="net481"
EPPlus.Interfaces version="7.7.0" targetFramework="net481"
EPPlus.System.Drawing version="7.7.0" targetFramework="net481"
Microsoft.IO.RecyclableMemoryStream version="3.0.1" targetFramework="net481"
System.Buffers version="4.5.1" targetFramework="net481"
System.ComponentModel.Annotations version="5.0.0" targetFramework="net481"
System.Data.DataSetExtensions version="4.5.0" targetFramework="net481"
System.Memory version="4.5.5" targetFramework="net481"
System.Numerics.Vectors version="4.5.0" targetFramework="net481"
System.Runtime.CompilerServices.Unsafe version="4.5.3" targetFramework="net481"

🖥️ System Requirements

Component Minimum Recommended
OS Windows 10 (1809) Windows 11 22H2
CPU x64 1.8GHz i5/Ryzen 5+
RAM 2GB 8GB+ for 500k+ rows
Storage 200MB SSD 500MB NVMe
Excel Not required 2016+ for previews

How to Get Ready to Use Your Application

Using Visual Studio 2022

  1. Download the script
  2. Extract it to your preferred location
  3. Double-click the file "ExcelFileProcessor.sln" to open the solution
  4. Look at the "Solution Explorer" → "References". If you find any reference with an exclamation mark it means that the reference or package is missing. Proceed as following:
    Right-click on your solution in Solution Explorer
    Select "Restore NuGet Packages"
  5. Clean the solution (Right-click on your solution → "Clean Solution")
  6. Rebuild your solution (Right-click on your solution → "Rebuild Solution")
  7. Execute your solution (Click on Start button in the Visual Studio toolbar)

Using Visual Studio Code

  1. Install Visual Studio Code from code.visualstudio.com if it has not been installed yet.
  2. Install these required extensions:
    C# (by Microsoft)
    NuGet Package Manager
  3. Open the project folder in VS Code:
    Click "File" → "Open Folder"
    Select the folder containing your solution
  4. Restore dependencies:
    Open the terminal (Ctrl+`)
    Run dotnet restore
  5. Build the solution:
    In the terminal, run dotnet build
  6. Run the application:
    Press F5 to start debugging, or
    In the terminal, run dotnet run

Dashboard

The Dashboard is the main screen of Excel Automation PowerTools Pro, providing quick access to various Excel file processing tools.

1. Dashboard Layout

The dashboard consists of the following buttons, each opening a specialized tool:

Button Functionality
Remove Empty Rows Opens the Empty Row Removal tool.
Remove Duplicates Opens the Duplicate Removal tool.
Merge Excel Files Opens the Excel File Merger tool.
Convert Excel to SQL Opens the Basic Excel-to-SQL Converter.
Advanced SQL Export Opens the Advanced Excel-to-SQL Converter (more customization options).

2. How to Use the Dashboard

2.1 Launching a Tool

  1. Click on any of the available buttons.
  2. A new window will open with the selected tool.
  3. Follow the instructions in the tool's interface to complete your task.

3. Available Tools & Their Functions

3.1 Remove Empty Rows

Purpose: Cleans up Excel files by removing blank rows automatically.

Use Case: Useful for data cleaning before analysis.

3.2 Remove Duplicates

Purpose: Detects and removes duplicate entries in Excel sheets.

Use Case: Ensures data uniqueness in customer lists, transaction records, etc.

3.3 Merge Excel Files

Purpose: Combines multiple Excel files into a single file.

Use Case: Consolidating reports from different sources.

3.4 Convert Excel to SQL (Basic & Advanced)

Basic Mode: Simple conversion of Excel data into SQL insert statements.

Advanced Mode: Custom table creation, data type mapping, and batch processing.

Use Case: Migrating Excel data to a database.
Note: This tool helps clean Excel files by identifying and removing completely blank rows while preserving all data-containing rows.

1. Remove Empty Rows from Excel File

1 Accessing the Tool

  1. Launch Excel Automation PowerTools Pro
  2. From the Dashboard, click "Remove Empty Rows"
  3. The Empty Row Remover window will open

2. User Interface Overview

2.1 Main Controls

Control Description
Upload File button Opens file selection dialog to choose Excel file
Check Empty Rows button Scans the file for completely empty rows
Remove Empty Rows button Deletes all identified empty rows
Download button Saves the cleaned file
Progress Bars Visual indicators for file processing
Status Labels Shows current operation status and row counts

3. Step-by-Step Usage

3.1 Uploading an Excel File

  • Click "Upload File"
  • Select your Excel file (.xlsx or .xls)
  • The system will:

    - Validate the file
    - Display the filename
    - Show total row count
    - Reset previous operations
Note: First row is always treated as headers.

3.2 Checking for Empty Rows

  • After successful upload, click "Check Empty Rows"
  • The tool will:

    - Scan each row
    - Count completely empty rows (where all cells are blank/NULL)
    - Display the count of empty rows found
    - Enable the Remove button if empty rows exist

3.3 Removing Empty Rows

  • Click "Remove Empty Rows"
  • The tool will:

    - Create a new version without empty rows
    - Show the new row count (original minus empty rows)
    - Enable the Download button

3.4 Saving the Cleaned File

  • Click "Download"
  • Choose save location and filename
  • The cleaned file will be saved as new .xlsx file
  • Success message will show file path
Tip: The original file remains unchanged - the tool always creates a new cleaned version.

4. Technical Details

4.1 What Constitutes an Empty Row?

A row is considered empty if:

  1. All cells contain NULL values OR
  2. All cells are empty strings OR
  3. All cells contain only whitespace OR
  4. All cells contain "NULL", "#N/A", or "-" (case insensitive)

4.2 Data Type Handling

The tool preserves:

  1. Text and numbers
  2. Dates (formatted as YYYY-MM-DD HH:MM:SS)
  3. Percentages (converted to proper % format)
  4. Original headers and column order

4.3 Performance Considerations

  1. Progress bars show real-time operation status
  2. Large files are processed efficiently
  3. Original file remains unchanged

5. Troubleshooting Guide

Issue Solution
Can't click Check Empty Rows Ensure file is uploaded first
No empty rows found File may already be clean
Download button disabled Complete removal step first
Error reading file Check file isn't open in Excel
Progress bars stuck Wait for operation to complete

6. Best Practices

  1. Backup original files before processing
  2. For very large files (>500,000 rows), consider splitting first
  3. Check results in the saved file before further processing
  4. Use the Advanced SQL Export for database integration

7. Frequently Asked Questions

Q: Does this modify my original file?

A: No, it creates a new file while leaving the original unchanged.

Q: What if my data has formulas that return empty strings?

A: These will be treated as empty cells and the row may be removed.

Q: Can I undo the removal?

A: Not within the tool - use your original file if needed.

Q: What's the maximum file size supported?

A: The tool can handle most Excel files, but extremely large files (>1GB) may require more memory.

Duplicate Row Remover Tool

1. Overview

The Duplicate Row Remover tool identifies and eliminates duplicate rows from Excel files while preserving unique data. Key features:

  • Select specific columns for duplicate comparison
  • Preview duplicate count before removal
  • Preserves first occurrence of duplicates
  • Maintains original data formatting
  • Progress tracking for large files

2. Getting Started

2.1 Accessing the Tool

  • Launch Excel Automation PowerTools Pro
  • From Dashboard, click "Remove Duplicates"
  • The Duplicate Remover window will open

3. User Interface

Control Description
Upload File Select Excel file for processing
Check Duplicates Analyze file after selecting columns
Remove Duplicates Eliminate identified duplicates
Download Save cleaned file
Progress Bars Visual indicators for operations
Row Counters Show before/after row counts

4. Step-by-Step Usage

4.1 Uploading Excel File

  1. Click "Upload File"
  2. Select your Excel file (.xlsx or .xls)
  3. The system will:
    • Validate the file structure
    • Display filename and total row count
    • Enable the duplicate checking button
Note: First row is always treated as column headers

4.2 Selecting Columns & Checking Duplicates

  1. Click "Check Duplicates"
  2. In the popup dialog:
    • Select which columns to check for duplicates
    • All columns are selected by default
    • Click OK to confirm
  3. The tool will:
    • Analyze selected columns
    • Display count of duplicate rows found
    • Enable the removal button if duplicates exist
Important: Duplicates are identified based on exact matches in ALL selected columns

4.3 Removing Duplicates

  1. Click "Remove Duplicates"
  2. The tool will:
    • Create a cleaned version of your data
    • Preserve the first occurrence of each duplicate set
    • Update the row counter with final count
    • Enable the download button

4.4 Saving Results

  1. Click "Download"
  2. Choose save location and filename
  3. The cleaned file will:
    • Maintain original column order
    • Keep all formatting
    • Have "_cleaned" added to filename

5. Technical Details

5.1 Duplicate Detection Logic

  • Case Sensitivity: Comparisons are case-sensitive
  • Null Handling: Empty cells match other empty cells
  • Data Types: Values are compared as strings
  • Preservation: First unique row is kept, subsequent duplicates removed

5.2 Performance Considerations

File Size Expected Processing Time Memory Usage
<10,000 rows 1-5 seconds Low
10,000-100,000 rows 5-30 seconds Moderate
>100,000 rows 30+ seconds High

6. Best Practices

  • Selective Columns: Choose only columns essential for duplicate identification
  • Backup First: Keep original files until verification
  • Large Files: Process during low system usage
  • Verification: Spot-check results before further processing

7. Frequently Asked Questions

Q: How are duplicates defined?
A: Rows are duplicates when ALL selected columns have identical values (case-sensitive).
Q: Which duplicate row is kept?
A: The first occurrence is preserved, subsequent duplicates are removed.
Q: Can I undo the removal?
A: No - always work on copies or save the cleaned file with a new name.
Q: Does formatting affect comparisons?
A: No - only cell values are compared, not formatting or formulas.

Excel File Merger Tool

1. Overview

The Excel File Merger tool combines two Excel files into a single file, preserving all data rows from both sources. It handles:

  1. Different file sizes
  2. Matching/mismatching column names (with warnings)
  3. Progress tracking during operations
  4. Clean output formatting

2. Getting Started

2.1 Accessing the Tool

  1. Launch Excel Automation PowerTools Pro
  2. From Dashboard, click "Merge Excel Files"
  3. The merge tool window will open

3. User Interface

Control Description
Upload File 1 Selects first Excel file
Upload File 2 Selects second Excel file
Merge Files Combines the two loaded files
Download Saves merged result
Progress Bars Visual indicators for each operation
File Info Displays Shows filename and row count for each file
Status Label Current operation status

4. Step-by-Step Usage

4.1 Uploading Files

  1. Click "Upload File 1" and select first Excel file (.xlsx or .xls)
    • Progress bar will show upload status
    • Filename and row count will display
  2. Click "Upload File 2" and select second file
    • Same validation occurs
    3. Note: Both files must be uploaded before merging

4.2 Merging Files

  1. After both files upload, click "Merge Files"
  2. The tool will:
    • Validate column structure
    • Show warning if column names mismatch
    • Combine all rows from both files
    • Display total merged row count

4.3 Saving Results

  1. Click "Download"
  2. Choose save location and filename
  3. Merged file saves as new .xlsx with:
    • All columns from first file
    • All data rows from both files
    • Original headers preserved

5. Technical Details

5.1 Merge Logic

  1. Column Matching: Uses first file's structure as base
  2. Row Handling: Appends all rows from both files
  3. Data Types: Preserves original formatting
  4. Progress Tracking: Real-time updates during operations

5.2 Validation Rules

  1. Column count must match between files
  2. Column names are compared (case-insensitive)
  3. User confirmation required if names differ

5.3 Output Format

  1. Single worksheet named "MergedData"
  2. Original column order from File 1
  3. No duplicate headers in data rows
  4. All cell values preserved as-is

6. Troubleshooting

Issue Solution
Merge button disabled Ensure both files uploaded
Column count mismatch Edit files to match columns first
Save fails Check write permissions
Progress bars stuck Wait for operation to complete

7. Best Practices

  1. Verify column structures match before merging
  2. For large files (>50,000 rows), allow extra processing time
  3. Check merged results before further processing
  4. Use original filenames in output for traceability

8. Frequently Asked Questions

Q: What if my files have different columns?
A: The tool requires matching column counts but can handle name differences with warning.
Q: Does header row appear twice in output?
A: No, only the first file's headers are kept.
Q: What's the maximum file size supported?
A: The tool can handle most Excel files, but performance may degrade above 100,000 rows.
Q: Can I merge more than two files?
A: Currently merges two files at a time - repeat process for additional files OR contact us for customized implementation.

Excel to Simple SQL Table Converter Tool

1. Overview

The Excel to Simple SQL Table Converter tool transforms Excel data into SQL table creation and data insertion scripts. Key features:

  1. Supports SQL Server, MySQL/MariaDB, and PostgreSQL
  2. Automatic data type detection
  3. Smart column name sanitization
  4. Progress tracking during conversion
  5. Database-specific syntax generation

2. Getting Started

2.1 Accessing the Tool

  • Launch Excel Automation PowerTools Pro
  • From Dashboard, click "Convert Excel to SQL"
  • The conversion tool window will open

3. User Interface

Control Description
Upload File Selects Excel file for conversion
Database Type Radio buttons to select target database
Convert Generates SQL script from Excel data
Download SQL Saves generated SQL script
Progress Bars Visual indicators for operations
SQL Output Preview of generated SQL script

4. Step-by-Step Usage

4.1 Uploading Excel File

  1. Click "Upload File"
  2. Select your Excel file (.xlsx or .xls)
  3. The system will:
    • Validate the file structure
    • Display filename and row count
    • Enable the Convert button
Note: First row is treated as column headers

4.2 Selecting Database Type

  1. Choose your target database:
    • SQL Server - For Microsoft SQL Server
    • MySQL/MariaDB - For open-source MySQL variants
    • PostgreSQL - For PostgreSQL databases

4.3 Generating SQL

  1. Click "Convert"
  2. The tool will:
    • Analyze column data types
    • Generate CREATE TABLE statement
    • Create INSERT statements for all rows
    • Display the SQL in the preview pane

4.4 Saving SQL Script

  1. Click "Download SQL"
  2. Choose save location and filename
  3. SQL script will be saved with:
    • Database configuration
    • Table creation statement
    • All data insertion statements

5. Technical Details

5.1 Data Type Conversion

Excel Data SQL Server MySQL PostgreSQL
Text (short) NVARCHAR(255) VARCHAR(255) TEXT
Text (long) NVARCHAR(MAX) TEXT TEXT
Numbers INT/DECIMAL INT/DECIMAL INT/NUMERIC
Dates DATETIME2 DATETIME TIMESTAMP
Boolean BIT TINYINT(1) BOOLEAN

5.2 Smart Features

  1. Automatic ID Handling: Detects valid ID columns or creates new ones
  2. Name Sanitization: Converts special characters to underscores
  3. Data Length Analysis: Recommends appropriate field sizes
  4. SQL Injection Protection: Properly escapes all values

6. Troubleshooting

Issue Solution
Convert button disabled Upload Excel file first
SQL syntax errors Check database type matches target
Data truncation warnings Review column data types
Special character issues Names are automatically sanitized

7. Best Practices

  1. Review generated SQL before executing
  2. For large files (>50,000 rows), test with sample first
  3. Check data type mappings match your requirements
  4. Use the Advanced version tool for complex schemas

8. Frequently Asked Questions

Q: How are column data types determined?
A: The tool analyzes actual data and column names to suggest appropriate types.
Q: What if my Excel has multiple sheets?
A: Only the first sheet is processed in the Simple converter.
Q: Can I customize the table name?
A: The table name is derived from the filename but can be edited in the SQL.
Q: How are special characters handled?
A: They're properly escaped in values and converted to underscores in names.

Advanced Excel to SQL Conversion Tool

1. Overview

The Advanced Excel to SQL Conversion tool transforms Excel data into a fully normalized database schema with:

  1. Multiple related tables
  2. Proper foreign key relationships
  3. Database-specific SQL syntax
  4. Automatic lookup table generation
  5. Data integrity constraints

2. Getting Started

2.1 Accessing the Tool

  • Launch Excel Automation PowerTools Pro
  • From Dashboard, click "Advanced SQL Export"
  • The advanced conversion tool window will open

3. User Interface

Control Description
Upload File Selects Excel file for conversion
Database Type Radio buttons to select target database
Advanced Convert Opens table relationship setup
Download SQL Saves generated SQL script
Progress Bars Visual indicators for operations
SQL Output Preview of generated SQL script

4. Step-by-Step Usage

4.1 Uploading Excel File

  1. Click "Upload File"
  2. Select your Excel file (.xlsx or .xls)
  3. The system will:
    • Validate the file structure
    • Display filename and row count
    • Enable the Advanced Convert button
    4. Note: First row is treated as column headers

4.2 Selecting Database Type

  1. Choose your target database:
    • SQL Server - For Microsoft SQL Server
    • MySQL/MariaDB - For open-source MySQL variants
    • PostgreSQL - For PostgreSQL databases

4.3 Setting Up Table Relationships

  1. Click "Advanced Convert"
  2. In the Table Setup dialog:
    • Enter a name for your main table
    • Check columns to convert to related tables
    • Click "Setup Related Tables"
  3. For each related table:
    • Review/change the table name
    • Add any additional columns needed
    • Click "OK" to confirm
  4. Click "Generate SQL" in the Table Setup dialog

4.4 Saving SQL Script

  1. Click "Download SQL"
  2. Choose save location and filename
  3. The generated script will include:
    • Database configuration
    • Table creation with constraints
    • Lookup table population
    • Main table data insertion

5. Technical Details

5.1 Database Schema Generation

The tool creates a normalized database structure:

  1. Main Table: Contains most data with foreign keys to lookup tables
  2. Lookup Tables: For each selected column, creates a related table with unique values
  3. Relationships: Proper foreign key constraints maintain data integrity

5.2 SQL Generation Process

  1. Creates all tables without constraints
  2. Populates lookup tables with distinct values
  3. Inserts main table data with proper foreign keys
  4. Adds constraints after data insertion

5.3 Supported Databases

Feature SQL Server MySQL PostgreSQL
Primary Keys IDENTITY AUTO_INCREMENT SERIAL
String Types NVARCHAR VARCHAR TEXT
Boolean BIT TINYINT(1) BOOLEAN
Constraints Added after data Added after data Added after data

6. Troubleshooting

Issue Solution
Conversion fails Check for duplicate values in lookup columns
Missing relationships Ensure columns are selected in Table Setup
SQL syntax errors Verify correct database type is selected
Performance issues For large files, consider simpler conversion first

7. Best Practices

  1. Review table relationships before generation
  2. For columns with many unique values, consider keeping in main table
  3. Test with sample data before processing large files
  4. Backup database before running generated scripts

8. Frequently Asked Questions

Q: How are lookup tables populated?
A: The tool extracts all unique values from the selected column and assigns IDs.
Q: Can I customize the table structure?
A: Yes, in the Table Setup dialog you can add columns and rename tables.
Q: What if my data changes after conversion?
A: You'll need to regenerate the SQL or update the database directly.
Q: How are foreign keys handled?
A: The tool automatically replaces original values with proper foreign key references.

Getting Help & Support

Support Options

📧
Email Support
For direct assistance from our technical team
support@mes-dev.com
📚
Knowledge Base
Step-by-step guides and troubleshooting articles
Please Review the current documentation and the FAQ section associated to each tool.
📧
Premium Support / Customization
For premium support customers and/or customizations
contact@mes-dev.com
Support Policy:
Regular License:
  • Response within 24 hours
  • 6 months of included support
Extended License:
  • Priority response within 8 hours
  • 12 months of included support
Support Limitations:
  • No installation support provided
  • No third-party integration support
  • Support covers tool functionality only

Before Contacting Support

  1. Check that you're using the latest version of the software
  2. Note any error messages you received
  3. Try reproducing the issue with a sample file
  4. Check the Knowledge Base for similar issues
Tip: Screenshots of the issue help us resolve your problem faster!