Reading an Excel File From C#

Question

I have a connection string to read an excel file from my C# project that looks like this..

String ConnectionString  = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                                      "Data Source=" + VariableFile + ";" +
                                      "Extended Properties=Excel 8.0;";

and I also have objConn.Open(); to open the file..

The problem is the only time my program will open the file is if I open the Excel file manually and run my program. Can anyone help me to open the file from my C# code instead of having to open it first manually. I get the error message: Could not find installable ISAM when I try to run it without opening the Excel file first.

Thank you

Answer 1

I think your connection string is formatted wrong and the "Could not find installable ISAM" is usually an indication of this.

Try this, it's from a piece of operational code I have:

Excel 2007

string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=No;IMEX=1\";", fullPath);

Excel 2003

string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";", fullPath);

Answer 2

I recently had to use this provider for an Azure Web Job where I needed to use an OLEDB Provider rather than the Excel.

You can install the Microsoft.ACE.OLEDB.12.0 Provider using the following setup.

Microsoft Access Database Engine 2010 Redistributable https://www.microsoft.com/en-us/download/details.aspx?id=13255

Once installed, you can modify the connection string for .xls and .xlsx file extensions.

For example, the following code will convert an Excel file to a DataSet with a DataTable for each Worksheet in the excel file.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Net;

...

public DataSet ExcelToDataSet(string excelFilename)
{
    var dataSet = new DataSet(excelFilename);

    // Setup Connection string based on which excel file format we are using
    var excelType = "Excel 8.0";
    if (excelFilename.Contains(".xlsx"))
    {
        excelType = "Excel 12.0 XML";
    }

    // <add key="Microsoft.ACE.OLEDB" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='{1};HDR=YES;READONLY=TRUE'"/>
    var connectionStringFormat = ConfigurationManager.AppSettings["Microsoft.ACE.OLEDB"].ToString();
    var excelNamePath = string.Format(@"{0}\{1}", Environment.CurrentDirectory, excelFilename);
    var connectionString = string.Format(connectionStringFormat, excelNamePath, excelType);

    // Create a connection to the excel file
    using (var oleDbConnection = new OleDbConnection(connectionString))
    {
        // Get the excel's sheet names
        oleDbConnection.Open();
        var schemaDataTable = (DataTable)oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        oleDbConnection.Close();
        var sheetsName = GetSheetsName(schemaDataTable);

        // For each sheet name 
        OleDbCommand selectCommand = null;
        for (var i = 0; i < sheetsName.Count; i++)
        {
            // Setup select command
            selectCommand = new OleDbCommand();
            selectCommand.CommandText = "SELECT * FROM [" + sheetsName[i] + "]";
            selectCommand.Connection = oleDbConnection;

            // Get the data from the sheet
            oleDbConnection.Open();
            using (var oleDbDataReader = selectCommand.ExecuteReader(CommandBehavior.CloseConnection))
            {
                // Convert data to DataTable
                var dataTable = new DataTable(sheetsName[i].Replace("$", "").Replace("'", ""));
                dataTable.Load(oleDbDataReader);

                // Add to Dataset
                dataSet.Tables.Add(dataTable);
            }
        }

        return dataSet;
    }
}

private List<string> GetSheetsName(DataTable schemaDataTable)
{
    var sheets = new List<string>();
    foreach(var dataRow in schemaDataTable.AsEnumerable())
    {
        sheets.Add(dataRow.ItemArray[2].ToString());
    }

    return sheets;
}