Solved

excel import to sql data table

Posted on 2006-07-17
6
392 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Wav problem 4 28
What namespace do I need to import? 2 38
Calculate number of nights between two dates 5 53
Adding items to a C# list incrementally 5 29
Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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