Help with a Oledb Connection String for Excel Files

Help with a OleDB connection string for excel files

Unfortunately, you can't set ImportMixedTypes or TypeGuessRows from the connection string since those settings are defined in the registry. For the ACE OleDb driver, they're stored at

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

in the registry. So, you can simplify your connection string to:

conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Nick\Desktop\Pricing2.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1;""";

Once you set TypeGuessRows to 0 and ImportMixedTypes to Text in the registry, you should get the behavior you are expecting. You might, however, consider using a suitably large number like 1000 instead of zero if you find import performance to be less than ideal.

oledb connection string for Excel 2016 in C#

This occurred for me after upgrading from a local install of Office 13 to Office 16 through the Office 365 program. I was getting this exception: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

I was not able to find a way to install the driver through the office 365 install process.

I had to install https://www.microsoft.com/en-us/download/details.aspx?id=13255 - the x64 version did not solve the issue, had to use the 32bit version.

My connection string in App.config

    <add key="Excel07ConnectionString" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

Code using it:

            var excelConnectionString = ConfigurationSettings.GetExcelConnection(fileLocation);
            var dataTable = new DataTable();

            using (var excelConnection = new OleDbConnection(excelConnectionString))
            {
                excelConnection.Open();
                var dataAdapter = new OleDbDataAdapter("SELECT * FROM [Users$]", excelConnection);
                dataAdapter.Fill(dataTable);
                excelConnection.Close();
            }
            Console.WriteLine("OpenExcelFile: File successfully opened:" + fileLocation);
            return dataTable;

How to prevent OLEDB create new excel file when path in connection string not exist

You can use File.Exists()

if (!File.Exists(@"D:\Item1.xlsx"))
  return;

How connect the uploaded excel file using OLEDB?

public bool CheckConnection(string fname)
        {
            string extension = Path.GetExtension(fname);
            try
            {
                string connstring = string.Empty;
                switch (extension)
                {
                    case ".xls":
                        connstring = string.Format(ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString, fname);
                        break;
                    case ".xlsx":
                        connstring = string.Format(ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString, fname);
                        break;
                }
                OleDbConnection connExcel = new OleDbConnection(connstring);
                OleDbCommand cmdExcel = new OleDbCommand();
                cmdExcel.Connection = connExcel;
                //bool canconnect = false;
                try
                {

                    connExcel.Open();
                    return true;
                    //DataTable dtExcelSchema;

                    //dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    // cmdExcel.ExecuteNonQuery();
                }
                catch 
                {
                    return false;
                }
                finally
                {
                    connExcel.Close();
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }

        }

just return true after connExcel.Open(); will get true if can connect else return false

---

Related Topics