Unable to Import Text File to SQL, “Truncated Data” Error

There seems to be a bug or at least an oversight in all versions of MS SQL wherein you cannot import from a text file to a table if SQL doesn’t like the data in the source file. I’ve tried everything from saving the text to an Excel sheet in a trimmed column to selecting only certain columns for import, nothing ever works. Even saving the bad column to a memo or nvarchar(max) field doesn’t work. This week I found a work around.
These are the errors you might see:
Messages Error 0xc020901c: Data Flow Task: There was an error with output column "English String" (18) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "English String" (18)" failed because truncation occurred, and the truncation row disposition on "output column "English String" (18)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Sheet1$" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. (SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038. (SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. (SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039. (SQL Server Import and Export Wizard)

SOLUTION:

You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key and changing it to 0. This will force it to scan all rows. Even when working with 500k+ records this takes less than a second for me.
NOTE: Restart SQL Management studio after your change!
Screen shot below shows the location on my Windows 7 x64 box.