Karuta’s ASP & M$ SQLserver

Dicas, códigos e outras coisinhas de meus estudos para não esquecer…

Archive for fevereiro \28\UTC 2012

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 (‘).

Posted in asp | Leave a Comment »