Parsing excel .xls database into sql server database

Question

I have asp.net mvc4 project, where have database with students, also have old database in .xls format and I need to migrate all values from old database into new one sql server database. In my mvc project I have membership controller which include method for Register new student. Also I'm write console application which parse .xls table and insert all values into my new database via Register method.

Console application:

static void Main(string[] args)
    {
        string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\MigrateExelSql\Include\TestDb.xlsx; Extended Properties=Excel 12.0;";

        MembershipController mc = new MembershipController();
        Student student = new Student();
        student.Username = null;
        student.Password = "password";
        student.Email = null;
        student.EntryYear = 2014;
        student.PassportNumber = 0;
        student.IsApproved = true;
        student.PasswordFailuresSinceLastSuccess = 0;
        student.IsLockedOut = false;

        using(OleDbConnection connection = new OleDbConnection(con))
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection); 
            using(OleDbDataReader dr = command.ExecuteReader())
            {
                 while(dr.Read())
                 {
                     string row1Col0 = dr[0].ToString();
                     Console.WriteLine(row1Col0);
                     string row1Col1 = dr[1].ToString();
                     Console.WriteLine(row1Col1);
                     Console.WriteLine();

                     student.Username = row1Col0;
                     student.Email = row1Col1;

                     try
                     {
                         mc.Register(student);
                     }
                     catch (Exception ex)
                     {
                         Console.WriteLine(ex.Message);
                     }
                 }
            }
        }

        Console.ReadKey();
    }

Register method

public static MembershipCreateStatus Register(string Username, string Password, string Email, bool IsApproved, string FirstName, string LastName)
        {
            MembershipCreateStatus CreateStatus;
            System.Web.Security.Membership.CreateUser(Username, Password, Email, null, null, IsApproved, null, out CreateStatus);

        if (CreateStatus == MembershipCreateStatus.Success)
        {
            using (UniversityContext Context = new UniversityContext(ConfigurationManager.ConnectionStrings[0].ConnectionString))
            {
                Student User = Context.Students.FirstOrDefault(Usr => Usr.Username == Username);
                User.FirstName = FirstName;
                User.LastName = LastName;
                Context.SaveChanges();

            }

            if (IsApproved)
            {
                FormsAuthentication.SetAuthCookie(Username, false);
            }
        }

        return CreateStatus;
    }

ActionResult POST

public ActionResult Register(Student student)
        {
            Register(student.Username, student.Password, student.Email, true, student.FirstName, student.LastName);

            return RedirectToAction("Index", "Membership");
        }

Everything work fine when I try to add students from web application, but when I try to add from my parsed database it was get the error on the next line

System.Web.Security.Membership.CreateUser(Username, Password, Email, null, null, IsApproved, null, out CreateStatus);

in my Register method, that invalidAnswer. I'm change null to "123456789", then next error will be invalidQuestion, then I'm also change from null to "123456789". And after this it's tell me that my invalidPassword. Also I'm added next line into my membership provider's line in my web.config requiresQuestionAndAnswer="false".

I have no idea why it's not working. Does anybody have any ideas?

Answer 1

Whenever I'm required to do anything like import data from spreadsheets like this simply write an Importer console app in my solution. I also use LINQ to CSV which you can get from Nuget and its a really useful tool to use.

It allows you to read in the data from your spreadsheet into the relevant class objects, and from there once you have validated each object you can simply create new objects in your Database using your Data Layer.

Answer 2

Since xls (not xlsx) files are basically text files in which contents are separated by usually a tab character (or maybe some certain spercial charchters) a better approach would be to read all the lines in the file and store them in a list.

Then you can split each element at the time you are inserting them in your database. This can be easily done using a foreach loop like:

foreach (var i in xlsList) {

    String s [] = i.Split('\t').ToArray();  // refer to your file 
    for (int j = 0; j < s.lenght; j++) {
       Your SQL statement in here
    }

}