How to generate sql script by reading the data from an excel sheet?

Question

Currently i am working on c# .net and i need to generate scripts ( sql insert scripts ) by using the data that is present in an excel sheet.

To be more specific,

If the excel sheet has three columns with

**column name** ColumName1, ColumName2, ColumName3 
**data like**   Value1,     Value2,     Value3

i need to write code to generate the insert script like -

INSERT [dbo].[TableName] ([ColumName1], [ColumName2], [ColumName3]) VALUES ('Value1', 'Value2', 'Value3')

Any ideas ?

do you want to create script in the excel sheet or some where else? — शेखर
– शेखर, Commented Jan 12, 2013 at 5:15
@krshekhar I just need the scripts to be generated. I must be able to run the scripts in my database (sql server). So its not a problem where it can be generated. I just need the scripts. You can do it your own way. Thanks. — Ebenezar John Paul
– Ebenezar John Paul, Commented Jan 16, 2013 at 5:48

tam tam · Accepted Answer · 2013-01-11 16:00:57Z

1

You would need to write a forumla for the final column for instance:

="insert into tblyourtablename (intmine, strval) values ("&B4&", N'"&C4&"'); set @intpane = scope_identity(); INSERT INTO tblpane (nameid_fk,strtext,bitmine,vbarmine) VALUES (@intpane ,N'"&D8&"' ,0 ,convert(varbinary,''));"

This should get your started

EDIT

Why do some SQL strings have an 'N' prefix? You may have seen Transact-SQL code that passes strings around using an N prefix. This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT. See Article #2354 for a comparison of these data types.

For further reading please view the following link

http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html

edited Jan 11, 2013 at 16:00

answered Jan 11, 2013 at 7:09

tam tam

1,9002 gold badges26 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ebenezar John Paul Over a year ago

Thank you for the response. I need to know what does N' exactly mean and what is it's operation here ?

tam tam Over a year ago

Please see me edit for the explaination for having an N, I do not know your column datatypes but N is not necessary.

Ebenezar John Paul Over a year ago

Thanks for the edit. It has the relevant information. And also i found this article that is really giving a best piece of understanding about our N' discussion. support.microsoft.com/kb/239530 'N - Its for Unicode data and stands for National language character set as you said. But it is not a mandatory one and depends on our requirement.

शेखर · Accepted Answer · 2013-01-11 07:23:19Z

0

var myConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Language_Batch1_OneClick.xls';Extended Properties=Excel 8.0;"); ;
var myCommand = new OleDbCommand();
var upCommand = new OleDbCommand();
int i = 0;
try
{

    string sql = null;
    string Value1 =null;
    string Value2=null;
    string Value3=null;
    myConnection.Open();
    myCommand.Connection = myConnection;
    sql = "select ColumName1,ColumName1from,ColumName3  [sheet-name]";
    myCommand.CommandText = sql;
    var dataReader = myCommand.ExecuteReader();
    {
      value1=dataReader["ColumName1"].ToString();
      value2=dataReader["ColumName2"].ToString();
      value3=dataReader["ColumName3"].ToString();

    string newQuery="INSERT [dbo].[TableName] ([ColumName1], [ColumName2], [ColumName3]) VALUES ('"+Value1+"', '"+Value2+"', '"+Value3+"')"
    }

}

edited Jan 11, 2013 at 7:23

answered Jan 11, 2013 at 7:05

शेखर

17.6k14 gold badges64 silver badges123 bronze badges

2 Comments

Ebenezar John Paul Over a year ago

Thank you for responding and for the explanation.

शेखर Over a year ago

you are welcome and please marked the answer it it helped you to solved your problem.

chengdu.jack · Accepted Answer · 2014-02-07 06:41:29Z

0

Read excel and get data ,then generate insert script

answered Feb 7, 2014 at 6:41

chengdu.jack

291 silver badge12 bronze badges

Collectives™ on Stack Overflow

How to generate sql script by reading the data from an excel sheet?

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related