Solved

Generate insert/update script from Excel sheet

Posted on 2013-11-26
3
539 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
[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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
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.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 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