Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Excel import truncates at 255 characters
Add to briefcase
11 posts, Page 1 of 2
12»»

Excel import truncates at 255 characters Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Bevan Keighley
Posted Wednesday, March 04, 2009 1:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:54 PM
Points: 282, Visits: 667
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
Post #668658
Bevan Keighley
Posted Monday, March 16, 2009 4:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:54 PM
Points: 282, Visits: 667
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.
Post #677028
RBarryYoung
Posted Monday, March 16, 2009 5:03 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
Good to know. Thanks for the follow-up Bevan!

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Post #677045
Bevan Keighley
Posted Monday, March 16, 2009 5:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:54 PM
Points: 282, Visits: 667
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!
Post #677058
Otis P. Driftwood
Posted Thursday, April 16, 2009 6:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 13, 2012 6:46 PM
Points: 4, Visits: 42
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.
Post #699036
congkhanh81
Posted Wednesday, January 12, 2011 11:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 05, 2012 3:45 AM
Points: 1, Visits: 3
yeah! the limited of Execl made big trouble for me. Thanks
Post #1047018
jc.arroyo10
Posted Monday, August 22, 2011 9:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 27, 2012 10:16 AM
Points: 1, Visits: 3
Thanks. It really works!
Post #1163366
bitbucket-25253
Posted Monday, August 22, 2011 10:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 5,099, Visits: 20,191
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.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read
Post #1163479
k_t_Schmidt
Posted Tuesday, February 12, 2013 12:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:49 PM
Points: 16, Visits: 103
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
Post #1419159
Bill Talada
Posted Tuesday, February 12, 2013 1:10 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:31 AM
Points: 65, Visits: 443
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.
Post #1419166
« Prev Topic | Next Topic »

Add to briefcase
11 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.