create a bulk upload from excel

Question

I have to make a bulk upload, there is 30000 data in excel, it has to be uploaded within 15 minutes. Time matters how much time will it take to upload the data from Excel to the database.The program I have done is taking 30 minutes to upload.

Amit Mohanty · Answer

Reading a large amount of data from an Excel file might require an optimized approach, so you can use EPPlus and batch processing to handle large Excel files. Check the below example, this may help you. public class EmployeeAttendance { public long EmployeeID { get; set; } public DateTime Date { get; set; } public string AttendanceStatus { get; set; } } using OfficeOpenXml; public ActionResult ReadAndProcessExcel() { string filePath = @"D:\ATTENDANCE.xlsx"; try { int batchSize = 1000; // Process 1000 rows at a time if (!string.IsNullOrEmpty(filePath)) { ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (var package = new ExcelPackage(new System.IO.FileInfo(filePath))) { ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; int rowCount = worksheet.Dimension.Rows; int columnCount = worksheet.Dimension.Columns; if (rowCount > 1) { for (int startRow = 2; startRow employeeAttendances = new List (); for (int row = startRow; row employeeAttendances) { DataTable dataTable = ConvertListToDataTable(employeeAttendances); string connectionString = "Your_SQL_Server_Connection_String"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "Your_Destination_Table_Name"; foreach (DataColumn col in dataTable.Columns) { bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName); } bulkCopy.WriteToServer(dataTable); } } } private DataTable ConvertListToDataTable (List data) { DataTable dataTable = new DataTable(); var properties = typeof(T).GetProperties(); foreach (var prop in properties) { dataTable.Columns.Add(prop.Name, prop.PropertyType); } foreach (T item in data) { DataRow row = dataTable.NewRow(); foreach (var prop in properties) { row[prop.Name] = prop.GetValue(item); } dataTable.Rows.Add(row); } return dataTable; }

neeraj kumar · Answer

program is uploaded please check it & help me its urgent sample date column is not fixed , its changable

neeraj kumar · Answer

call this method to insert data- Controller:- DataTable Status = await objService.BulkAttendanceUpload(newdt, Convert.ToInt64(Session["MappingID"])); Services:- public async Task BulkAttendanceUpload(DataTable DT, long CreatedBy) { try { int srno = 1; DT.Columns.Add(new DataColumn("Status", typeof(string))); var param = new DynamicParameters(); DataRow dr = null; for (int i = 0; i ("usp_BulkAttendanceUpload", param, commandType: CommandType.StoredProcedure).FirstOrDefault(); } } return DT; } catch (Exception ex) { throw ex; } }

Prasad Raveendran · Answer

Could you kindly share the program you've developed for uploading the CSV file? As recommended by Sam Hobbs, there are various methods available. If you have specific requirements and the preferred technology, we can explore how to accomplish this.

Sam Hobbs · Answer

You can export the data as a CSV file and import using a SQL bulk insert. Or you could use OPENROWSET. See the following. Use BULK INSERT or OPENROWSET(BULK...) to import data to SQL Server - SQL Server | Microsoft Learn https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server?view=sql-server-ver16 Or you could use: Import and Export Data with the SQL Server Import and Export Wizard - SQL Server Integration Services (SSIS) | Microsoft Learn https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/import-and-export-data-with-the-sql-server-import-and-export-wizard?view=sql-server-ver16

create a bulk upload from excel

Answers (5)