Reading Excel-file using Oledb - treating content of excel file as Text only

Question

I am using C# and OleDb to read data from an excel 2007 file.

Connection string I am using is:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Following is the code to read excel:

private OleDbConnection con = null;
private OleDbCommand cmd = null;
private OleDbDataReader dr = null;
private OleDbDataAdapter adap = null;
private DataTable dt = null;
private DataSet ds = null;
private string query;
private string conStr;

public MainWindow()
{
    this.InitializeComponent();
    this.query = "SELECT * FROM [Sheet1$]";
    this.conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\301591\\Desktop\\Fame.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";
}

private void btnImport_Click(object sender, RoutedEventArgs e)
{
    this.ImportingDataSetWay();
}

private void ImportingDataSetWay()
{
    con = new OleDbConnection(conStr);
    cmd = new OleDbCommand(query, con);
    adap = new OleDbDataAdapter(cmd);
    ds = new DataSet();
    adap.Fill(ds);
    this.grImport.ItemsSource = ds.Tables[0].DefaultView;
}

Here grImport is my WPF Data-Grid and I am using auto-generated columns.

How can I make sure the content stored in Excel will always be read as a string. I am not allowed to modify any of the registry values to achieve this. Is there any better way to read excel. Please guide me. If you need any other information do let me know.

Regards, Priyank

Averi

Is your excel file has header row? – Inutile

Answer 1

Could you try oledb provider connection string as follow.

HDR=NO means oledb will read all rows as data [NO HEADER]. So as your header columns are all text, it will treat all row data in all columns as text. After filling data into DataSet, you have to remove first row as it is not data.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";

Inutile

Thanks for the suggestion. It is still now working for me :( :( I have updated the code in question above. Can you please review it and let know whether it is correct or not? – Averi

HDR=YES shoud be HDR=NO, and remove first row of DataTable in DataSet – Inutile

Okies :) :) I will try and let you know. Thanks again. – Averi

It worked. :) the only problem is if user starts entering data from the first row rather than 2nd row again it is taking data-type :( – Averi

Answer 2

One fix we found, is to ensure that the first row contains a header. i.e. make sure that your column names are in the first row. If that's possible.

Then in your code, you have to programmatically ignore the first row, while at the same time scarfing your column names from it, if need be.

Use this in your connection string.

     IMEX=1;HDR=NO;

I'm not sure of this

     TypeGuessRows=0;ImportMixedTypes=Text

Oddity

Answer 3

I had similar issue.. i resolved it by splitting the connectionstring as mentioned in following string. Please note that after extended properties.. there is (char)34 to surround IMEX=1 addition to the string. without surrounding with (char)34, it will give error "cant find ISAM". Hope this resolves your issue for ACE provider also

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                        "Data Source=" + Server.MapPath("UploadedExcel/" + FileName + ".xls") +
                        ";Extended Properties=" +
                        (char)34 + "Excel 8.0;IMEX=1;" + (char)34;

Secateurs