A modern, user-friendly Windows Forms application built with .NET 10 that imports Excel data into SQL Server databases with high-performance bulk import capabilities.

• Excel File Support: Reads Excel files (.xlsx, .xls) using ClosedXML library
• Automatic Header Detection: Smart detection of header rows in Excel files
• Custom Data Start Row: Specify from which row the actual data begins
• Database Connection: Connect to SQL Server using Windows Authentication or SQL Authentication
• Database/Table Selection: Browse and select target database and table
• ?? Searchable Table Selection: Type to quickly find tables in large databases
• ?? Create New Table: Automatically generate a table schema based on Excel data
• Column Mapping: Map Excel columns to database columns with visual interface
• Selective Import: Choose which columns to import
• ? High-Performance Bulk Import: 10-100x faster imports for large datasets (1,000+ rows)
• Progress Tracking: Real-time progress bar and status updates during import
• Auto-Mapping: Automatically maps Excel columns to SQL columns based on name matching
• ?? Smart Data Type Inference: Automatically suggests appropriate SQL data types
• ?? Pre-Import Data Validation: Validate all data before importing to catch errors early

• Wizard-Style Navigation: 4-step process with Previous/Next buttons
• Modern Design: Clean, professional interface with organized tabs and custom icon
• Data Preview: View Excel data before importing
• Import Summary: Review all settings before starting the import
• ?? Type-to-Search: Quickly find tables by typing in the table dropdown
• Error Handling Options: Choose between Transaction, Skip Errors, or Stop on Error modes

• Automatic Bulk Import: Uses SqlBulkCopy for datasets with 1,000+ rows
• Smart Method Selection: Automatically chooses the fastest import method
• Batch Processing: Processes data in 5,000-row batches for optimal speed
• Memory Efficient: Streaming enabled for large datasets
• Progress Reporting: Real-time updates every 1,000 rows

Performance Benchmarks:

• 10,000 rows: ~8 seconds (vs. 150 seconds before)
• 50,000 rows: ~20 seconds (vs. 12 minutes before)
• 100,000 rows: ~40 seconds (vs. 25 minutes before)

See PERFORMANCE_OPTIMIZATION.md for detailed benchmarks and tuning guide.

• .NET 10.0
• SQL Server (any edition)
• Windows operating system

• ClosedXML (0.105.0): For reading Excel files
• Dapper (2.1.66): For efficient database operations
• Microsoft.Data.SqlClient (6.1.3): For SQL Server connectivity

1. Click "Browse..." to select your Excel file
2. The application will automatically detect if the first row contains headers
3. Adjust the "Data starts at row" value if needed
4. Preview your data in the grid

1. Enter your SQL Server name (e.g., localhost, .\SQLEXPRESS, or server.domain.com)
2. Choose authentication method:
   • Windows Authentication: Uses your current Windows credentials
   • SQL Authentication: Enter username and password
3. Click "Test Connection" to verify the connection
4. Select the target database from the dropdown
5. Select or type the target table name (searchable dropdown)
6. ?? OR Click "Create New Table" to create a new table based on your Excel structure

1. Click the "? Create New Table" button
2. Enter a table name
3. Review the automatically detected column names and data types
4. Modify data types if needed:
   • NVARCHAR(50/100/255/MAX) for text
   • INT/BIGINT for whole numbers
   • DECIMAL(18,2) for decimal numbers
   • BIT for boolean values
   • DATE/DATETIME for dates
5. Click "Create" to generate the table
6. The new table will be automatically selected

1. Review the automatic column mappings
2. Check/uncheck columns you want to import
3. Change SQL column mappings if needed using the dropdown
4. Use "Select All" or "Deselect All" for bulk operations
5. The data type for each SQL column is displayed for reference

1. Review the import summary
2. Click "Start Import" to begin
3. Monitor the progress bar and status messages
4. Wait for completion confirmation

ImportExcelIntoDatabase/
??? Models/
?   ??? ColumnMapping.cs       # Column mapping data model
?   ??? ExcelData.cs            # Excel data container
??? Services/
?   ??? ExcelService.cs         # Excel file reading logic
?   ??? DatabaseService.cs      # Database operations
??? Form1.cs                    # Main form logic
??? Form1.Designer.cs           # Form UI design
??? CreateTableDialog.cs        # ?? New table creation dialog
??? Program.cs                  # Application entry point

• Loads Excel files using ClosedXML
• Automatically detects headers
• Provides data preview
• Reads all rows for import

• Tests database connections
• Retrieves databases and tables
• Gets table column definitions
• ?? Creates new tables with custom schemas
• ?? Infers SQL data types from Excel data
• Performs bulk data import using Dapper
• Handles both Windows and SQL Authentication

• Shows Excel column structure
• Suggests appropriate SQL data types
• Allows customization of column names and types
• Validates table and column names

• Wizard-style UI with 4 tabs
• Validation at each step
• Progress tracking
• Error handling and user feedback
• ?? Searchable table dropdown

Instead of scrolling through a long list of tables, you can now:

• Start typing the table name to filter results
• Auto-complete suggestions appear as you type
• Faster table selection in databases with many tables

The application can now create database tables for you:

1. Automatic Data Type Detection: Analyzes your Excel data to suggest appropriate SQL types

   • Numeric columns ? INT or DECIMAL
   • Date columns ? DATE
   • Boolean columns ? BIT
   • Text columns ? NVARCHAR with appropriate length

2. Customizable Schema: Edit suggested data types before creation

3. Smart Column Naming: Uses Excel headers as column names (validated for SQL compatibility)

4. Auto-ID Column: Automatically adds an identity primary key column

Example:

Excel Columns:
- FirstName ? NVARCHAR(100)
- Age ? INT
- Salary ? DECIMAL(18,2)
- HireDate ? DATE
- IsActive ? BIT

Generated SQL Table:
CREATE TABLE NewEmployees (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(100),
    Age INT,
    Salary DECIMAL(18,2),
    HireDate DATE,
    IsActive BIT
);

Imports with 1,000+ rows now use SqlBulkCopy for ultra-fast data loading:

• Batch Processing: Data is processed in batches of 5,000 rows
• Streaming: Reduces memory usage for large imports
• Progress Reporting: Updates every 1,000 rows

Performance Improvements:

• 10,000 rows: From 150 seconds to ~8 seconds
• 50,000 rows: From 12 minutes to ~20 seconds
• 100,000 rows: From 25 minutes to ~40 seconds

See PERFORMANCE_OPTIMIZATION.md for details.

The application includes comprehensive error handling:

• Invalid Excel file formats
• Database connection failures
• Missing or invalid column mappings
• Import errors (continues with remaining rows)
• User-friendly error messages
• ?? Table creation validation

1. Large Files: The application automatically uses high-performance bulk import for 1,000+ rows
2. ? Performance: For maximum speed, use Transaction mode with pre-validated data
3. Column Mapping: Auto-mapping works best when Excel headers match SQL column names
4. Data Types: Empty strings are converted to NULL for non-string columns
5. Testing: Always test with a small dataset first
6. Backup: Backup your database before importing large amounts of data
7. ?? Table Search: Use Ctrl+F in the table dropdown to quickly search
8. ?? New Tables: Let the app suggest data types, but review them before creating
9. ?? Data Type Inference: Based on preview data (first 10 rows), so verify for accuracy
10. ?? Validation: Use "Validate Data" button before large imports to catch all errors at once

• Verify SQL Server is running
• Check firewall settings
• Ensure SQL Server allows remote connections (if connecting remotely)
• Verify authentication credentials

• Check data types match between Excel and SQL columns
• Ensure required SQL columns are mapped
• Verify no constraints (foreign keys, check constraints) are violated
• Check SQL column sizes for text data

• Invalid table name: Must start with a letter, use only alphanumeric characters and underscores
• Duplicate table: Table name already exists in the database
• Permission denied: User account needs CREATE TABLE permission

This project uses open-source libraries:

• ClosedXML: MIT License
• Dapper: Apache 2.0 License
• Microsoft.Data.SqlClient: MIT License

Possible improvements:

• Support for other database types (MySQL, PostgreSQL)
• Data transformation options
• Duplicate detection
• Data validation rules
• Export mapping configurations
• Scheduled imports
• Multiple worksheet support
• Create table from Excel structure ? Implemented!
• Searchable table dropdown ? Implemented!
• High-performance bulk import ? Implemented!
• Pre-import data validation ? Implemented!
• Application icon ? Implemented!