Select columns with no specific name

Question

I am developing a C# application that consumes data from a spreadsheet and at some point I need a query string that will feed a variable.

For example:

strComando1 = "SELECT TOP 2 ColunaGenerica1, ColunaGenerica2 FROM ["+ planilha +"]";

I wanted to know if it is possible and how can I consume data from the first two columns, for example, without specifying their name, since the name of the columns may vary according to the user’s import.

Provider and connection:

    private static OleDbConnection conexao = new OleDbConnection();

    strConexao = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=0\"", caminhoArquivo);
    conexao.ConnectionString = strConexao;

    private static DataTable tabelaDeSaida;

Answer 1

You can use the command:

DataTable dtCols = conexao.GetSchema("Columns");

or

DataTable dtCols = conexao.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] {null, null, "NomeDaTabela", null});

He will return a DataTable, which has the following columns (among others):

TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION

From there, just take the column you want, from the table you want and mount the query.

Example:

conexao.Open();

DataTable dtCols = conexao.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, "tabela$", null });

DataRow[] rs = dtCols.Select("ORDINAL_POSITION <= 2");

string query = "Select ";
foreach (DataRow r in rs)
{
    query+= r["COLUMN_NAME"].ToString()+ ",";
}

query = query.Remove(query.Length-1);

query += " From tabela$ where 1=1;";

MessageBox.Show(query);

Upshot:

Select coluna1,coluna2 From tabela$ where 1=1;