Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SSIS and Excel 2007 Expand / Collapse
Author
Message
Posted Friday, October 24, 2008 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 30, 2008 6:13 PM
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.
Post #591368
Posted Friday, October 24, 2008 10:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:28 AM
Points: 902, Visits: 972
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



Post #591378
Posted Friday, October 24, 2008 6:23 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #591617
Posted Sunday, October 26, 2008 9:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 8:24 AM
Points: 9, Visits: 136
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!)
Post #591822
Posted Sunday, October 26, 2008 11:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:24 AM
Points: 203, Visits: 1,304
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
Post #591831
Posted Sunday, October 26, 2008 5:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 5:38 AM
Points: 6, Visits: 91
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!! :D
Post #591876
Posted Monday, October 27, 2008 9:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 03, 2013 8:20 PM
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 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



Post #592198
Posted Monday, October 27, 2008 2:32 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 25, 2013 7:43 AM
Points: 1,384, 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




Post #592439
Posted Monday, October 27, 2008 3:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:24 AM
Points: 203, Visits: 1,304
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



Post #592470
Posted Monday, October 27, 2008 3:19 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:28 AM
Points: 902, Visits: 972
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



Post #592475
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse