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 sitehttp://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 Workbookstring excelConnectionString=@."Provider=Microsoft
 .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
 Properties=""Excel 8.0;HDR=YES;""";// Create Connection to Excel Workbookusing (OleDbConnection connection=
new OleDbConnection(excelConnectionString)){ OleDbCommand command=new OleDbCommand
 ("Select ID,Data FROM [Data$]", connection); connection.Open();// Create DbDataReader to Data Worksheetusing (DbDataReader dr= command.ExecuteReader()) {// SQL Server Connection Stringstring sqlConnectionString="Data Source=.;
 Initial Catalog=Test;Integrated Security=True";// Bulk Copy to SQL Serverusing (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 alsohttp://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 excelConnectionStringAsString ="Provider=Microsoft .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;"""

' Using

Dim connectionAs OleDbConnection =New OleDbConnection(excelConnectionString)

Try

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

connection.Open()

' Using

Dim drAs DbDataReader = command.ExecuteReader

Try

Dim sqlConnectionStringAsString = WebConfigurationManager.ConnectionStrings("CampaignEnterpriseConnectionString").ConnectionString

' Using

Dim bulkCopyAs SqlBulkCopy =New SqlBulkCopy(sqlConnectionString)

Try

bulkCopy.DestinationTableName =

"ExcelData"

bulkCopy.WriteToServer(dr)

Finally

CType(bulkCopy, IDisposable).Dispose()

EndTry

Finally

CType(dr, IDisposable).Dispose()

EndTry

Finally

CType(connection, IDisposable).Dispose()

EndTry

Catch exAs Exception

EndTry

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.

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.

|||

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.