Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS and Excel 2007


SSIS and Excel 2007

Author
Message
btainer
btainer
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
I haven't experienced that particular anomoly. I'll have to keep an eye out for that. I do try to avoid using Excel files... general nuisance and best avoided. But when the files are maintained by people in excel v.???? the best thing to do is go with it if you can't convince them to change formats. It's easier to deal with it from that perspective then it is to count on the user to save the file in a specified format for the process or to add a manual step to your own process.

Sometimes we just don't have a choice.
DavidSimpson
DavidSimpson
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 1074
I really hope the driver gets updated to fix this issue, at least for Excel 2007 files. Really annoying and potential dangerous when you define an input column as varchar yet the import process throws out all the data where there are characters in the column just because the first few rows are numeric.

Because of this I do like Gosta stated and export the data to a delimited file and bulk insert from that. If forced to import from Excel files, make sure you have plenty of quality control checks running on the imported data and explain to management the risks involved.

David



timothyawiseman
timothyawiseman
SSC Eights!
SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)

Group: General Forum Members
Points: 802 Visits: 920
The article was well done, but it was much lighter on content than most articles here. A little more depth to this otherwise good work would have been nice.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
bilx10
bilx10
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 137
I know my data very well and also that ADO in excel doesn't handle columns with mixed data. A cell with numeric data will be taken as numeric and any data with alphanumeric characters ignored even if the destination is varchar datatype. Columns of data where for example there is a column of order numbers from different customers with different order number formats are a problem and even values can be - individual cells in a column of money values can be formated as text especially if they are pasted in from a word document. ADO just ignores the value. Any of these values could be easily fixed by a convert transformation or script transformation if provided to the package but they are left out by the excel export process so they don't reach the package for the errors to be handled. Of course if the excel files where properly created with each column formatted to the right datatype and data validated and verified there would be no problem with ADO (its an ADO problem not BIDS) but if you have been able to get the business units providing the excel files to do that please let us in on the secret of how thats done - most of the business users creating these sources don't have a clue what a datatype is and have no interest in learning. That includes their Management so there is no support for improvement.
I have found that the only way to guarentee getting all data (including bad data) from excel sheets into my package is to convert them to delimited files, either tab delimited or csv then load those. All data will be brought in which allows me to set up error handling if needed. I use an activex script do this by creating an excel automation object to open the excel workbook and save the individual sheets as txt or csv that can then be handled by SSIS loops and transformations. This was a quick fix copied from my original DTS version that I will replace with a script task when I have the time to work out how to do it with a script task in BIDS - you can't directly add the references needed in BIDS. (Unless someone out there has a way!)
Gosta Munktell
Gosta Munktell
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 1408
This is an interesting discussion. We are not alone with need to import data
from Excel.
At first look at the front Excel. Is the user permitted to key in any garbage to
be imported to the database? To be short I still keep to the tab textfiles but I also
do it in un other way.
I design budget/prognoses system(s) with Excel as front and SQL-server as back.
All coding (shuffling data to and from Excel is VBA remote OLE DB ADO in Excel.
Very short the user fetch an Excel template from an intrant server and populate
one or more sheets with data by choise ie cost center. The user then see actual
figures and existing budget/prognoses. All cells showing values are locked!
Certain cells on certain sheets are mark with a different colour and open for just numbers
to be inserted in a certain range. The workbook/sheets recalculate then the user key in
values (so it is a bit of simulation). Also comments can be entered or references to other
documents /stored in the database. When the user is satisfied he/she press a button
and the values (only in the open cells) are inserted in the database by a traditional insert
statement. (Can be update also).
Best luck with your Excel/database adventure.
//Gosta
guruprasat85
guruprasat85
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 93
thanks Dinesh but in Sql Server 2005 its easy too.. All you have to do is to use a data flow task and you are on!! BigGrin
mcloney
mcloney
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 83
bilx10 (10/26/2008)
I know my data very well and also that ADO in excel doesn't handle columns with mixed data. A cell with numeric data will be taken as numeric and any data with alphanumeric characters ignored even if the destination is varchar datatype.


Hmmm, I don't think I've seen this, but I'm definitely going to watch for it now.

bilx10 (10/26/2008)
Of course if the excel files where properly created with each column formatted to the right datatype and data validated and verified there would be no problem with ADO (its an ADO problem not BIDS) but if you have been able to get the business units providing the excel files to do that please let us in on the secret of how thats done - most of the business users creating these sources don't have a clue what a datatype is and have no interest in learning. That includes their Management so there is no support for improvement.


I completely agree with you on that. Sigh Sad I think as DBAs we all try to impart the importance of following basic business rules to management, but I think as part of life, we're all disappointed when those rules are consistently broken with no consequences to anyone but us (and the users of the system who depend on timely and accurate data).

Cheers,

-m



danschl
danschl
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1388 Visits: 644
I agree with david the importing from excel is bad at best

Pretty bad considering they are both microsoft products

I always go to csv files to import

Although MS screwed that up also with sql2005

I had several CSV imports that worked sql2000 but failed in sql2005 and sql 2008



Gosta Munktell
Gosta Munktell
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 1408
As I allready have stated I have abandoned Excel native format import because of the
problems.
But I became curios , will it work like described in the article which started this
thread. I have one machine with XP and VB2005, SQLserver2005 and Office2007 updated
I followed the procedure in the article but always get the encouring message:

Test connection failed because of an error in initializing provider. Invalid UDL file.

To my knowledge I have not done anything wrong but who knows?
Google did not give any strait answer so I gave up.
You fellows have you seen any pitchfalls like what I get?

//Gosta
DavidSimpson
DavidSimpson
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 1074
I followed the procedure in the article but always get the encouring message:

Test connection failed because of an error in initializing provider. Invalid UDL file.

I tried this as well and get the same error message as Gosta.

David



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search