none
Using JET OLEDB to create an Excel spreadsheet with column headers RRS feed

  • Question

  •  

    I am trying to use OLEDB to create an Excel spreadsheet without column headers.  I have tried the following connection string  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myexcel.xls;Extended Properties="Excel 8.0;HDR=No;".  However, I still get headers in the spreadsheet.  Does anyone know if this is possible?

     

    Thank you

    Thursday, February 14, 2008 9:20 PM

Answers

  • Do you read data and need to skip header row? If yes, then make sure that your Extended Properties part of the connection string has an additional pair of double quotes around them to make them work. Extended Properties is a string inside of connection string, not just a list of properties. Your connection string in this case should look like

     

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myexcel.xls;Extended Properties=""Excel 8.0;HDR=No;"";". 

    Friday, February 15, 2008 10:51 AM
    Moderator

All replies

  • Do you read data and need to skip header row? If yes, then make sure that your Extended Properties part of the connection string has an additional pair of double quotes around them to make them work. Extended Properties is a string inside of connection string, not just a list of properties. Your connection string in this case should look like

     

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myexcel.xls;Extended Properties=""Excel 8.0;HDR=No;"";". 

    Friday, February 15, 2008 10:51 AM
    Moderator
  • Val,

     

    Thank you for the response. However, when I use the following connection string to read the data; Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myexcel.xls;Extended Properties="Excel 12.0;HDR=No;IMEX=1;  the data from the header row is still read.  It would be nice to be able to create a spreadsheet without the header row.

    Friday, February 15, 2008 12:20 PM
  • That is correct, since you specified in your connection string that you do not have headers (HDR=No) and provider starts to read from the first row. Spreadsheets do not have header rows and it is just a logical definition for the provider. If you specify that you have headers it means provider should treat first rows of cells as header, but in reality it is exact same row of cells as any other row. What you could do is to create spreadsheet with the "header" and then change your connection string in a code to read starting next row after the header.

    Tuesday, February 19, 2008 10:33 AM
    Moderator
  • Val,

     

    Thanks again - you are correct. However, the program that produces these spreadsheets currently uses a legacy dll that produces Excel 2.1 spreadsheets without a header. These spreadsheets are then used as input into a web browser based application.  I have updated the program by developing a class that uses JET OLEDB to produce Excel 8.0 spreadsheets (which appears to only create spreadsheets with headers). I cannot insure that my users willl upgrade at the same time since they are geographically spread out. I worked around the problem by creating the Excel 8.0 spreadsheet using JET OLEDB (with the header row). Since JET OLEDB will not allow me to delete the header row from the spreadsheet; I then clear all the cells in the header row.  The website that uses these spreadsheets as input ignores blanks row.

    Tuesday, February 19, 2008 2:01 PM
  • So, does it work now as expected? Do you still need help?

     

    Wednesday, February 20, 2008 10:47 AM
    Moderator
  • Yes, the work around is acceptable. Thank you for your assistance.

    Wednesday, February 20, 2008 12:14 PM