Connect to Excel spreadsheet using OleDbConnection , sort by column number

I am connecting to an Excel spreadsheet via OleDbConnection .

How do I order by ColumnNumber ? I'd like to do something like : SELECT * FROM [Sheet1$] ORDERBY ColumnNumber where ColumnNumber is a number like 1 or 2 ? Any ideas ? Note: the file I'm trying to open has no headers.

 private String BuildConnectionStringXLS()
        {
            String fileName = GetFileName();

            Dictionary<string, string> props = new Dictionary<string, string>();
            props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
            props["Data Source"] = fileName;
            props["Extended Properties"] 开发者_如何学Python= "\"Excel 8.0;HDR=No;IMEX=1\"";
            StringBuilder sb = new StringBuilder();
            foreach (KeyValuePair<string, string> prop in props) 
            {
                sb.Append(prop.Key); 
                sb.Append('=');
                sb.Append(prop.Value); 
                sb.Append(';'); 
            }
            return sb.ToString();
        }




    public DataTable GetFullTable(int columnToOrderBy)
        {
            String fileName = GetFileName();

            DataTable resultDataTable = new DataTable();
            String connectionString = BuildConnectionString();
            OleDbConnection conn = new OleDbConnection(connectionString);
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$] ORDERBY ColumnNumber", conn);
            da.Fill(resultDataTable);
            conn.Close();
            return resultDataTable;
        }

---

I find it surprising that you are calling [sheet1$] directly without referring to any linked server. Here are 2 approaches that are possible:

Using Linked Server

First create a linked server to access excel data as below:

(I am assuming you have already have either JET or ACE provider for excel)

  exec sp_addLinkedServer @server='ExcelLnkdServr', 
                    @srvproduct='ACE 12.0', 
                    @provider='Microsoft.ACE.OLEDB.12.0', 
                    @datasrc='\\sysdev\loadExcel.xlsx',
                    @provstr='Excel 12.0;HDR=Yes';
/*  If the above creation is successful, you can see a listing when use SP_LINKEDSERVERS in SSMS. Now you can query the sheet using order by as below: */
   select * from ExcelLnkdServr...[Sheet1$]                    
  order by 1 asc, 2 desc 

Using Adhoc Queries

You can OPENROWSET, OPENDATASOURCE or OPENQUERY as shown below: (Second option doesnt need any linked server creation )

 select * from openquery(ExcelLnkdServr, 'SELECT * FROM [Sheet1$] order by 1 asc, 3 desc') 

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=\\sysdev\loadExcel.xlsx;HDR=Yes','Select * from [Sheet1$] order by 1 asc')