sql command for reading all columns in the first row

Question

Dear All

I am an SQL newbie,

I am having an excel sheet in which the first row contains title for all the columns. I want to all the names in the first row.

Question

What is the SQL command for - reading all the entries in the first row? If possible, I want to define the max-limit.

Addition: What I want is, "Enumerate all the column names"

Thank you

Answer 1

You need to mention "HDR=No;" in your connection string. Then you can use the following query:

Select Top 1 * from [SheetName$]

"HDR=No" will specify that the first row DOES NOT contains the column names.

Being said that, I don't know if there is a SQL statement to get the column names from a table. If there is, then you can have HDR=Yes in the connection string and use that SQL statement.

Answer 2

Select Top 1 * into #temp from [SheetName$]

use tempdb

sp_help #temp.

by this you can get the column names of the #temp table.

again you have to change the DB to

use yourDBName

Put the data in a temp table and read the properties of the temp table and from that you can get the list of column names.

Answer 3

I hope I'm understanding this right....but I think you're saying that you want to select the column names from a table:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = [YourTableName]

Answer 4

This works for me in Excel using a saved workbook, and it enumerates the column (field) names.

Sub ListFieldADO()

    strFile = Workbooks(1).FullName
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"


    Set cn = CreateObject("ADODB.Connection")

    cn.Open strCon

    ''You can also use the name of a named range
    Set rs = cn.OpenSchema(adSchemaColumns, _
        Array(Empty, Empty, "Sheet1$")) 
    While Not rs.EOF
        Debug.Print "     " & rs!Column_Name
        rs.MoveNext
    Wend

    rs.Close
    Set rs = Nothing
End Sub