Import Excel Spreadsheet Data Into SQL Server Database Table Using SqlBulkCopy

 

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.