Excel and SQL Server  
Author Message
UNOTech





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top

How do I read an excel spreadsheet in vb.net and insert the records from the spreadsheet into a SQL database




.NET Development13  
 
 
DMan1





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top

You will need 2 connection objects one for sql and one for excel....

Imports System.Data.SqlClient
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString="my connectionstring"
oSQLConn.Open()

"Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False"


"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.


Now use a datareader to go through the excel dataset and add row by row to SQL


 
 
Struski





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top

I tried to open a Excel file using the code below:

string excelfilename = openFileDialog1.FileName;

string connString = +excelfilename+ ;

System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connString);

conn.Open();

System.Data.OleDb.OleDbCommand selectAllComand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [sheet1$]");

System.Data.OleDb.OleDbDataReader excelReader = selectAllComand.ExecuteReader();

conn.Close();

But I got this error at the conn.Open() line: "Couln not find installable ISAM"...

 

Anyone knows why



 
 
bhamby





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top

If you're going to use more than one Extended Properties, they need to be enclosed in quotation marks. HDR=YES is the default, so you really don't have to include that. You can probably get by without the IMEX=1 too. If you get it down to just the one - Excel 8.0, then it doesn't have to have the extra quotes around it. Otherwise it has to look like this: Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
 
 
Glider Guider





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top

Two questions: 1. Where would I find the documentation for HDR and IMEX etc

2. HDR=Yes isn't working for me. I can't get the first row from Excel with the column names. Any idea what might be wrong


 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top


The HDR argument will determine whether the first row in the Worksheet is used as column names in your DataSet/DataTable. If the value is YES then the column names will correspond to those found in the first row of the Excel Worksheet or Range. If the value is NO then the column names for your DataSet/DataTable will default to F1, F2, F3, etc. The column names are not returned as data if that is what you are expecting.

The IMEX argument determines the method used to import/export the file. You can find more info in the following MS KB article:

PRB: Excel Values Returned as NULL Using DAO OpenRecordset



 
 
Smokey I





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top

 

Two questions:  1. Where would I find the documentation for  HDR and IMEX etc

2. HDR=Yes isn't working for me.  I can't get the first row from Excel with the column names.  Any idea what might be wrong

 

Try HDR=NO if you need all the data including the column header



 
 
VMazur





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top

Information about extended properties for Jet connection string (like IMEX, HDR or other one) could be found here

http://msdn2.microsoft.com/en-gb/library/aa140022(office.10).aspx#adoproperties_extendedsettings

http://msdn2.microsoft.com/en-us/library/ms254500.aspx



 
 
pappascd





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top

The column names in my Excel file aren't all being read in correctly. The 1st row of the Excel sheet contains -

CARRIER MODEL 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016....all the way up to 2031

the "CARRIER" and "MODEL" names both get set correctly, but the subsequent columns all get assigned "F3, F4, F5", etc. Any way around this other than setting the cell types to text


 
 
VMazur





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top

I believe it happens because those cells are defined as numeric values, not as strings. In this case provider just skips them and assigns default names. Have you tried to use IMEX=1 in your connection string It forces Jet to treat all the values as strings, but I am not sure if it would work in a case of column names.



 
 
YOUR-Mark





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top

http://java.sun.com/products/jdk/1.3/docs/api/java/lang/String.html

http://www.ietf.org/rfc/rfc1521.txt



 
 
jamesjustin





PostPosted: .NET Framework Data Access and Storage, Excel and SQL Server Top

Hi

use a datareader to go through the excel dataset and add row by row to SQL

HOw any code example will help

Cheers