1

I have the code to select the values from excel sheet. Currently the excel sheet contains 258 columns. I'm using OLDDBDatrareader to select the values from that excel.But the select query only selects the first 255 columns alone. The number of columns in the excel sheet will increase for each release. So how can i select more than 255 columns from excel sheet. I have used the below code for that, 

string Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=FALSE\"";

        OleDbConnection con = new OleDbConnection(Connection);
        OleDbCommand command = new OleDbCommand();
        DataSet ds = new DataSet();
        command.CommandText = sql;
        command.CommandType = CommandType.Text;
        command.Connection = con;
        command.Connection.Open();
        OleDbDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

And the select query is ,

sql.Append("SELECT * FROM [Sheet$]");

i tried the thing like to breakup the ranges and used like sql.Append("SELECT * FROM [Message$A:IU]") and sql.Append("SELECT * FROM [Message$IV:ConfigurationManager.AppSettings.Get("EndRegion")]"); and merged the result in single datatable. its working fine as expected. but number of columns in this sheet is dynamically updating for each release. So each we need to give the end range in that configuration. Instead of this solution is it possible to select all the rows select query without providing range? if possible can you pls provide how to use select query in EPPlus?

Please give some suggestions on this.

Thanks in advance.

user2960882
  • 57
  • 1
  • 8
  • Also: https://social.technet.microsoft.com/Forums/en-US/7b0935e7-741e-4ad9-b210-0131b8605402/how-to-import-excel-2007-worksheet-that-has-more-than-255-columns-into-sql-server-2008-table-using?forum=sqlintegrationservices – Soner Gönül May 07 '15 at 07:12
  • I tried the thing like to breakup the ranges and used like sql.Append("SELECT * FROM [Message$A:IU]") and sql.Append("SELECT * FROM [Message$IV:ConfigurationManager.AppSettings.Get("EndRegion")]"); and merged the result in single datatable. its working fine as expected. but number of columns in this sheet is dynamically updating for each release. So each we need to give the end range in that configuration. Instead of this solution is it possible to select all the rows select query without providing range? if possible can you pls provide how to use select query in EPPlus? – user2960882 May 07 '15 at 07:26
  • Please see this answer https://stackoverflow.com/a/50408587/2931264 – SadikAli May 18 '18 at 12:07

0 Answers0