ASP importação EXCEL – problemas campos numéricos com alfa-numéricos
Posted by karuta em fevereiro 28, 2012
Não esquecer de colocar ASPAS DUPLAS depois de “Extended Properties=”
“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=””Excel 8.0;HDR=Yes;IMEX=1″””
“HDR=Yes;” indicates that the first row contains columnnames, not data. “HDR=No;” indicates the opposite.
“IMEX=1;” tells the driver to always read “intermixed” data columns as text. Note that this option might affect excel sheet write access negative.
TIP! SQL syntax: “SELECT * FROM [sheet1$]” – i.e. worksheet name followed by a “$” and wrapped in “[” “]” brackets.
TIP! 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.
Important note! The two double quota (“”) in the string are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax (ex. \”) or maybe single quota (‘).