Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-09-01
5
Medium Priority
?
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 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