Solved

How can I automatically update a SQL table from Excel file?

Posted on 2010-08-18
7
357 Views
Last Modified: 2013-11-27
Hello Experts.

I have a database with two tables.  One of the tables was built using data from an Excel file (the tables are the same).  I would like an executable file that I can schedule Windows to run at a set time each night that will replace the existing data in the SQL table with the new data from the Excel file.  I don't know how to write an executable such as this, but was I'm hoping somebody else has already done something like this.

Here's what I have right now:
Excel file titled Website Contact List.xlsx
It's path is D:\DailyBatchJobs\Website Contact List.xlsx
The columns are, in order from left to right (these are the column headers also):  FIRST_NAME, LAST_NAME, EMAIL, JOB_TITLE, PHONE, DIVISION_CODE, DIVISION_NAME (I have attached the file.)

The database is emailDB.mdf
The path for the database is:  C:\inetpub\wwwroot\WebSites\TxDRA\App_Data\emailDB.mdf
I would like the script to replace the data in the table titled emailTbl.
The table has the following fields, in order from left to right:
id - this field is automatically incremented (primary key)
firstName
lastName
title
phoneNumber
emailAdd
divisionCode
divisionName

I'm working with SQL Express 2008.

Please let me know if I need to provide more information.

Thank you,
Jerald





Website-Contact-List.xlsx
0
Comment
Question by:ferguson_jerald
[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
7 Comments
 
LVL 10

Accepted Solution

by:
Jini Jose earned 500 total points
ID: 33465929
here is a small code for updating data from excel to sql database. may be u need to change the provider.

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
 
LVL 1

Expert Comment

by:BenHarpas
ID: 33465951
Code snippet to use inside a stored procedure with a job.


EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\mssql7\binn\filename.xls',
NULL,
'Excel 5.0'
GO

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\mssql7\binn\filename.xls";User ID=;Password=pwd;Extended properties=Excel 5.0')...sheetname$

You change this for your settings and with the use of cursors or temporary tables you could simply use it on a Job. All you have to do after is put the desired file into the directory.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33465964
You can do that ... The below link will help you achieve it.

Basically you will need to write  a script in SQL and Execute it using a batch file and also schedule the batch file .. before that you will need to choose the way to import the Excel file..Below link gives you different ways on how to import from Excel File and also how to run the script from batch file using SQL cmd

http://support.microsoft.com/kb/321686
http://www.sqlbook.com/SQL-Server/SQLCMD-command-line-utility-13.aspx
http://www.1keydata.com/sql/sqlupdate.html
http://www.1keydata.com/sql/sqlinsert.html
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 11

Expert Comment

by:ladarling
ID: 33466013
This would be pretty simple for an experienced programmer to do, but you will need it customized for your specific database. As for someone having it pre-made... that is unlikely, since the conection string to your database woudl need to be specified.
 
0
 
LVL 11

Expert Comment

by:ladarling
ID: 33466083
Ummm... hello....other experts... he specifically stated that he was using SQL Express, there is no job scheduling.... there is no server instance, read the question please.
0
 

Author Comment

by:ferguson_jerald
ID: 33469868
I can save the excel file saved as a tab delimited text file.  Would that be easier to deal with?  
0
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33470016
you can do the same way for  a text file, the code i posted earlier.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Trigger 8 37
query analyser in sql server 2016 express edition 2 25
Searching a barcode number within a string. 7 25
Web page design problem 3 14
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

749 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