Question read from excel file and write into database table

raysefo

Well-known member
Joined
Feb 22, 2019
Messages
155
Programming Experience
10+
Hi,

I have an asp.net core 2.1 web application. I would like to read an excel file and insert it 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.
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
365
Location
Virginia Beach, VA
Programming Experience
10+
Is this a constant periodic import, or just something that happens one a month or only once? If the frequency is low, just manually save the Excel data to a .CSV, and then parse and read the .CSV in your .NET core app.
 

raysefo

Well-known member
Joined
Feb 22, 2019
Messages
155
Programming Experience
10+
It is periodic maybe once a week but the important point is, the client has to upload it from the web application.
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
365
Location
Virginia Beach, VA
Programming Experience
10+
Sounds simple enough. Only allow .CSV file uploads. ;)
 

raysefo

Well-known member
Joined
Feb 22, 2019
Messages
155
Programming Experience
10+
The excel might have 5000 records at most, should I use sqlbulkcopy?

Here is my working code:
C#:
[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");
        }
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
365
Location
Virginia Beach, VA
Programming Experience
10+
It is easier and faster to parse a comma delimited text file (e.g. .CSV) as opposed to decompressing a .zip file into component XML files, and then parsing the component XML files (e.g. a .XLSX). So for the same 5000 records you will be expending less computing resources to process them for import into your database.

Furthermore, you could require that the .CSV be compressed prior to upload and you decompress on the server. .CSV files compress very well and have less overhead than XML for the same data.
 
Top Bottom