How to create connection string to excel sheet 2007 by C#

ahmed salah

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sourabh Somani9y ago
I will suggest you please read following you will clear about connection string

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

Accept my answer if you got your answer
Thanks
+3
Sourabh Somani9y ago
To always use IMEX=1 is a safer way to retrieve data for mixed data columns

If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.

Read More here
http://stackoverflow.com/questions/10102149/what-is-imex-in-the-oledb-connection-string
+1
Rafnas T P9y ago
thats not a problem.
0
Sourabh Somani9y ago
//connection string for that file which extantion is .xls
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

//connection string for that file which extantion is .xlsx
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

You can read my article link is given as below

Read Excel Sheet Data and Bind With ASP.NET GridView

http://www.c-sharpcorner.com/UploadFile/75a48f/read-excel-sheet-data-and-bind-with-asp-net-gridview/
+3
ahmed salah9y ago
I read it i only need to now
i using interger column and reamining colmun text
if i set imex=1 this will mset problem or not
0
Rafnas T P9y ago
you can refer this
http://www.c-sharpcorner.com/article/import-excel-data-into-sql-table-using-sqlbulkcopy-in-c-sharp/
+1