locked
Read from excel and insert into database table RRS feed

  • Question

  • User-1104215994 posted

    Hi,

    I have an asp.net core 2.1 web application. I would like to read an excel file and insert into a database table. I think OleDb is not supported so I need some other 3rd party library. Is there any good tutorial about reading from excel and inserting into DB?

    Best Regards.

    Monday, September 2, 2019 10:07 AM

Answers

  • User-1104215994 posted

    The excel might have 5000 records, should I still use <g class="gr_ gr_31 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="31" data-gr-id="31">sqlbulkcopy</g>?

    Here is my working code:

    [HttpPost]
            [Authorize]
            public async Task<IActionResult> Upload(IFormFile postedFile)
            {
                if (postedFile == null || postedFile.Length == 0)
                {
                    return RedirectToAction("ImportExcel");
                }
    
                //Get file
                var newfile = new FileInfo(postedFile.FileName);
                var fileExtension = newfile.Extension;
    
                //Check if file is an Excel File
                if (fileExtension.Contains(".xls"))
                {
                    using (MemoryStream ms = new MemoryStream())
                    {
                        await postedFile.CopyToAsync(ms);
    
                        using (ExcelPackage package = new ExcelPackage(ms))
                        {
                            ExcelWorksheet workSheet = package.Workbook.Worksheets["Game"];
                            int totalRows = workSheet.Dimension.Rows;
    
                            List<GameBanks> customerList = new List<GameBanks>();
    
                            for (int i = 2; i <= totalRows; i++)
                            {
                                customerList.Add(new GameBanks
                                {
                                    ProductDescription = workSheet.Cells[i, 1].Value.ToString(),
                                    ProductCode = workSheet.Cells[i, 2].Value.ToString(),
                                    UnitPrice = Convert.ToDouble(workSheet.Cells[i, 3].Value),
                                    Quantity = Convert.ToInt16(workSheet.Cells[i,4].Value),
                                    Version = workSheet.Cells[i,5].Value.ToString(),
                                    Currency = workSheet.Cells[i,6].Value.ToString(),
                                    TotalPrice = Convert.ToDouble(workSheet.Cells[i,7].Value),
                                    Status = Convert.ToInt16(workSheet.Cells[i,8].Value),
                                    Used = Convert.ToInt16(workSheet.Cells[i,9].Value)
                                });
                            }
    
                            _context.GameBanks.AddRange(customerList);
                            await _context.SaveChangesAsync();
                        }
                    }
                    
                }
    
                
    
                return RedirectToAction("Index");
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 3, 2019 6:13 AM
  • User711641945 posted

    Hi cenk1536,

    I tried BulkCopy but i think it need all the columns in the table in order to process. Is that true?

    Yes,it is.And If you use SqlBulkCopy,you need to check the below:

    • Column order - order of columns you are inserting with SqlBulkCopy needs to exactly match the order of columns in database table
    • Column names - column names of DataTable C# object need to exactly match the names of database table column names

    I mean I will only insert couple of columns which I can do it with my sample code.

    This thread have resolved actually.If you want to insert couple of columns,I suggest that you could post a new thread.

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 17, 2019 6:38 AM

All replies

  • User475983607 posted

    The standard practice is using SQL Server not C# to bulk load data.  I use BCP and BulkLoad.

    https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017

    Monday, September 2, 2019 11:11 AM
  • User-1104215994 posted

    Hi <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="5" data-gr-id="5">mgebhard</g>,

    This should be from web application not directly from SQL server because <g class="gr_ gr_112 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="112" data-gr-id="112">client</g> will upload data into <g class="gr_ gr_85 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="85" data-gr-id="85">database</g>.

    Monday, September 2, 2019 11:18 AM
  • User475983607 posted

    This should be from web application not directly from SQL server because client will upload data into database.

    Professional shops generally bulk load a file.  ASP.NET Core has the ability to bulk load a file out-of-the-box.

    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.8

    Loading a table is very simple.  The hard part is validating the client file.  Using Excel makes this even more difficult because there is a wide range of possible data issues.  A delimited file is best and the file layout is usually discussed up front.

    However, there are Excel API NuGet packages that you can include in your project.  A simple Google search...

    https://www.nuget.org/packages/EPPlus.Core/

    Monday, September 2, 2019 12:16 PM
  • User-1104215994 posted

    The excel might have 5000 records, should I still use <g class="gr_ gr_31 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="31" data-gr-id="31">sqlbulkcopy</g>?

    Here is my working code:

    [HttpPost]
            [Authorize]
            public async Task<IActionResult> Upload(IFormFile postedFile)
            {
                if (postedFile == null || postedFile.Length == 0)
                {
                    return RedirectToAction("ImportExcel");
                }
    
                //Get file
                var newfile = new FileInfo(postedFile.FileName);
                var fileExtension = newfile.Extension;
    
                //Check if file is an Excel File
                if (fileExtension.Contains(".xls"))
                {
                    using (MemoryStream ms = new MemoryStream())
                    {
                        await postedFile.CopyToAsync(ms);
    
                        using (ExcelPackage package = new ExcelPackage(ms))
                        {
                            ExcelWorksheet workSheet = package.Workbook.Worksheets["Game"];
                            int totalRows = workSheet.Dimension.Rows;
    
                            List<GameBanks> customerList = new List<GameBanks>();
    
                            for (int i = 2; i <= totalRows; i++)
                            {
                                customerList.Add(new GameBanks
                                {
                                    ProductDescription = workSheet.Cells[i, 1].Value.ToString(),
                                    ProductCode = workSheet.Cells[i, 2].Value.ToString(),
                                    UnitPrice = Convert.ToDouble(workSheet.Cells[i, 3].Value),
                                    Quantity = Convert.ToInt16(workSheet.Cells[i,4].Value),
                                    Version = workSheet.Cells[i,5].Value.ToString(),
                                    Currency = workSheet.Cells[i,6].Value.ToString(),
                                    TotalPrice = Convert.ToDouble(workSheet.Cells[i,7].Value),
                                    Status = Convert.ToInt16(workSheet.Cells[i,8].Value),
                                    Used = Convert.ToInt16(workSheet.Cells[i,9].Value)
                                });
                            }
    
                            _context.GameBanks.AddRange(customerList);
                            await _context.SaveChangesAsync();
                        }
                    }
                    
                }
    
                
    
                return RedirectToAction("Index");
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 3, 2019 6:13 AM
  • User711641945 posted

    Hi cenk1536,

    The excel might have 5000 records, should I still use sqlbulkcopy?

    Yes,you could.SqlBulkCopy is an efficient solution to insert large number of records quickly.And you could follow the link that mgebhard posted.

    Best Regards,

    Rena

    Wednesday, September 4, 2019 7:24 AM
  • User-1104215994 posted

    I tried BulkCopy but <g class="gr_ gr_29 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="29" data-gr-id="29">i</g> think it <g class="gr_ gr_43 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="43" data-gr-id="43">need</g> all the columns in the table in order to process. Is that true? I mean I will only insert <g class="gr_ gr_232 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="232" data-gr-id="232">couple</g> of columns which I can do it with my sample code.

    Wednesday, September 11, 2019 12:29 PM
  • User711641945 posted

    Hi cenk1536,

    I tried BulkCopy but i think it need all the columns in the table in order to process. Is that true?

    Yes,it is.And If you use SqlBulkCopy,you need to check the below:

    • Column order - order of columns you are inserting with SqlBulkCopy needs to exactly match the order of columns in database table
    • Column names - column names of DataTable C# object need to exactly match the names of database table column names

    I mean I will only insert couple of columns which I can do it with my sample code.

    This thread have resolved actually.If you want to insert couple of columns,I suggest that you could post a new thread.

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 17, 2019 6:38 AM