Excel 2003 Datasheet not getting imported in QTP 10 Datatable

Recently faced issue,
"Excel 2003 Datasheet not getting imported in QTP 10 Datatable"

Environment Conditions:
- QTP 10
- Excel 2003/2007/2010 with no Excel Addins installed(Addins like Data Classification tools)
- Win XP/7
- File Format of Data Sheet (.xls or 97-2003)


Drilling Down...
Tried many things...
checking compatibility mode of Excel workbook, column headers, etc. Nothing worked.

Finally I tried data row by data row.
Created a new excel with same headers, just went on adding set of rows and finally drilled down to row which had the problem.

Root Cause:

The data in the column contained n-Dash instead of Hyphen

n-Dash being UNICODE character occupies more space and then hyphen(an ASCII).

Now when the total character size of cells containing Unicode characters increases beyond a certain limit(not sure about the limit but this is what I got from the set of execution I performed), QTP is not able to import any sheet of that Excel Workbook.

The difference can be seen below. n-dash in 1^st row and hyphen in 2^nd.

Solution: Replace all n-Dash by Hyphen

Try this if you too are facing a similar problem. Hope it helps...

Cheers....