?
Solved

excel import to sql data table

Posted on 2006-07-17
6
Medium Priority
?
396 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
[X]
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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

752 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