Solved

Generate insert/update script from Excel sheet

Posted on 2013-11-26
3
531 Views
Last Modified: 2013-11-27
Hi Experts,

   Please do refer the attache excel sheets which contain two sample tables.  In my database I've two respective tables as given below.
CREATE TABLE CONFIGURATION
(
Name VARCHAR(100) NOT NULL,
NType  VARCHAR(100) NOT NULL,
Data	VARCHAR(100) NOT NULL,
Datatype VARCHAR(100) NOT NULL,
DataVal VARCHAR(100) NOT NULL,
Remarks VARCHAR(100)  NULL) 

GO
ALTER TABLE CONFIGURATION
ADD CONSTRAINT PK_NTD PRIMARY KEY CLUSTERED (Name, NType, Data);


GO

CREATE TABLE Settings
(
ID INT NOT NULL,
NKey VARCHAR(100) NOT NULL,
Value VARCHAR(100) NOT NULL,
Remarks VARCHAR(100)  NULL
)
GO
ALTER TABLE Settings
ADD CONSTRAINT PK_IDNkey PRIMARY KEY CLUSTERED (ID, NKey);
GO

Open in new window


I would like to read the excel sheet and generate sql script for each table based on the table's PK.

Say for example, for the  configuration table the expected result looks like given below.


IF EXISTS (SELECT 1 FROM CONFIGURATION WHERE Name= 'Auditing'  AND NType = 'Audit'  AND  Data= 'AuditDt')
BEGIN
 UPDATE CONFIGURATION
 SET 
 Datatype = 'D',	DataVal	= '11/26/2013', Remarks  = 'Audit Date'
 WHERE  Name= 'Auditing'  AND NType = 'Audit'  AND  Data= 'AuditDt'

END
ELSE
BEGIN
 INSERT CONFIGURATION ([Name],[NType],[Data],[Datatype],[DataVal],[Remarks])
 VALUES ('Auditing','Audit'	,'AuditDt','D','11/26/2013','Audit Date')

END

Open in new window



Please guide me how to achieve this? It would be great if you share the coding or existing tool?

Thanks in advance.
SampleExcel.xlsx
0
Comment
Question by:Easwaran Paramasivam
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
unknown_routine earned 500 total points
ID: 39677492
.NET(C#) lets you read Excel easily:

string MyCon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;Extended Properties='Excel 8.0;HDR=Yes;'"
using(OleDbConnection connection = new OleDbConnection(MyCon ))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Configuration$]", connection)
    using(OleDbDataReader dr = command.ExecuteReader())
    {
         while(dr.Read())
         {
            'rest of your code....
            ...

         }
    }
}
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39679936
Thanks. It would be great if you share sample application.
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39682581
Thanks.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

23 Experts available now in Live!

Get 1:1 Help Now