×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

FYI

本文发表在 rolia.net 枫下论坛CAUSE
The Jet OLE DB provider reads a registry key to determine how many rows are to be read to guess the type of the source column. The registry setting is:

HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

By default, the value for this key is 8. Hence, the provider scans the first 8 rows of the source data to determine the data types for the columns. If any field looks like text and the length of data is more than 255 characters, the column is typed as a memo field. So, if there is no data with a length greater than 255 characters in the first 8 rows of the source, Jet cannot accurately determine the nature of the data type.
WORKAROUND
WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

To change the value of TypeGuessRows, use these steps:
On the Start menu, click Run. In the Run dialog box, type Regedt32, and then click OK.
Open the following key in the Registry editor:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
Double-click TypeGuessRows.
In the DWORD editor dialog box, click Decimal under Base. Type a value between 0 and 16, inclusive, for Value data.
Click OK, and then exit the Registry Editor.
A second way to workaround this problem (without modifying the registry) is to make sure that rows with fields, which have data 255 characters or greater, are present in the first 8 rows of the source data file.更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / 用SQL IMPORT WIZARD将EXCEL文件导入到SQL中,由于某些CELL的内容很长,超过200个字符,结果说超过bufferized size,不成功,应该怎么解决?thanks a lot
    • FYI
      本文发表在 rolia.net 枫下论坛CAUSE
      The Jet OLE DB provider reads a registry key to determine how many rows are to be read to guess the type of the source column. The registry setting is:

      HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

      By default, the value for this key is 8. Hence, the provider scans the first 8 rows of the source data to determine the data types for the columns. If any field looks like text and the length of data is more than 255 characters, the column is typed as a memo field. So, if there is no data with a length greater than 255 characters in the first 8 rows of the source, Jet cannot accurately determine the nature of the data type.
      WORKAROUND
      WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

      To change the value of TypeGuessRows, use these steps:
      On the Start menu, click Run. In the Run dialog box, type Regedt32, and then click OK.
      Open the following key in the Registry editor:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
      Double-click TypeGuessRows.
      In the DWORD editor dialog box, click Decimal under Base. Type a value between 0 and 16, inclusive, for Value data.
      Click OK, and then exit the Registry Editor.
      A second way to workaround this problem (without modifying the registry) is to make sure that rows with fields, which have data 255 characters or greater, are present in the first 8 rows of the source data file.更多精彩文章及讨论,请光临枫下论坛 rolia.net
      • thanks a lot, It really works. btw, where did you find it?
        • find solution from Microsoft website for MS Error.
    • 当你作到"select source tables and views" 那一步的时候,在"Transform"下面有个button(上面有3个点),点这button进去"Column Mappings", 从哪里可以修改column size
      • 兄弟,这个方法我试验过了,我改成NTEXT类型,但还是不工作,好象没有作用的。楼上的写的解决问题了。THX。
        • 呵呵,sorry,你不是XD,是猫姥姥,:D
          • 这可如何是好