Home » C# ProgrammingRSS

Read Excel File using OleDb

I am using C# with VS 2010 and Office 2010.

I am trying to read Excel spreadsheet using OleDb. the problem is in row 3 column G; I have a date and the cell is formated as date, so the subsiquent cells in colum G are read as dates even though they are just numbers and those cells formated as general and the values come out as 1/1/1990.  How can I read cell(G3) as a date or a string and anything below it as a number or string.

Here is my connection string:

            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyFile.xlsx;Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\");

            try
            {
                con = new OleDbConnection(connectionString);
                con.Open();

                dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

 

I can send you the Excel file if needed.

Thank You

Peter

 

10 Answers Found

 

Answer 1

Hi Peter,

Not sure how you display the data, but I see some problems in your code such as lacking a quote at the end of the connectionString and no need to explicitly call con.Open() to use dataset storing the data .

Here is a sample code that works with a datagridview to show the data from an Excel sheet:

            string connectString =
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\testit.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";
            OleDbConnection conn = new OleDbConnection(connectString);
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From [Sheet1$]", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;

Please try again and if have any problem please let me know.

Thanks.


Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 


Please remember to mark the replies as answers if they help and unmark them if they provide no help.
 

Answer 2

Connection is not my problem.

I can read  the data, but the data I read is not correct.

Can you try this to see if you get the same result.

In Cell A1 through A8 key in a date and format those cells as date

in Cell A9 key in a number

Then read the spreadsheet and see if you get a number in row[8][0]

 

 

Answer 3

Hi Peter,

It doesn't matter how we set the format in the Excel sheet. I got the correct format as always, can you let me know your display and data retrieving code?

Or can you just create a new project with the code I provided to see if there is any problem?

Thanks.


Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 


Please remember to mark the replies as answers if they help and unmark them if they provide no help.
 

Answer 4

Hi Peter,

It doesn't matter how we set the format in the Excel sheet. I got the correct format as always, can you let me know your display and data retrieving code?

Or can you just create a new project with the code I provided to see if there is any problem?

Thanks.


Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 


Please remember to mark the replies as answers if they help and unmark them if they provide no help.


I've created the project using your code and I am getting the same results, although I can not duplicate it when I create the spreadsheet, but all of the spreadhseets comming from a customer having the same problem so I am still stuck.

I can send you the spreadsheet if you want.

 

Answer 5

Thanks for your response, Peter.

You can send it to v-fifei<at>microsoft<dot>com (with this thread's url)

Thanks.


Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 


Please remember to mark the replies as answers if they help and unmark them if they provide no help.
 

Answer 6

Thank You

I have emailed the problem spreadsheet.

 

 

Peter

 

Answer 7

Hi Peter,

I've recieved your mail, Thanks.

As the format of your sheet's G column is inconsistent, I suggest you to read  the specific range instead of the whole sheet.

The code looks like:

            string connectString =
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\testit.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";

            OleDbConnection conn = new OleDbConnection(connectString);
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From [Sheet1$G19:G45]", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);

or

            conn.Open();
            OleDbCommand cmd = new OleDbCommand("Select * From [Sheet1$G19:G45]", conn);
            OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            while (reader.Read())
            {
                object item = reader.GetValue(0);
                textBox1.AppendText(item.ToString() + Environment.NewLine);
            }
            reader.Close();

HTH.

Thanks.


Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 


Please remember to mark the replies as answers if they help and unmark them if they provide no help.
 

Answer 8

Thank for the suggestion, it might be the workaround.

 

The problem is I have to convert the entire worksheet to .CSV format and Excel is not installed on the computer.

 

Answer 9

Hi Peter,

For more details about the excel  development issue, you can also use http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads for more suggestions.

Have a nice day.


Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 


Please remember to mark the replies as answers if they help and unmark them if they provide no help.
 

Answer 10

Hi Peter,

For more details about the excel  development issue, you can also use http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads for more suggestions.

Have a nice day.


Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 


Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Thank you for your help!

 

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter