March 4, 2009 at 1:42 pm
Hi Everyone,
I am trying to import some product data from a xls spreadsheet (provided by an external supplier) into SQL Server 2005. Some of the product descriptions are longer than 255 characters and they are being truncated to 255 when I bring them into SQL.
I have been using:
INSERT into dbo.BCatalogue
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=E:\blahblahblah\Export.xls;Extended Properties=Excel 8.0')
...[B_Catalogue$] WHERE SequenceKey is not null
The fields that are being truncated are defined as varchar(4000) in the BCatalogue table.
I've also tried adding a linked server and selecting from that.... same problem!
This is not a matter of the results field not displaying fully in management studio because if I use len(description) the larger rows return 255.
Has anyone got any ideas?
Bevan
March 16, 2009 at 4:28 pm
Just in case anyone else ever hits this problem, the solution is to add a row near the top of the worksheet that contains your maximum string length. In my case that meant filling out my cells with 1000 x's. I ignore the line when importing but SQL correctly identifies the length of the remaining fields.
March 16, 2009 at 5:03 pm
Good to know. Thanks for the follow-up Bevan!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 16, 2009 at 5:19 pm
There is nothing more annoying than finding a forum post describing the exact problem you have.... but no answer!
Which is exactly what happened to me when I was looking up this problem!
April 16, 2009 at 6:39 pm
Imports from Excel to SQL via OLEDB only looks 8 rows in by default to determine the column format, despite the format settings within Excel or Cast statements on a SELECT... FROM ...OPENDATASOURCE. So if I don't have 8 five digit zips, 8 straight dates, or a text column over 255 in the first 8 rows, OLEDB does not determine the data type and goes NULL on you or defaults to the 255 default column width of a text column in Excel.
Go into the registry editor and find
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows
Change the 8 to a higher number. I just set a machine to 100000 with no noticeable impact on the speed of the data transfer. It's all coming in clean now.
I've tried the blank and dummy row trick for years and only recently found an instance where it did not work.
January 12, 2011 at 11:35 pm
yeah! the limited of Execl made big trouble for me. Thanks
August 22, 2011 at 9:17 am
Thanks. It really works!
August 22, 2011 at 10:50 am
Using OPENROWSET for example:
SELECT * FROM OPENROWSET('Microsoft.jet.OLEDB.4.0','Excel 8.0;DATABASE=c:\temp\items.xls;IMEX=1;HDR=No','SELECT * from
[items$1]')
Note the IMEX setting. If it is NOT present the open rowset reads the first 8 or so rows to determine the length each column of the spread sheet, and uses that to read all the rows. With the IMEX set that step is not executed and the data is read in without regard to its length, and there is NO need to edit the registry.
I have not used the setting in an OPENDATASOURCE but it might be something you could attempt and let everyone know if that also works properly.
February 12, 2013 at 12:49 pm
bitbucket-25253 (8/22/2011)
Using OPENROWSET for example:
SELECT * FROM OPENROWSET('Microsoft.jet.OLEDB.4.0','Excel 8.0;DATABASE=c:\temp\items.xls;IMEX=1;HDR=No','SELECT * from
[items$1]')
Note the IMEX setting. If it is NOT present the open rowset reads the first 8 or so rows to determine the length each column of the spread sheet, and uses that to read all the rows. With the IMEX set that step is not executed and the data is read in without regard to its length, and there is NO need to edit the registry.
I have not used the setting in an OPENDATASOURCE but it might be something you could attempt and let everyone know if that also works properly.
Ok - I am using the IMEX=1 and it's still not working. My text is being truncated at 255. Maybe this post is old but i can't find any other updated info? Please help. I even tried putting the row with the most char's as the first row to test out the "First 8 row..." theory and it still cuts it off.
Thanks! Katie
February 12, 2013 at 1:10 pm
Careful with SSMS grids and text output settings chopping the displayed text to 255. Sometimes things are working correctly but display chopped off. I set mine to 8K and still get bit by this quirk. I don't see why it can't be set to unlimited.
February 12, 2013 at 3:26 pm
k_t_Schmidt (2/12/2013)
bitbucket-25253 (8/22/2011)
Using OPENROWSET for example:
SELECT * FROM OPENROWSET('Microsoft.jet.OLEDB.4.0','Excel 8.0;DATABASE=c:\temp\items.xls;IMEX=1;HDR=No','SELECT * from
[items$1]')
Note the IMEX setting. If it is NOT present the open rowset reads the first 8 or so rows to determine the length each column of the spread sheet, and uses that to read all the rows. With the IMEX set that step is not executed and the data is read in without regard to its length, and there is NO need to edit the registry.
I have not used the setting in an OPENDATASOURCE but it might be something you could attempt and let everyone know if that also works properly.
Ok - I am using the IMEX=1 and it's still not working. My text is being truncated at 255. Maybe this post is old but i can't find any other updated info? Please help. I even tried putting the row with the most char's as the first row to test out the "First 8 row..." theory and it still cuts it off.
Thanks! Katie
UMmmm - yes - i should have tested... IMEX=0, that worked.
January 31, 2019 at 3:32 am
Change TypeGuessRows in the Windows-Registry, according to https://support.office.com/en-us/article/using-the-typeguessrows-setting-for-excel-driver-6aa3e101-2a90-47ac-bf0f-7d4109a5708b
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply