How to create connection string to excel sheet 2007 by C# [Resolved]

Posted by Ahmedsa under C# on 2/28/2017 | Points: 10 | Views : 255 | Status : [Member] | Replies : 7
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





Responses

Posted by: A2H on: 2/28/2017 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
IMEX=0 or 1 or 2
“IMEX=1;” tells the driver to read mixed (numbers, dates, strings etc) data columns as text. using IMEX=1 is a safer way to retrieve data for mixed data columns.

You can find more details here : https://www.connectionstrings.com/ace-oledb-12-0/treating-data-as-text/

Thanks,
A2H
My Blog

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 2/28/2017 [Member] [MVP] Silver | Points: 25

Up
0
Down
HDR = YES OR NO
Using "HDR=Yes;" indicates that the first row contains columnnames, not data in Excel sheet.


Thanks,
A2H
My Blog

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ahmedsa on: 2/28/2017 [Member] Starter | Points: 25

Up
0
Down
if i using imex=1 this is best

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 2/28/2017 [Member] [MVP] Silver | Points: 25

Up
0
Down
Yes as I mentioned in earlier response , using IMEX=1 is a safer way to retrieve data for mixed data columns.

Thanks,
A2H
My Blog

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ahmedsa on: 2/28/2017 [Member] Starter | Points: 25

Up
0
Down
Thank you for reply
you say
using IMEX=1 is a safer way to retrieve data for mixed data columns.
but i also using insert and update not retrieve or select only
so that IMEX=1 for update and insert also
please i need answer for this if possible

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ahmedsa on: 2/28/2017 [Member] Starter | Points: 25

Up
0
Down
if i use update and insert in excel sheet also using imex=1
i need yes or no only

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 2/28/2017 [Member] [MVP] Silver | Points: 25

Up
0
Down
No

Thanks,
A2H
My Blog

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response