Solved

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

Posted on 2010-09-01
5
346 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:SubbuUSA
  • 3
  • 2
5 Comments
 

Author Comment

by:SubbuUSA
ID: 33579989
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
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33580071
so you need the full code for the above requirement ?
0
 

Author Comment

by:SubbuUSA
ID: 33580109
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
 
LVL 10

Accepted Solution

by:
Jini Jose earned 500 total points
ID: 33580172
  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
 

Author Comment

by:SubbuUSA
ID: 33587164
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now