what is the benfits of IMEX in connection to excel file 2007 ?

Article
2017-02-27

Question

_{Monday, February 27, 2017 11:49 AM}

I work on windows form application c#

i write connection as below

  string connection = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=D:\\Attendance Sheet.xlsx;Mode=ReadWrite;Extended Properties=Excel 12.0 Xml;");

connection above is work without any problem for select and read data and update data

what is benfit IMEX and how i write in connection above ?

and are connection above is best for performance ?

All replies (8)

_{Monday, February 27, 2017 1:43 PM}

Here are the possibilities for IMEX

One option for building the connection string

Connections Connection = new Connections();
// load Sheet1$ into temp DataTable
using (OleDbConnection cn = new OleDbConnection() { ConnectionString = Connection.NoHeaderConnectionString(FileName) })
{
    using (OleDbCommand cmd = new OleDbCommand() { Connection = cn })
    {
        cmd.CommandText = "SELECT * FROM [Sheet1$]";
        cn.Open();
        dtWorkSpace.Load(cmd.ExecuteReader());
    }
}

How the connection is setup

using System.Data.OleDb;
using System.IO;

namespace OleDbOperations 
{
    public class Connections
    {
        public Connections() { }

        /// <summary> 
        /// Create a connection where first row contains column names 
        /// </summary> 
        /// <param name="FileName"></param> 
        /// <param name="IMEX"></param> 
        /// <returns></returns> 
        /// <remarks></remarks> 
        [System.Diagnostics.DebuggerStepThrough()]
        public string HeaderConnectionString(string FileName, int IMEX = 1)
        {
            OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder();
            if (Path.GetExtension(FileName).ToUpper() == ".XLS")
            {
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                Builder.Add("Extended Properties", string.Format("Excel 8.0;IMEX={0};HDR=Yes;", IMEX));
            }
            else
            {
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                Builder.Add("Extended Properties", string.Format("Excel 12.0;IMEX={0};HDR=Yes;", IMEX));
            }

            Builder.DataSource = FileName;

            return Builder.ToString();
        }

        /// <summary> 
        /// Create a connection where first row contains data 
        /// </summary> 
        /// <param name="FileName"></param> 
        /// <param name="IMEX"></param> 
        /// <returns></returns> 
        /// <remarks></remarks> 
        [System.Diagnostics.DebuggerStepThrough()]
        public string NoHeaderConnectionString(string FileName, int IMEX = 1)
        {
            OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder();
            if (Path.GetExtension(FileName).ToUpper() == ".XLS")
            {
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                Builder.Add("Extended Properties", string.Format("Excel 8.0;IMEX={0};HDR=No;", IMEX));
            }
            else
            {
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                Builder.Add("Extended Properties", string.Format("Excel 12.0;IMEX={0};HDR=No;", IMEX));
            }

            Builder.DataSource = FileName;

            return Builder.ToString();

        }
    }

}

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Monday, February 27, 2017 3:49 PM}

Thank you for reply

what i understand is

0 is Export mode for ReadOnly
 1 is Import mode for write
 2 is Linked mode (full update capabilities)for read and update

i write what understand above

if i need to add full capabilities(read and write and modify) to my connection add IMEX=2

or what

can you please answer for me to this question

_{Monday, February 27, 2017 6:42 PM}

Thank you for reply

what i understand is
0 is Export mode for ReadOnly
 1 is Import mode for write
 2 is Linked mode (full update capabilities)for read and update
i write what understand above

if i need to add full capabilities(read and write and modify) to my connection add IMEX=2

or what

can you please answer for me to this question

Take a look at the following. It really depends on the data you are working with.

https://www.connectionstrings.com/excel/

_{Tuesday, February 28, 2017 10:03 AM}

I need to know are this connection string to excel is wrong or true

public static  string  fileName = string.Format("{0}\\Book502", Directory.GetCurrentDirectory());

string connection = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};Mode=ReadWrite;Extended Properties='Excel 12.0 Xml;HDR=YES'", fileName);

my file book 502
have two sheets

sheet 1

i insert data and update and read data from it

it have following column

ID INTEGER

User Name char(255)

Country char(255)

sheet 2 have following columns

ID INTEGER

Member Name char(255)

meaning all two sheets have text except ID

ALSO file have column in header

processes of two sheets insert update read select

size of transaction i will use 5000 rows

3 USER will use excel sheet

so that what correct format to excel file ?

the format i write working good ?
but really i need what i assign IMEX=0 or 1 or 2
HDR = YES OR NO

_{Tuesday, February 28, 2017 1:51 PM}

The IMEX argument indicates whether the ImportMixedTypes Registry entry is implemented. Typically you would not use it unless you have columns with mixed data (text and numeric) and you want to import everything as text. It can also cause Memo fields (columns with more than 255 characters of text) to be truncated. Here is some more information about the Registry entry settings for the Excel ISAM driver:

https://msdn.microsoft.com/en-us/library/office/ff844939.aspx

I would *not* recommend using IMEX unless you have columns with mixed data types.

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Tuesday, February 28, 2017 2:09 PM}

Your connection string looks fine. You don't really need the Mode argument, read/write is the default. You probably don't need IMEX but I would check your data to make sure it is being returned correctly.

Only use HDR=YES if the first row of your Worksheet contains column names.

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Wednesday, March 1, 2017 8:45 AM}

Thank you for reply

my data as below

UserID UserName          UserNameAR     Country
1001    hamada abo elalla    حماده ابو العلا             Emarate
1002    hamdy abed allah     حمدى عبد الله           Bahreen
1003    hamada elmasry       حماده المصرى            Tunisi

UserID IS INTEGER AND INCREMENT EVERY

time added by 1 and starting from 1001 to 5000

country and username is English

and UserNameAR field have Arabic data

as text and not have number

number only found in field

UserID

_{Monday, February 18, 2019 4:59 PM}

Dear Paul,

i have a problem.

i import Data vom an excelfile via ado.

i put imex=1, because i have mixed data.

if the first cell in a column is a number, and the next eight cells are null (empty) and the ninth cell is a text, it will not take the text data.

what can i do?

Oliver

Share via

what is the benfits of IMEX in connection to excel file 2007 ?

Question

All replies (8)

Additional resources