Excel and SQL Server

Excel and SQL Server

• zybernau

  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

  NoSpam_Glider_Guider@Yahoo.com.NoSpam

  
  

• Weixiao Fan

  
  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

  
  
  

• enric vives

  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.

  
  
  

• Beth31

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

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

  
  
  

• steanson

  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

  
  

• Geo725

  Hi

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

  HOw any code example will help

  Cheers

  
  

• Wellnow

  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"
  

• RickGaribay.NET

  Glider Guider wrote:

  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 NoSpam_Glider_Guider@Yahoo.com.NoSpam

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

  
  
  

• -Raitz-

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

  string excelfilename = openFileDialog1.FileName;

  string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+excelfilename+@";Extended Properties=Excel 8.0;HDR=Yes;IMEX=1";

  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

  
  
  

• Newbie Kam

  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

  
  
  

• duck thing

  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

  
  
  
• Excel and SQL Server