Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy RSS

• • Reply
  

  Birdbird

  Member

  2 Points

  3 Posts

  Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

  Dec 13, 2007 07:59 AM|LINK

   

  Hi, I'm a Student, and since a few months ago I'm learning JAVA. I'm creating an application to call and compare times. For this I create in Excel a time table which is quite big and it would be a lot of typing work to input one by one the data in each cell in SQL Server, considering that I have to create 8 more tables. I was able to retreive the data from excel usin the JXL API of JAVA but it doesn't give all the funtions to perform math operations as JDBC. That's why I need to move the tables from Excel to SQL.

  I found this site http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx which gives a code to do so, but I guess that some heathers are missing or maybe I don't know which compiler to use to run that code, I would like you help to identify which compiler use to run that code or if there is some vital piece of code missing.

  // Connection String to Excel Workbook
  string excelConnectionString = @"Provider=Microsoft
      .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
      Properties=""Excel 8.0;HDR=YES;""";
  
  // Create Connection to Excel Workbook
  using (OleDbConnection connection =
               new OleDbConnection(excelConnectionString))
  {
      OleDbCommand command = new OleDbCommand
              ("Select ID,Data FROM [Data$]", connection);
  
      connection.Open();
      
      // Create DbDataReader to Data Worksheet
      using (DbDataReader dr = command.ExecuteReader())
      {
          // 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);
          }
      }
  }

  On the other hand in this forum I that someelse use that link but implements a totally different code which I'm not able to compile also http://forums.asp.net/p/1110412/2057095.aspx#2057095. It seems this code works as I was able to read, but I do not know which language is used.

  Dim excelConnectionString As String = "Provider=Microsoft .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;"""

   

  ' Using

   

  Dim connection As OleDbConnection = New OleDbConnection(excelConnectionString)

   

  Try

   

  Dim command As OleDbCommand = New OleDbCommand("Select ID,Data FROM [Data$]", connection)

  connection.Open()

   

  ' Using

   

  Dim dr As DbDataReader = command.ExecuteReader

   

  Try

   

  Dim sqlConnectionString As String = WebConfigurationManager.ConnectionStrings("CampaignEnterpriseConnectionString").ConnectionString

   

  ' Using

   

  Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString)

   

  Try

  bulkCopy.DestinationTableName =

  "ExcelData"

  bulkCopy.WriteToServer(dr)

   

  Finally

   

  CType(bulkCopy, IDisposable).Dispose()

   

  End Try

   

  Finally

   

  CType(dr, IDisposable).Dispose()

   

  End Try

   

  Finally

   

  CType(connection, IDisposable).Dispose()

   

  End Try

   

  Catch ex As Exception

   

  End Try

  The Compilers I have are: Eclipse, Netbeans, MS Visual C++ Express Edition and MS Visual C# Express Edition. In MS Visual C++

  Thanks for your help.

   Regads,

  Robert.

   

  • Moved by jeff@zina.com on Dec 13, 2007 11:03 AM
• • Reply
  

  Nai-Dong Jin...

  All-Star

  41630 Points

  3558 Posts

  Re: Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

  Dec 18, 2007 04:09 AM|LINK

  Hi,

  From your description, it seems that you want to connect to your EXCEL table by OLDEB from your application, right?

  If so, you can build your connection string in the following way, and create the oledbcommand object to execute your query statement.

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ExcelFilePath;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
  string SQLQuery = "SELECT * FROM [sheet1$]".
  // excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

  For details, see:

  http://support.microsoft.com/kb/326548 (Excel Part)

  Besides, If your application runs in 64-bit mode, all of the components it uses must also be 64-bit. There is no 64-bit Jet OLE DB Provider, so you get the message described. You would receive a similar error when trying to connect to a database using OLE DB or ODBC if there is no 64-bit version of the specified OLE DB provider or ODBC driver.

  http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=123311

  Thanks.

   

  Please mark the replies as answers if they help or unmark if not.Feedback to usMicrosoft One Code Framework
  • Marked as answer by Nai-Dong Jin - MSFT on Dec 19, 2007 02:02 AM
• • Reply
  

  jpazgier

  Contributor

  3725 Points

  622 Posts

  Re: Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

  Dec 18, 2007 05:18 PM|LINK

  if you really would like be sure that your import is successfully, do not use OLE DB or any other tolls to export your data from excel. Just use excel to export data to any common format like DBase, ACCESS, flat file and next import it to place you want. It will save you a lot of problems because the best tool to read Excel data is Excel itself and do not use other tools to do it. do not use SQL server SSIS also, it use OLE DB and does not work correctly with some excel shits.

  Thanks
  
  JPazgier
  • Marked as answer by Nai-Dong Jin - MSFT on Dec 19, 2007 02:02 AM
• • Reply
  

  sharma.sande...

  Member

  14 Points

  7 Posts

  Re: Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

  Jan 16, 2008 01:14 PM|LINK

  Hi,

  I want to import the content of Excel sheet into the SQL Server Database table. Infact I was successfully import the data into the table but the problem is that in that I was used  Microsoft.Office.Interop.Excel.dll to import the data into the table and my code like this : -

   

  Application excelObj = new Application();
  Workbook book = excelObj.Workbooks.Open(path + fupReportFile.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

  Worksheet sheet = (Worksheet)book.Worksheets[1];

  Range myRange = (Range) sheet.Cells[RowCounter, 17];
                      while (myRange.Value2.ToString().Trim() == "Contact #")
                      {
                          #region " Retrieve Data From Excel Sheet"
                              Range HeaderNoRange = (Range)sheet.Cells[RowCounter - 1, 50];
                              if (HeaderNoRange.Value2 != null)
                                  GuestHeaderNo = HeaderNoRange.Value2.ToString().Trim();
                              Range ContactNoRange = (Range)sheet.Cells[RowCounter, 24];
                              if (ContactNoRange.Value2 != null)
                                  CurrentContactNo = int.Parse(ContactNoRange.Value2.ToString().Trim());
                              Range GuestNameRange = (Range)sheet.Cells[(RowCounter + 4), 2];
                              if (GuestNameRange.Value2 != null)
                                  GuestName = GuestNameRange.Value2.ToString().Trim();
                              Range GuestSourceRange = (Range)sheet.Cells[(RowCounter + 4), 35];
                              if (GuestSourceRange.Value2 != null)
                                  GuestSource = GuestSourceRange.Value2.ToString().Trim();
                              Range GuestAddress1Range = (Range)sheet.Cells[(RowCounter + 6), 2];
                              if (GuestAddress1Range.Value2 != null)
                                  GuestAddress1 = GuestAddress1Range.Value2.ToString().Trim();

                   #endregion

  } 

   

  But I dont want to use this Excel Driver to import the data into the table.

  Is there any other way to import the data of excel sheet into table.  

  Any help regarding this is really helpful for me.
  

  Thanks and Regards

  Sandeep 

   

   

• • Reply
  

  sharma.sande...

  Member

  14 Points

  7 Posts

  Re: Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

  Jan 17, 2008 09:13 AM|LINK

  Hi,

  I just want to know that : -

   
  How can i read a excel file without having excel installed on my computer ....?

  I want to read an excel file on the basis of row and column. 

   
  Reply ASAP

   

  Thanks and Regards