Last post Jan 17, 2008 09:13 AM by sharma.sandeep82
Dec 13, 2007 07:59 AM|Birdbird|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
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);
// 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";
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;"""
Dim connection As OleDbConnection =
Dim command As OleDbCommand =
New OleDbCommand("Select ID,Data FROM [Data$]", connection)
Dim dr As DbDataReader = command.ExecuteReader
Dim sqlConnectionString As
String = WebConfigurationManager.ConnectionStrings("CampaignEnterpriseConnectionString").ConnectionString
Dim bulkCopy As SqlBulkCopy =
Catch ex As Exception
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.
Dec 18, 2007 04:09 AM|Nai-Dong Jin - MSFT|LINK
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.
Dec 18, 2007 05:18 PM|jpazgier|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.
Jan 16, 2008 01:14 PM|sharma.sandeep82|LINK
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;
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();
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
Jan 17, 2008 09:13 AM|sharma.sandeep82|LINK
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.
Thanks and Regards