0

I am using the ACE OLEDB connection string to connect to an excel file. I've noticed my query (see example below) that returns the column schema takes longer to run when the worksheet has more rows of data on it.
For some of my larger worksheets (200k rows) it is taking around 10 seconds for the header schema to be returned. It there a way to speed this up or a better way to get the column headers?

    string connectionString = string.Empty;
    connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};
        Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""", path);

    OleDbConnection con = new OleDbConnection(connectionString);
    con.Open();

    DataTable dtSchema = new DataTable();

    System.Diagnostics.Debug.WriteLine("Start: " + DateTime.Now.ToLongTimeString());
    dtSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                    new Object[] { null, null, WorksheetName, null });
    System.Diagnostics.Debug.WriteLine("End: " + DateTime.Now.ToLongTimeString());


    con.Close();

UPDATE
I tried rewriting this - turning Headers off and manually reading only the first row. It still takes around 10 seconds to process on my larger files (small ones still come back very quickly). Is there anything else I can try that might be able to get the header(first row) values quicker?

string connectionString = string.Empty;
connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; 
    Extended Properties=""Excel 12.0 Xml;HDR=NO;""", path);

DataTable dtSchema = new DataTable();                        

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    OleDbCommand command = new OleDbCommand(String.Format("SELECT * FROM [{0}A1:II1]", WorksheetName),conn);

    OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
    dataAdapter.SelectCommand = command;

    DataSet dataSet = new DataSet();
    dataAdapter.Fill(dataSet);

    dtSchema = dataSet.Tables[0];
}
wham12
  • 295
  • 5
  • 21
  • how long do you expect it to return.. do you know what the total and or max size in regards to rows that a single excel sheet can handle.. it's like 67K I thought.. so 200K is large.. also `you only need to write this Debug.WriteLine` you do not have to fully qualify it.. there are better ways to load the excel file into a `DataTable` as well I will post an example that I have just tried.. – MethodMan Nov 25 '15 at 19:59
  • look at these .. this is what I have used to just try some of my own Excel http://stackoverflow.com/questions/18006318/how-to-import-all-the-excel-sheets-to-dataset-in-c-sharp – MethodMan Nov 25 '15 at 20:05
  • I'm not trying to load the whole file, just the schema (headers). I would expect those to come back almost immediately. (Btw, the 65k limit is just for 97-2003 versions, 2007+ can handle just over 1M rows.) – wham12 Nov 26 '15 at 02:59
  • if you are trying to just load the headers.. then you are obviously doing something wrong.. look at this link http://stackoverflow.com/questions/3924745/use-getoledbschematable-to-get-the-columns-of-a-table-named-street also you can also do a google search to find other example by typing in the following in the search engine `C# GetOleDBSchemaTable` – MethodMan Nov 27 '15 at 20:18
  • That is exactly what I'm doing in my code sample above, and it takes longer than I would expect it to for the `con.GetOleDbSchemaTable` to return the columns DataTable. – wham12 Nov 27 '15 at 20:31
  • it's not exactly what you're doing.. for starters..you don't need this part in regards to this `DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb"); DbDataAdapter adapter = factory.CreateDataAdapter();` totally not necessary `DbProviderFactory` – MethodMan Nov 27 '15 at 20:33

0 Answers0