locked
Column Names in Excel RRS feed

  • Question

  • User404418099 posted

    Hi,

    I am using MS Excel as my data source. While retrieving data from excel I get column names as F1, F2, F3... rather than A, B, C... (the column names orginally given by excel. e.g. A, B.... ,AA, AB...)

    Please help me.

    Regards,

    Priyank

    Wednesday, December 7, 2011 3:01 AM

Answers

  • User1648350404 posted

    In your excel connection string  set HDR = YES then first row is taken as column name.

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 7, 2011 3:14 AM
  • User-821857111 posted

    Excel generated column names are only available if you launch an Excel application. If you use Jet or ACE to connect to an Excel file as if it is a database, you can specify that the first row contains headers by adding HDR=yes to the extended properties section of the connection string. If the first row doesn't contain headers, the column names default to F1, F2, F3 etc.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 7, 2011 3:16 AM

All replies

  • User1648350404 posted

    In your excel connection string  set HDR = YES then first row is taken as column name.

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 7, 2011 3:14 AM
  • User-821857111 posted

    Excel generated column names are only available if you launch an Excel application. If you use Jet or ACE to connect to an Excel file as if it is a database, you can specify that the first row contains headers by adding HDR=yes to the extended properties section of the connection string. If the first row doesn't contain headers, the column names default to F1, F2, F3 etc.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 7, 2011 3:16 AM
  • User1074791864 posted

    Hi friend,

    Did you try with HDR='Yes' in connection string?

    Thanks.

    Wednesday, December 7, 2011 3:20 AM
  • User404418099 posted

    Hi Mike,

    Thanks a lot for the insight. :)

    -Priyank

    Excel generated column names are only available if you launch an Excel application. If you use Jet or ACE to connect to an Excel file as if it is a database, you can specify that the first row contains headers by adding HDR=yes to the extended properties section of the connection string. If the first row doesn't contain headers, the column names default to F1, F2, F3 etc.

    Wednesday, December 7, 2011 4:08 AM