Sql 2005 Import Data From Excel Problem
I'd always stayed away from the import wizard in Sql 2005 Management Studio. One, because I really liked the importing functionality in Sql 2000; I could sail through it with little effort (or thought). Two, I've changed jobs and don't have to import many excel files into Sql.
Finally, I pulled some production logs and they came in as an excel sheet. I didn't want to work with 100MB excel file and try to find information in it so I wanted to import it into Sql so I could run queries on it. I tried importing plain jane style into a table and received this error message"
There was an error with output column "pathandquery" (45) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one of more characters had no match in the target code page."
I tried changing the target destination table for all the columns to be ntext, but I still had the same error message to deal with. I did some yahooing (hey, if google is a verb...) and came across this knowledge base article:
PRB: Transfer of Data from Jet 4.0LEDB Source Fails with Buffer Overflow Error
By default the Jet provider will only read the first 8 rows to set up its internal buffer for transferring data to sql. The first 8 rows of my datasource were small, but some of the later rows have large amounts of text in the column. When it reached those, the buffer blew chunks and was unable to recover. I guess it beats an unhandled buffer overflow error that can be exploited, yah Microsoft.
You need to make a modification to the registry to change the behavior.
HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows
The default value is 8. The KB article said the range of valid values is 0 to 16. If you set it to zero, it will scan 16,384 rows, slowing down the import process. I changed my value to 0; it was a little slower, but it worked.
Bill's sage coding advice: Slow and works is ALWAYS better than fast and broken.