-1

Assume I have an Excel document with a single sheet in it. Where there is data in columns A-G, I can select all of them simply by using "SELECT * FROM [" + sheetname + "]". However, what if I want to specify columns? If there only happens to be a small number of columns, I would do the same as I would in SQL: SELECT x, y, z FROM.... If I want, however, to select a large number of specific columns, is there a way to do this in bulk? Additionally, if the first specific column is the first column in the sheet, is it possible to select the first x number of columns simply?

I'm looking to significantly shorten the T-SQL string here.

Wolfish
  • 960
  • 2
  • 8
  • 34

1 Answers1

0

you can get the specific list of columns in this manner:

cmd = new OleDbCommand("SELECT * FROM [" + sheetname + "$A1:G10000]", conn);

This will work if your range of columns is continuous. It doesn't have to start from A1, it can be any valid column. G10000 means to select 10K rows for columns A-G. You can change that number depending on the estimated size of you sheet.

If your range of columns is not continuous, then you need to specify the list of columns in the SELECT clause, eg.: SELECT A1, C1 FROM... . Try both A and A1 because as far as I remember someone reported that simply specifying A without a row number didn't work for them but I'm not 100% sure about this, needs to be confirmed.

This answer has some additional info.

HTH

Community
  • 1
  • 1
andrews
  • 2,173
  • 2
  • 16
  • 29