Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
280 views
in Technique[技术] by (71.8m points)

c# - DateTime format mismatch on importing from Excel Sheet

I'm importing data from an Excel sheet on to a DataTable using the following code:

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0");
con.Open();
_myDataSet = new DataSet();
OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + "Sheet1" + "$]", con);
myCommand.Fill(_myDataSet);
con.Close();

I have a Date column in the Excel sheet in the format dd/MM/yyyy. The above code is failing when the date is dd/MM/yyyy (eg. 27/12/2009). How to specify the date format?

EDIT (adding more details):

It is not throwing any exception. Data is imported to the DataSet until the row where an invalid Date format is encountered. I have the date as dd/MM/yyyy in Excel sheet. When I import using OleDbDataAdapter, it is expecting the date in the Excel sheet to be in MM/dd/yyyy. No naturally when it encounters a date such as 27/2/2009 it stops the process of importing, though no error/exception is thrown. So I'm having only partial results in DataTable.

Please help.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Pulling in Date or other mixed-data column items in the past has bit me with Excel.

It seems that their provider "peeks" ahead XX rows (depending on Provider version) to determine what the column type is at runtime. The prior answers that apply properties to your connection have helped me in the past, but do not help when you have BLANK dates and/or different values in the column itself - it confuses the Excel driver.

What I recommend is that you use FileHelpers or another third-party library instead. Infragistics Excel component has treated me very well, while FileHelpers is opensource.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...