October 23, 2008 at 11:29 pm
Comments posted to this topic are about the item SSIS and Excel 2007
My Blog:
October 24, 2008 at 3:15 am
We have IA-64 servers........other than using a CSV converted version......will Microsoft ever release excel (Jet) drivers so we can use the excel Tasks?
Do you have any methods for bringing in Excel files as is on an IA64 platform?
October 24, 2008 at 3:41 am
Nice one...
October 24, 2008 at 5:44 am
If you want to do this in sql, refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Failing to plan is Planning to fail
October 24, 2008 at 6:52 am
Madhivanan (10/24/2008)
If you want to do this in sql, refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Unfortunately "Microsoft.Jet.OLEDB.4.0" does not exist for IA-64..........unless you have heard otherwise.....
If you have we would be very pleased indeed!! if so please provide MS link to the IA-64 OLEDB jet driver.
October 24, 2008 at 8:18 am
From what I've seen, importing data from excel can be very hazardous so I avoid it whenever possible. The import process will interpret your data type by sampling the data irregardless of how you define the data type in SSIS or on your destination table. So if the first part of your file contains numbers and the last contains characters... the import process will simply throw out the data with characters and load NULL's without any warnings or errors. I know there is a reg key you can set to determine the number of records that are sampled, but the fact that the Excel driver does this is bad enough for me. :crazy:
David
October 24, 2008 at 8:31 am
"From what I've seen, importing data from excel can be very hazardous etc"
I agree on that.
But I have no experience with Excel2007 yet. Does it work as 2003 in this sence ?
Dinesh what do you say?
For me I will still save the sheets as TAB sep textfiles and import them so.
//Gosta
October 24, 2008 at 8:40 am
"Unfortunately "Microsoft.Jet.OLEDB.4.0" does not exist for IA-64..........unless you have heard otherwise"
If you keep to textfiles you don't need the Jet driver if you use bulk insert instead.//
//Gosta
October 24, 2008 at 10:26 am
Awesome. Thank you. I could have used this last week... but I got through it.
I had to do a comparison of an excel 2007 file to an sql table and follow that with a report on the findings in SSRS.
I found that I had to export to an earlier version of excel for the reporting services piece. I couldn't find how to use a driver for excel 97 in reporting services. Did I miss something there?
Thank you
October 24, 2008 at 10:28 am
David (10/24/2008)
From what I've seen, importing data from excel can be very hazardous so I avoid it whenever possible. The import process will interpret your data type by sampling the data irregardless of how you define the data type in SSIS or on your destination table. So if the first part of your file contains numbers and the last contains characters...
I have found this to be true as well. I haven't started experimenting with SSIS in 2008 yet, but hopefully there's a setting that addresses this. From what I remember, SSIS only looks at the first 200 rows to try to analyze the data type. It would obviously be better if it tried to find every possible data type in a column and then chose the lowest common denominator.
However, I'd make the argument that one should be familiar with the data that's being imported, and should therefore be able to anticipate any data types for each column, and handle any deviation with row redirects to text files so that the package execution doesn't come to a dead stop because of a few bad apples in a source.
Cheers,
-m
October 24, 2008 at 10:39 am
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.
October 24, 2008 at 10:55 am
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
October 24, 2008 at 6:23 pm
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/
October 26, 2008 at 9:49 am
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!)
October 26, 2008 at 11:08 am
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
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply