Solved

Generate insert/update script from Excel sheet

Posted on 2013-11-26
3
529 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
Comment Utility
.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
Comment Utility
Thanks. It would be great if you share sample application.
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
Comment Utility
Thanks.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

14 Experts available now in Live!

Get 1:1 Help Now