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
Solved

Reading Data From Excel to SQL in C#

Posted on 2011-03-07
3
404 Views
Last Modified: 2013-12-17
Hi,

i am trying to read Data from an Excel sheet into a SQL Database.
The attached code works, except for the Selection string. It does not allow me to use the "Print Area" as a selection source.

Does anybody know how i can use the Print Area as a selection source?

Thanks
OleDbConnection DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + @"c:/temp/lpa.xls" + ";" 
            + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
        DBConnection.Open();

        string SQLString = @"Select * FROM [RR Door LH$.Print_Area]";
        OleDbCommand DBCommand = new OleDbCommand(SQLString, DBConnection);
        IDataReader DBReader = DBCommand.ExecuteReader();

        using (IDataReader DBReader = DBCommand.ExecuteReader())
        {
            string SQLCOnnectionString = @"Data Source=localhost\SQLEXPRESS; Initial Catalog=Test; Integrated Security=True";

            using (SqlBulkCopy bulkcopy = new SqlBulkCopy(SQLCOnnectionString))
            {
                bulkcopy.DestinationTableName = "ExcelData";
                bulkcopy.WriteToServer(DBReader);
            }
            DBReader.Close();
        }

        GridView1.DataSource = DBReader;
        GridView1.DataBind();

        DBReader.Close();
        DBConnection.Close();

Open in new window

0
Comment
Question by:ktpoitm
3 Comments
 
LVL 13

Expert Comment

by:agarwalrahul
ID: 35064497
0
 
LVL 1

Author Comment

by:ktpoitm
ID: 35070053
Thanks agarwalrahul,

i looked at the links you posted and it is not really addressing the issue.
Both links use the the following select statement:

"select * from [Sheet1$]", oconn"

This select statement will select everything in sheet one. I tried it, but it takes a long time for the query. I would like to just query the Print Area instead of the whole sheet.
0
 
LVL 3

Accepted Solution

by:
Sebastian_OH earned 500 total points
ID: 35138232
You can use the following select query:

"Select * FROM [Sheet1$Print_Area]"

That should do the trick
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

790 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