Solved

excel import to sql data table

Posted on 2006-07-17
6
390 Views
Last Modified: 2008-01-16
Hi experts,

I trying to import data from excel in to sql database,

I got this code from one of the experts which should retrieve data from excel

but I do no see where it fills a database table.




how can i call the code and have it fill a database table?

I have tried using sqlbulkcopy, but its only available for 2.0?

===========================pageload============

==============================================================




 public DataSet LoadExcel(string path, string sheet, bool headers)
          {

               // Is the first row headers?
               string headerFlag = (headers ? "Yes" : "No");
     
               // Build a connection string
               string connectString =
                    @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + path + ";" +
                    "Extended Properties=\"Excel 8.0;HDR=" + headerFlag + ";\";";

               
               // Make sure the name ends with '$', which is an Excel
               // convention (i.e. Sheet1$).
               if (!sheet.EndsWith("$"))
                    sheet += "$";

         
               // Build an SQL Select statement:
               //   Example:  Select * From [Sheet1$]
               string commandText = "Select * From [" + sheet + "]";

        // SQL Server Connection String
        string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

        // Bulk Copy to SQL Server
               
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
        {
            bulkCopy.DestinationTableName = "ExcelData";
            bulkCopy.WriteToServer(dr);
        }


               // Get the data from the specified worksheet.
               OleDbDataAdapter da = new OleDbDataAdapter(commandText, connectString);
               DataSet ds = new DataSet();
               da.Fill(ds);

               da.Dispose();

               return ds;
          }



can u help

thanks
0
Comment
Question by:SirReadAlot
  • 4
  • 2
6 Comments
 

Author Comment

by:SirReadAlot
ID: 17120542
what can i use in place of
 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
        {
            bulkCopy.DestinationTableName = "ExcelData";
            bulkCopy.WriteToServer(dr);
        }
0
 
LVL 5

Expert Comment

by:nickhoggard
ID: 17120662
Hi,

What version of .NET are you running?

Just a re-write on the fly of the above (moved the SQLBulkCopy down to after the data set is loaded, and changed it from paramter dr to ds.Tables[0]).  This will require .NET 2.0.

Please note I have not tested this code, I just tweaked what didn't look quite right from above.

Cheers

Nick


public DataSet LoadExcel(string path, string sheet, bool headers)
          {

               // Is the first row headers?
               string headerFlag = (headers ? "Yes" : "No");
     
               // Build a connection string
               string connectString =
                    @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + path + ";" +
                    "Extended Properties=\"Excel 8.0;HDR=" + headerFlag + ";\";";

               
               // Make sure the name ends with '$', which is an Excel
               // convention (i.e. Sheet1$).
               if (!sheet.EndsWith("$"))
                    sheet += "$";

         
               // Build an SQL Select statement:
               //   Example:  Select * From [Sheet1$]
               string commandText = "Select * From [" + sheet + "]";



               // Get the data from the specified worksheet.
               OleDbDataAdapter da = new OleDbDataAdapter(commandText, connectString);
               DataSet ds = new DataSet();
               da.Fill(ds);

               da.Dispose();

        // SQL Server Connection String
        string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

        // Bulk Copy to SQL Server
               
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
        {
            bulkCopy.DestinationTableName = "ExcelData";
            bulkCopy.WriteToServer(ds.Tables[0]);
        }

               return ds;
          }
0
 

Author Comment

by:SirReadAlot
ID: 17120669
will try this nick
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:SirReadAlot
ID: 17120676
yeah,

I am running on 1.1,
0
 

Author Comment

by:SirReadAlot
ID: 17120687
basically what can i use in place of

bulcopy

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
        {
            bulkCopy.DestinationTableName = "ExcelData";
            bulkCopy.WriteToServer(ds.Tables[0]);
        }

               return ds;
          }
0
 
LVL 5

Accepted Solution

by:
nickhoggard earned 500 total points
ID: 17124277
Sorry, I'm not sure that there is an option on version 1.1 for bulk copy.

When I was last working on version 1.1 we used Sql Server DTS packages to do the import, and triggered it from a .NET call.  That is reliant on you being able to set up the DTS package in advance though.

Cheers

Nick
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
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.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

862 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

25 Experts available now in Live!

Get 1:1 Help Now