Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

excel import to sql data table

Posted on 2006-07-17
6
Medium Priority
?
397 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

618 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