Read from Excel with OLEDB

lape
lape Member Posts: 19
edited 2014-05-22 in NAV Three Tier
Is it possible to Create an OLEDB Connection to an Excel Sheet and Read the Tables from the SchemaTable via DotNet Datatype?
I tryed the following:
ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\test.xls;Extended Properties="Excel 8.0;HDR=YES";';
OLEConnect := OLEConnect.OleDbConnection(ConnectionString);
OLEConnect.Open();
...
in C# i can do it like this: 
DataTable sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 	new object[] {null, null, null, "TABLE"});
but in Dynamics the method table not avalible
... 
OLEConnect.Close();

Comments

  • David_Singleton
    David_Singleton Member Posts: 5,479
    HI Lape,

    that's a pretty cool idea. I don't know how to solve it, but I hope you solve it, as I would like to try this also. I can see quite a few uses for this. Especially for automation projects where Excel is not installed.

    Also a question. Do you think this would be faster than using excel automations? I have a lot of cases where clients have huge spread sheets that take quite some time to import or export.
    David Singleton
  • toenne
    toenne Member Posts: 38
    Hi,
    you can alternatively use following code to retrieve the sheet/tablenames:
    lSheetName := 'Tabelle1$';
lSchemaTable := lOleDbConnection.GetSchema('Tables');
IF lSchemaTable.Rows.Count > 0 THEN BEGIN
  lDataRow := lSchemaTable.Rows.Item(0);
  lSheetName := lDataRow.Item(2);
END;
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    You mean like this?

    http://vjeko.com/blog/excel-interoperab ... n-nav-2013
    Marije Brummel | Freelance Business Central Specialist
  • toenne
    toenne Member Posts: 38
    hi,

    this was meant as alternative for the missing to do something like 
    DataTable sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,    new object[] {null, null, null, "TABLE"});
    in c/al, if you don't know the names of the included worksheets.
    the example retrieves the name of the 1st sheet.

Categories