5 Replies - 1467 Views - Last Post: 18 September 2012 - 01:40 AM Rate Topic: -----

#1 gunapriyan  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 16-September 12

Transfer EXCEL data to SQL (not SQLBULK) using C#

Posted 16 September 2012 - 11:22 AM

Friends,

I need to insert the values from Excel to SQL using C#.

But in Excel, all the values are dynamic and in various cells.

Clients (More than 1000 Clients) will fill the excel (Already format created) and they need to upload.

Please find the attachment.

The data (highlighted) must transfer to SQL.

I had tried many ways. But still problem.

Please help me.

Thanks in advance.

Regards, Guna

Attached image(s)

  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: Transfer EXCEL data to SQL (not SQLBULK) using C#

#2 Momerath  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 996
  • View blog
  • Posts: 2,392
  • Joined: 04-October 09

Re: Transfer EXCEL data to SQL (not SQLBULK) using C#

Posted 16 September 2012 - 11:28 AM

This is not a code writing service. Post what you have done and we can help fix errors or point you in the right direction.
Was This Post Helpful? 0
  • +
  • -

#3 gunapriyan  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 16-September 12

Re: Transfer EXCEL data to SQL (not SQLBULK) using C#

Posted 16 September 2012 - 11:32 AM

Sorry, Please find the code below.

using System.Data.OleDb;

namespace TestInsertSpecifiedCells
{
    class Program
    {
        static void Main(string[] args)
        {                      
            string sql = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\00-Excel\\TestData.xlsx;Extended Properties='Excel 12.0 Xml;HDR=No';";
            OleDbConnection Con = new System.Data.OleDb.OleDbConnection(sql);
            OleDbCommand cmd = new OleDbCommand();
            Con.Open();
            cmd.Connection = Con;
            cmd.CommandText = "Update [Sheet1$A1:B1] Set F2='8545'";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "Update [Sheet1$A5:G5] Set F1='18000',F2='5',F3='7800',F4='0',F5='0',F6='18000',F7='18000'";
            cmd.ExecuteNonQuery();
            Con.Close();
        }
    }
}


--------------

One of my friend help to write above code. But i don't know F2,F1,F3,F4,F5....values...Because its depending upon clients.

Please help me to fix them for dynamic values.

Regards,

Guna

MOD EDIT: Added code tags. When posting code...USE CODE TAGS!!!

:code:

This post has been edited by JackOfAllTrades: 16 September 2012 - 12:55 PM

Was This Post Helpful? 0
  • +
  • -

#4 dhazard  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 12
  • View blog
  • Posts: 67
  • Joined: 06-September 06

Re: Transfer EXCEL data to SQL (not SQLBULK) using C#

Posted 17 September 2012 - 01:34 AM

Why not use a DataGridView and rollout with a single solution? Why Does it have to be excel per se?

This post has been edited by Curtis Rutland: 17 September 2012 - 08:24 AM
Reason for edit:: don't quote the previous post

Was This Post Helpful? 0
  • +
  • -

#5 gunapriyan  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 16-September 12

Re: Transfer EXCEL data to SQL (not SQLBULK) using C#

Posted 17 September 2012 - 10:19 AM

As per the suggestion by clients, they need to work in Offline mode.

Because some time they had faced problem while operating through online.

That is the main reason. Thats why they asked to work with Excel.

After completing, they can upload that Excel file....the data will transfer to my server.

Regards,

Guna
Was This Post Helpful? 0
  • +
  • -

#6 Sunny.W  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 6
  • Joined: 31-August 12

Re: Transfer EXCEL data to SQL (not SQLBULK) using C#

Posted 18 September 2012 - 01:40 AM

Hello,

I think that you can save data from Excel to DataTable and to SQL.

There is code for exporting data from Excel to DataTable

	    workbook.LoadFromFile(ExcelPath);
	            //Initialize worksheet
	            Worksheet sheet = workbook.Worksheets[0];
                    DataTable dataTable = sheet.ExportDataTable();
                    this.dataGridView1.DataSource = dataTable;


.NET Excel component is used.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1