Uploading Excel Files to SSMS Table

Uploading Excel Files to SSMS Table: Lessons Learned

Introduction
I recently worked on an API project where I needed to upload files in Excel format and store the data in a SQL Server Management Studio (SSMS) table. This involved using several tools and techniques including memory stream, load from collection, Excel package, DataTable, and DataSet. Here’s a detailed documentation of what I learned, the problems I faced, and the solutions I implemented.

Tools and Technologies Used

• Memory Stream: To handle the Excel file data in memory.
• Load from Collection: To convert the data into a collection format.
• Excel Package (EPPlus): To read and manipulate Excel files.
• DataTable & DataSet: To structure the data before inserting it into the database.
• SQL Server Management Studio (SSMS): To store the data.

Steps and Implementation

1. Reading the Excel File:

   • Used the ExcelPackage class from the EPPlus library to load the Excel file.
   • Employed MemoryStream to read the file data into memory.

   using (var memoryStream = new MemoryStream(fileData))
   {
       using (var package = new ExcelPackage(memoryStream))
       {
           var worksheet = package.Workbook.Worksheets.First();
           // Further processing
       }
   }
   

2. Fetching All Worksheets:

   • Iterated through all worksheets in the Excel file and extracted the selected columns.

   foreach (var worksheet in package.Workbook.Worksheets)
   {
       for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
       {
           rowData.Add(new MyDataModel
           {
               Column1 = worksheet.Cells[row, 1].Text,
               Column2 = worksheet.Cells[row, 2].Text,
               WorksheetName = worksheet.Name
               // More columns as needed
           });
       }
   }
   

3. Converting Collection to DataTable:

   • Created a DataTable and populated it with the data from the collection.

   var dataTable = new DataTable();
   dataTable.Columns.Add("Column1");
   dataTable.Columns.Add("Column2");
   dataTable.Columns.Add("WorksheetName");
   // More columns as needed
   
   foreach (var data in rowData)
   {
       var row = dataTable.NewRow();
       row["Column1"] = data.Column1;
       row["Column2"] = data.Column2;
       row["WorksheetName"] = data.WorksheetName;
       // More columns as needed
       dataTable.Rows.Add(row);
   }
   

4. Inserting Data into SSMS Table:

   • Used SqlBulkCopy to efficiently insert the data from the DataTable into the SSMS table.

   using (var connection = new SqlConnection(connectionString))
   {
       connection.Open();
       using (var bulkCopy = new SqlBulkCopy(connection))
       {
           bulkCopy.DestinationTableName = "MyTable";
           bulkCopy.WriteToServer(dataTable);
       }
   }
   

5. Handling Excel Export with Multiple Worksheets:

   • Added an attribute WorksheetName to store the original worksheet names.
   • Used this attribute to recreate the Excel file with data in the correct worksheets during export.

   var worksheets = dataTable.AsEnumerable()
                             .GroupBy(row => row["WorksheetName"].ToString())
                             .ToList();
   
   using (var package = new ExcelPackage())
   {
       foreach (var worksheetGroup in worksheets)
       {
           var worksheet = package.Workbook.Worksheets.Add(worksheetGroup.Key);
           // Populate worksheet with data
       }
       var fileData = package.GetAsByteArray();
       // Return file data
   }
   

Problems Faced and Solutions

1. Handling Large Files:

   • Problem: Large Excel files caused memory issues.
   • Solution: Implemented pagination and batch processing to handle large datasets incrementally.

2. Data Type Mismatches:

   • Problem: Mismatches between Excel data types and SQL table columns.
   • Solution: Added data validation and type conversion logic before inserting into the database.

3. Error Handling:

   • Problem: Lack of proper error handling caused the process to fail silently.
   • Solution: Implemented comprehensive try-catch blocks and logging to capture and debug errors effectively.

4. Performance Issues:

   • Problem: Initial implementation was slow for large datasets.
   • Solution: Optimized the process by minimizing read/write operations and using SqlBulkCopy for efficient data transfer.

5. Multiple Worksheets Handling:

   • Problem: During data export, all data was being created in a single worksheet.
   • Solution: Added a WorksheetName attribute to each row to keep track of the original worksheet and used it to organize data correctly during export.

Conclusion
This project taught me valuable lessons about handling Excel files, data transformation, and efficient database insertion. Overcoming the challenges improved my problem-solving skills and reinforced the importance of robust error handling and performance optimization in backend development.

By documenting these learnings, I hope to provide a useful reference for anyone facing similar challenges. Happy coding!