Excel source having more than 255 characters

Question

Excel source having more than 255 characters

itzmee

Say Hey Kid

Points: 700
More actions
February 5, 2010 at 10:37 am

#229671

Hi,
I have my SSIS package importing data from excel source to table. My excel file can contain cell text of more than 255 characters.
When i import data directly from excel to table, it truncates data after 255 and ports only first 255 characters to table.
I need the complete text to be ported to table.
Any suggestions pls...
Thanks.

Viewing 15 posts - 1 through 14 (of 14 total)

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

WayneS SSC Guru Points: 95409 More actions · Answer 1

by default, excel text fields have a max length of 255 when importing. If using larger than this, you'll need to set up that field as a memo and not a text.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

itzmee Say Hey Kid Points: 700 More actions · Answer 2

Hi, Thanks for your reply. Please explain how to do that..

WayneS SSC Guru Points: 95409 More actions · Answer 3

itzmee (2/5/2010)
Hi, Thanks for your reply. Please explain how to do that..

Right-click your Excel Source, and select "Show Advanced Editor".

Go the the "Input and Output Properties" tab.

Expand the "Excel Source Output" node.

Expand the "External Columns" and "Output Columns" nodes.

Click on the appropriate column under "External Columns".

Change DataType to "Unicode Text Stream".

Click on the appropriate column under "Output Columns".

Change DataTYpe to "Unicode Text Stream".

Click "Ok".

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

itzmee Say Hey Kid Points: 700 More actions · Answer 4

Hi,

I tried to do that. The datatype of External column is always getting reset to its original one. I am able to change only the datatype of Output column. Please help.

drew.allen SSC Guru Points: 76944 More actions · Answer 5

SSIS bases its datatypes on a sampling of the records (8 rows by default) from the Excel file. If none of the records in the sample are longer than 255 characters, you cannot set the field to memo.

You can change the sample size by changing the value of TypeGuessRows in the registry key for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

itzmee Say Hey Kid Points: 700 More actions · Answer 6

Hi, Thanks for the information. I tried it by placing my longest data in the second row. Even then i am not able to set the datatype of external columns to DT_NTEXT.

SQLDuck SSC-Addicted Points: 418 More actions · Answer 7

Thanks so much...upping the TypeGuessRows value solved this annoying issue for me.

jwmott SSC Eights! Points: 979 More actions · Answer 8

Just thought I would share my two cents. I ran into the same problem when going from Microsoft Access to Microsoft Excel to SSIS. If you export to XML directly from Access, then you can get around a lot of issues with data types, field lengths, and so on. I had struggled with importing data from Excel to SSIS until I got the source Access from the client and did the export to XML which cut my problems down tremendously.

Mike Vassalotti SSC Enthusiast Points: 124 More actions · Answer 9

All,

I my opinion, this is a big bug by MS in SSIS. There is a very simple effective workaround that works bullet-proof. Never import directly from Excel. Open the Excel workbook and export the data to tab-delimited ASCII file(s). Even better, ask your providers to do the export to ASCII for you, and submit the ASCII file to you, so, you don't have to do it. Then, from SSIS, import from the tab-delimited ASCII file(s). From ASCII files, SSIS can import fields with thousands of chars, without a glitch. It really works. Try it!

Mike Vassalotti

Herndon Virginia - USA

gward 98556 Mr or Mrs. 500 Points: 538 More actions · Answer 10

I had a similar issue in SSIS 2008 with an Excel file that had columns wider than 255 characters, as well as embedded text qualifiers and delimiters. A real pain as Microsoft were not handling embedded delimiters well in SSIS at the time. I got around it by saving from Excel in csv format, then putting a Schema.ini file in the same folder as the csv, and specifying the data types of each column (Memo for long strings, or Text, Date, Double etc) within the Schema.ini file. In Connection Managers I chose "New OLE DB Connection", New Provider: Microsoft Jet 4.0 OLE DB Provider.

Clicked "All" in left pane and set Extended Properties to the following (without the quotes): "text;HDR=Yes;FMT=Delimited"

Clicked "Connection" in the left pane and set Database file name to the folder where the csv is (without including the csv filename).

Created an OLE DB Source in the Data Flow and pick the new connection manager in the first dropdown.

Set data access mode to "SQL command" and set the SQL command text to "SELECT * FROM myexportname.csv" or whatever the name of the csv file is.

The text within the Schema.ini file should look something like this:

[myexportname.csv]

ColNameHeader = True

Format = CSVDelimited

DateTimeFormat=dd/MM/yyyy

Col1=Category Text

Col2=Registration Text

Col3=Description Text

Col4="Sales Big Description" Memo

etc....

If you have to load multiple csvs from a single folder with this method, they all must use the same Schema.ini file but this file can include separate schemas for each csv filename.

Not fun to set up but once done it is reliable.

riggerjon SSC Rookie Points: 45 More actions · Answer 11

Thanks to the contributors in this thread - it helped a bunch! Just three things to add:

1. My Jet registry key was under HKEY_LOCAL_MACHINE/SOFTWARE/Wow6432Node/Microsoft/Jet...

2. Setting TypeGuessRows to 0 can affect performance on large spreadsheets, but it scans all rows and avoids the issue in this thread.

3. Here's more detail on editing the registry:

gward 98556 Mr or Mrs. 500 Points: 538 More actions · Answer 12

Don't forget that whenever you ever move the package to another server it will fail again until you or a colleague finds out about the registry hack. Ultimately that is why I didn't choose that route. If an SSIS package needs a registry hack to work then it's definitely worth putting a comment about it somewhere obvious in the package!:-)

waxingsatirical SSC Eights! Points: 934 More actions · Answer 13

Have just been hitting these problems in SSIS with Visual Studio 2015.

Even though there is an 'Advanced Editor' which allows you to set the external datatypes to DT_NTEXT, this reverts to the detected type as soon as the window closes. Didn't try the registry hacks, just edited the first line of the spreadsheet to force correct datatypes.

This also alphanumeric columns that just happen to have only numbers in the first few rows. SSIS helpfully detects these as floats or something.

drew.allen SSC Guru Points: 76944 More actions · Answer 14

gward 98556 (2/27/2014)
Don't forget that whenever you ever move the package to another server it will fail again until you or a colleague finds out about the registry hack. Ultimately that is why I didn't choose that route. If an SSIS package needs a registry hack to work then it's definitely worth putting a comment about it somewhere obvious in the package!:-)

No, this registry entry is used when editing a package, so this is only an issue when the package is being developed, so the registry hack only needs to be applied on the developers' machines.

Drew

PS: I realize that this is an old thread, but this information is still relevant.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA