1

I have been trying to import data from an Excel file into SQL Server with ASP.NET Core MVC. But this code just doesn't run:

[HttpPost]
public IActionResult Index(ICollection<IFormFile> files)
{
    string line;

    using (SqlConnection con = new SqlConnection(@"Data Source=NT;Initial Catalog=StudentDB;Integrated Security=True"))
    {
        con.Open();

        using (StreamReader file = new StreamReader("TestFile.xlsx"))
        {
            while ((line = file.ReadLine()) != null)
            {
                string[] fields = line.Split(',');

                SqlCommand cmd = new SqlCommand("INSERT INTO Persons(ContactID, FirstName, SecondName, Age) VALUES (@contactid, @firstname, @secondname, @age)", con);
                cmd.Parameters.AddWithValue("@id", fields[0].ToString());
                cmd.Parameters.AddWithValue("@firstname", fields[1].ToString());
                cmd.Parameters.AddWithValue("@secondname", fields[2].ToString());

                cmd.ExecuteNonQuery();
            }
        }
    }

    return View();
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ohhhkeo
  • 21
  • 2
  • What's wrong with this code? Any error? Did you debug the code and check what's going on in it? – Chetan Oct 13 '17 at 00:55
  • It says for the ("TestFile.xlsx") ,Cannot change from string to System.IO.Stream – ohhhkeo Oct 13 '17 at 01:04
  • can you help me to code on how to upload an excel file data and store into the sqlserver of my visual studio 2017? I don't quite know where to start – ohhhkeo Oct 13 '17 at 01:10
  • If you don't want to use oledb the you can explore interop libraries for office. You can read excel files using it. – Chetan Oct 13 '17 at 01:38
  • Excel XLS(X) is a binary file, you can't easily parse it as text like CSV format (use byte array instead). You can try other libraries like EPPlus, NPOI or Office interop to get Excel file contents. – Tetsuya Yamamoto Oct 13 '17 at 01:39
  • do you know anywhere where i could get codes for those? – ohhhkeo Oct 13 '17 at 01:55

1 Answers1

0

I would recommend using the EPPlus open source free library which you can install through Nuget or visit(https://www.nuget.org/packages/EPPlus/) which reads Excel files without having Excel or any other Interops installed.

Below is the updated code using Epplus. I am assuming you don't have a header row in your excel file(if you do just change the var row = 1 to var row = 2 which would mean to start at Row 2 and ignore the header)

  using (var con = new SqlConnection(@"Data Source=NT;Initial Catalog=StudentDB;Integrated Security=True"))
        {
            con.Open();

            var excelFile = new FileInfo(@"TestFile.xlsx");
            using (var epPackage = new ExcelPackage(excelFile))
            {
                var worksheet = epPackage.Workbook.Worksheets.First();

                for (var row = 1; row <= worksheet.Dimension.End.Row; row++)
                {
                    var rowValues = worksheet.Cells[row, 1, row, worksheet.Dimension.End.Column];
                    var cmd = new SqlCommand("INSERT INTO Persons(ContactID, FirstName, SecondName, Age) VALUES (@contactid, @firstname, @secondname, @age)", con);
                    cmd.Parameters.AddWithValue("@contactid", rowValues["A1"].Value);
                    cmd.Parameters.AddWithValue("@firstname", rowValues["B1"].Value);
                    cmd.Parameters.AddWithValue("@secondname", rowValues["C1"].Value);
                    cmd.Parameters.AddWithValue("@age", rowValues["D1"].Value);

                    cmd.ExecuteNonQuery();
                }

            }

        }

Some of the parameters in your AddWithValue didn't match the parameter you declared in your insert statement, and I cleaned those up as well. Using the code I was able to read a test xlsx file I created to match your layout and insert into the database. Hope it helps.

Abdul Samad
  • 441
  • 2
  • 11
Darthchai
  • 757
  • 8
  • 17
  • Thanks for the help, i tried using the code you've provided. I tried to run it and it came to a blank page.. > – ohhhkeo Oct 13 '17 at 07:27
  • What error are you getting when debugging? Are you seeing records in your database? – Darthchai Oct 13 '17 at 14:50
  • There's no error. the application could run but it just goes to empty page. no records being added into the database. – ohhhkeo Oct 16 '17 at 03:38