Excel formatting in SSIS

Question

Excel formatting in SSIS

sperry-750868

SSCommitted

Points: 1505
More actions
December 9, 2010 at 5:15 am

#244070

Hi all.
I am trying to upload spreadsheets from users using SSIS. This is an automated process and i HAVE to use excel.
Since these spreadsheets come from users i never really know what sort of rubbish that i am going to get through. I have one column that is causing me issues, the ID column. Since the ID can start with a zero (Stupid i know) I cant be formatted as a number, it is formatted as general.
As you can see from the attached example, i have a column of ID's. All are formatted as general and most of them are right justified. Half way down there are 20 or so rows that are left justified and have an excel error attached the them saying that they are numbers stored as text and should be converted. Something in this error message is preventing the rows from going into the database, any ideas?

Viewing 8 posts - 1 through 7 (of 7 total)

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

In my experience, this happens because the Excel OLEDB provider scans the first few rows (from memory 8 by default...configurable by a registry setting) and determines what the most appropriate datatype should be for the column. When you get to the row where excel is suggesting that the content could be formatted as a number, the provider is probably interpreting the value as a string and doesn't convert this to a number for you. I suspect the you are actually getting nulls in returned.

Have a look at http://www.connectionstrings.com/excel. You probably need to change the connection string in you package so that it includes the IMEX=1 option.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

As HappyCat said: add IMEX=1 to your connection string. IMEX stands for intermixed data types. In other words, if Excel notices that a column contains more than one data type, it will use the string data type (since Excel "guesses" the data type of a column).

Furthermore, you should change the registry setting (also mentioned by HappyCat): TypeGuessRows. Set it to 0. Excel will scan the whole column to find the appropriate data type.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

tmitchelar Hall of Fame Points: 3391 More actions · Answer 3

da-zero (12/10/2010)
Furthermore, you should change the registry setting (also mentioned by HappyCat): TypeGuessRows. Set it to 0. Excel will scan the whole column to find the appropriate data type.

This is a great tip. You can also do this for text files. Speaking of - I have had better luck when converting my excel files to text/csv files first. I also try to request business to send only flat files and this is one of the few things I stand my ground fairly firm on when it comes to business users. I've lost count of the issues that I've run into over the years when dealing with excel and behavior only gets more bizarre when working in 64 bit environments.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 4

Sperry

You say : "This is an automated process and i HAVE to use excel."

I then suppose you have Excel installed at server computer.

I always avoid to use SSIS to handle office automation.

In your case convert the Excelfile to a .txt file as sperry sugest and

use SSIS or bulk insert to load the data.

If the users will not send you plain textfiles you can use a macro

to open and save the Excelfiles. Another approach which I usually go

for is to use VBA and ADO to load the database.

Gosta M

wu.wuqiang Ten Centuries Points: 1008 More actions · Answer 5

I made a SSIS package successfully by using the method in the artical:

http://www.simple-talk.com/community/blogs/jonathanallen/archive/2010/09/07/94303.aspx

I never try it from Excel to SQL server but hope it will work.

sperry-750868 SSCommitted Points: 1505 More actions · Answer 6

Hi Guys.

Thanks for all your help, sorry it has taken me so long to reply. I have been in bed with flu.

The IMEX solution looks like it will do the job but i am having trouble getting the connection string working. I know that i am doing something stupid but i just cant see what.

There is the connection string. Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

The error i am getting is "Expression cannot be evaluated" "Parsing the expression "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";" Failed. The equal sign (=) at line number "1", character number 9, was not expected. A double equals sign (==) may be required at the location specified."

sperry-750868 SSCommitted Points: 1505 More actions · Answer 7

Figured it out, i was missing my slashes! Thanks again for all your help!