Retrieving metadata from an excel file

Questions & Answers
1

I am using ODBC in a c# Unity script to access data in a .xsl file. The connection works and I can retrieve data from the file, but I am having trouble with its metadata. When I call the GetSchema(string) function, it gets stuck in a endless recursive call until it causes a stack overflow. This problem happens regardless of what particular schema I try to get.

Here is the code I am using:

string connectionString = "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; Dbq=" + file + ";UNICODESQL=1;Unicode=yes;";
    
OdbcConnection dbCon = null;
OdbcDataReader dbData = null;

try
{
  dbCon = new OdbcConnection(connectionString);
  Debug.Log(connectionString);
  
  dbCon.Open();

  DataTable sheets = dbCon.GetSchema(OdbcMetaDataCollectionNames.Tables);
  foreach(DataRow sheet in sheets.Rows)
  {
    string sheetName = sheet["TABLE_NAME"].ToString().Trim('\'').TrimEnd('$');    
    OdbcCommand dbCommand = new OdbcCommand("SELECT * FROM [" + sheetName + "$]", dbCon);
    DataTable data = new DataTable(sheetName);
    dbData = dbCommand.ExecuteReader();
    data.Load(dbData);
  }
}
catch (Exception ex)
{
  if (ex != null)
  {
    Debug.LogError(ex.Message);
    Debug.LogError(ex.StackTrace);
  }
  else
    Debug.LogError("Exception raise loading '" + file + "'");
}
finally
{
  if (dbData != null)
    dbData.Close();
  
  if (dbCon != null)
    dbCon.Close();
}

}

2

Copied from my answer at stackoverflow (link c# - Retrieving metadata from excel in Unity - Stack Overflow)

I ran into this problem myself. It has to do with monodevelop’s implementation of getschema(string str). It doesn’t do what one would expect; it calls one of it’s other implementations which then calls itself recursively (which is causing the stack overflow), casting exception if the connection has been closed, otherwhise calling itself. In other words it doesn’t ever return what it says it’s going to return, it calls itself until stackoverflow or the connection is closed (which probably won’t happen since the close-command is usually called afterwards).

public override DataTable GetSchema (string collectionName)
{
    return GetSchema (collectionName, null);
}
public override DataTable GetSchema (string collectionName, string [] restrictionValues)
{
    if (State == ConnectionState.Closed)
        throw ExceptionHelper.ConnectionClosed ();
    return GetSchema (collectionName, null);
}

^ From documentation: mono/mcs/class/System.Data/System.Data.Odbc/OdbcConnection.cs at mono-4.0.0-branch · mono/mono · GitHub

That is the reason for your error but I unfortunately don’t have any real solution at this time but my workaround might be of use to someone. I want to get some sheetnames in my Excel-file so what I have done is add all sheetNames I want to use in a specific sheet, “Unity”. I then read in that to get sheetnames then load them in one by one.

It’s a very error-prone solution since it counts on a manual list of the sheetnames and that this list is correct but I’m pressed on time and have no other known alternative, but maybe it can be of help to someone.