Excel import truncates at 255 characters

  • Bevan Keighley

    SSCommitted

    Points: 1825

    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

  • Bevan Keighley

    SSCommitted

    Points: 1825

    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.

  • RBarryYoung

    SSC Guru

    Points: 143327

    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]

  • Bevan Keighley

    SSCommitted

    Points: 1825

    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!

  • Otis P. Driftwood

    SSC Enthusiast

    Points: 180

    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.

  • congkhanh81

    SSC Journeyman

    Points: 91

    yeah! the limited of Execl made big trouble for me. Thanks

  • jc.arroyo10

    Valued Member

    Points: 73

    Thanks. It really works!

  • Ron McCullough

    SSC Guru

    Points: 63877

    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[/url]
    Before posting a performance problem please read[/url]

  • k_t_Schmidt

    Say Hey Kid

    Points: 683

    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

  • Bill Talada

    SSChampion

    Points: 11956

    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.

  • k_t_Schmidt

    Say Hey Kid

    Points: 683

    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.

  • benigemperle

    Newbie

    Points: 5

    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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply