Data Import to SQL Server 2005 from a Spreadsheet using C#

Hello Experts,

I have a little medium to complex requirement on uploading / Importing data from the spreadsheet to the SQL Server table.
Please refer my attachments for the tables structure and data

The spreadsheet will have a employee firstname and lastname which has to be looked for matching records in the EE_Master, Team table and the Employee ID, Team Id, startdate, endDate will be taken out and kept in the new table called TM_ImportMembers. Rest all other data should be a direct import by column matching if available or put NULL

My primary goal is to import good records from the spreadsheet and upload it in the  TM_ImportMembers

If I have a two or more employees , then i need alert the user and skip an update for this user.
As of now we will have only data for the TeamID 5 (just to make it simple)

Files / Sample data
1. The three majors tables that i use are in the ( Table-Structure.txt ).
2. Sample data for the import in to TM_ImportMemberstable ( Sample Data to be imported to TM_ImportMembers_Table.xlsx )
3. The file should be uploaded by a File upload control in C# and good matching ones will be shown in one gridview and bad ones are shown in bad records grid view


Table-Structure.txt
Sample-Data-to-be-imported-to-TM.xlsx
SubbuUSAAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jini JoseConnect With a Mentor Senior .Net DeveloperCommented:
  the below code is updating a banks and branches tables from an excel sheet.

you can change wherever you required.


the below is the excel column headings.
   NAME OF   THE BANK,   IFSC CODE,   MICR CODE,   BRANCH NAME,   ADDRESS,   CONTACT DETAILS,   CENTRE,   DISTRICT,   STATE

bool UpdateBanks(string FileName)
        {
            bool completed = false;
            try
            {
                OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);
                DataTable dt = new DataTable();
                da.Fill(dt);
                Object obj = null;
                SqlParameter[] oparam = new SqlParameter[1];
                foreach (DataRow drBranches in dt.Rows)
                {
                    string BankId = "";
                    //check bank exists in banks table
                    Query = "select bankid from banks where BankName=@BankName";
                    oparam[0] = new SqlParameter("@BankName", drBranches["Name of The Bank"].ToString());
                    obj = SQLData.ExecuteScalar(Util.Constring, CommandType.Text, Query, oparam);

                    if (obj == null)
                    //if not insert
                    {
                        Query = "Select isnull(max(bankid),0)+1 from Banks";
                        obj = SQLData.ExecuteScalar(Util.Constring, CommandType.Text, Query);
                        BankId = obj.ToString();
                        Query = "Insert into Banks select " + BankId + ",@BankName";
                        oparam = new SqlParameter[1];
                        oparam[0] = new SqlParameter("@BankName", drBranches["Name of The Bank"].ToString());
                        SQLData.ExecuteNonQuery(Util.Constring, CommandType.Text, Query, oparam).ToString();
                    }
                    else BankId = obj.ToString();
                    //take bank id

                    //check if the branch already inserted in bank branches
                    Query = "select branchid from bankbranches where BranchName=@BranchName";
                    oparam = new SqlParameter[1];
                    oparam[0] = new SqlParameter("@BranchName", drBranches["Branch Name"].ToString());
                    obj = SQLData.ExecuteScalar(Util.Constring, CommandType.Text, Query, oparam);
                    if (obj == null)
                    //if not insert
                    {
                        string BranchId = "";
                        Query = "Select isnull(max(BRANCHID),0)+1 from BankBranches";
                        obj = SQLData.ExecuteScalar(Util.Constring, CommandType.Text, Query);
                        BranchId = obj.ToString();
                        Query = "INSERT INTO [BankBranches] " +
                            " ([BranchId],[BankId],[BranchName],[IFSC_Code],[MICR_Code],[Address],[ContactDetails],[Centre],[District],[State]) " +
                            " SELECT " +
                            " " + BranchId + " " +
                            " ," + BankId + "" +
                            " ,@BranchName" +
                            " ,@IFSCCODE" +
                            " ,@MICRCODE" +
                            " ,@ADDRESS" +
                            " ,@CONTACTDETAILS" +
                            " ,@CENTRE" +
                            " ,@DISTRICT" +
                            " ,@STATE";

                        oparam = new SqlParameter[8];
                        oparam[0] = new SqlParameter("@BranchName", drBranches["Branch Name"].ToString());
                        oparam[1] = new SqlParameter("@IFSCCODE", drBranches["IFSC CODE"].ToString());
                        oparam[2] = new SqlParameter("@MICRCODE", drBranches["MICR CODE"].ToString());
                        oparam[3] = new SqlParameter("@ADDRESS", drBranches["ADDRESS"].ToString());
                        oparam[4] = new SqlParameter("@CONTACTDETAILS", drBranches["CONTACT DETAILS"].ToString());
                        oparam[5] = new SqlParameter("@CENTRE", drBranches["CENTRE"].ToString());
                        oparam[6] = new SqlParameter("@DISTRICT", drBranches["DISTRICT"].ToString());
                        oparam[7] = new SqlParameter("@STATE", drBranches["STATE"].ToString());

                        SQLData.ExecuteNonQuery(Util.Constring, CommandType.Text, Query, oparam);
                    }
                    //else update
                }
                completed = true;
            }
            catch (Exception)
            {
                
                completed = false;
                throw;
            }
            return completed;
        }

Open in new window

0
 
SubbuUSAAuthor Commented:
Clarifying the previous post,

If first name and last name of the spreadsheet record matches two or more columns in the master table, then we need to skip and alert the user saying tow or more records found and treat them as a bad record.

If there are no matches found..then it again treated as a bad record.
0
 
Jini JoseSenior .Net DeveloperCommented:
so you need the full code for the above requirement ?
0
 
SubbuUSAAuthor Commented:
gmailJini..that was really funny. I dont want the full code but just to have an idea and approach of how I can achieve it. I have written the GUI and model objects for all the tables and operations. but this requirement is something I am not sure how to proceed.

anwyays thanks for reading my question
0
 
SubbuUSAAuthor Commented:
Hello gmailJini,

The above solution did not work out. Let me tell you how I have started and I will be more happy to take your help.

I did a raw import on a temp table in my sql server 2005. made every column as a nvarchar column. I uploaded all the data and they are sitting pretty in my table now. Just to avoid excel sheet manipulation because of less traction over it.

Now I want to take first name and last name from this table (TM_TempImportMembers) and check for validity in the employee table ,...if yes then I will mark it as a good record..not then it is a bad record..both good and bad will be shown in a separate grid view.

Any help would be appreciated.

Thanks and you have a great day!
Subbu


 protected void btn_upload1_OnClick(object sender, EventArgs e) {

             string FilePath =  System.IO.Path.GetFullPath(FileUpload1.PostedFile.FileName);
                    
                      
                    string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";" + "Extended Properties=Excel 8.0;";
                   
                    using (OleDbConnection connection = new OleDbConnection(xConnStr)) {

                            OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
                            connection.Open();

                            using (DbDataReader dr = command.ExecuteReader()) {
                                string conStr = ConfigurationManager.ConnectionStrings["stafftracker_conn"].ToString();
                                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conStr)) {

                                      // bulkCopy.DestinationTableName = "MT_XLDataImport";
                                            bulkCopy.DestinationTableName = "TM_TempImportMembers";
                                                                                                                     
                                        bulkCopy.WriteToServer(dr);
                                        Response.Write("Raw Import Successful ");
                                        LoadGrid();
                               
                                     }
                             }
                     }
    }

Open in new window

XL2SQLDataImport1.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.