Solved

excel import to sql data table

Posted on 2006-07-17
6
391 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

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…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
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…

831 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