Problem in load excel data into dataset using datadapet.fill method

Hi

When i load the excel data into dataset using datadapet.fill method, It shows empty cell value where invalid dates are present. I want to show the error of invalid date. kindly help to me to sort the issue.
Shahid-AbbasiAsked:
Who is Participating?
 
Shahid-AbbasiConnect With a Mentor Author Commented:
After a long struggle, i am able to find the answer of this mistery. There could be multiple reasons, but the most possible reason is to add 'IMEX=1' in connectionstring. Other possible reasons are.


Connection String: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FilePath;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

1) "HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

2) "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

SQL syntax "SELECT * FROM [sheet1$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

Important:

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows".

That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."
0
 
Praveen KumarArchitectCommented:
Then you should use, DataReader instead of Adapter.
1. Read the data using DataReader
2. Try Parse your date column to valid dates, if not possible alert to the user.

0
 
Shahid-AbbasiAuthor Commented:
same thing is happening while using a datareader aswell.
0
 
Shahid-AbbasiAuthor Commented:
strange, this issue is very common, but expert team is failed to provide me the solution of this issue,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.