<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Dinesh Asanka
»
How to Read Excel Cells from SQL Server...
28 posts, Page 3 of 3
««
«
1
2
3
How to Read Excel Cells from SQL Server Integration Services
Rate Topic
Display Mode
Topic Options
Author
Message
gary.humphryes
gary.humphryes
Posted Thursday, April 29, 2010 4:28 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, December 04, 2012 8:38 PM
Points: 4,
Visits: 74
Yes Rich, I spent days trying to crack this and in the end had to cut my losses and battle the SSIS import which in itself proved ridiculously difficult. This is largely do to the inability to set Excel metadata on import which then truncates all text fields to 255 characters. A dark art combination/permutation of IMEX=0/IMEX=1 in the source file connection string and/or padding the source spreadsheet with hidden dummy large text fields eventually got the data in, but as newbie to the asp.net/SSIS environment I hear myself repeatedly asking the same question "why should it ever be this hard?". Not exactly an inspiring start to this environment but at least it's good to know I'm not alone on this one.
Post #913317
Koen Verbeeck
Koen Verbeeck
Posted Friday, April 30, 2010 12:52 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 3:23 AM
Points: 9,367,
Visits: 6,464
Don't worry Gary, it's just Excel, the rest of SSIS works (practically) fine.
How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #913487
rmechaber
rmechaber
Posted Friday, April 30, 2010 6:35 AM
SSChasing Mays
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656,
Visits: 2,921
gary.humphryes (4/29/2010)
Yes Rich, I spent days trying to crack this and in the end had to cut my losses and battle the SSIS import which in itself proved ridiculously difficult. This is largely do to the inability to set Excel metadata on import which then truncates all text fields to 255 characters. A dark art combination/permutation of IMEX=0/IMEX=1 in the source file connection string and/or padding the source spreadsheet with hidden dummy large text fields eventually got the data in, but as newbie to the asp.net/SSIS environment I hear myself repeatedly asking the same question "why should it ever be this hard?". Not exactly an inspiring start to this environment but at least it's good to know I'm not alone on this one.
Gary, if you want, I can send you the code I used - mostly downloaded from others - to kill the Excel process. I didn't (and don't) like forcing an application closed like that, but in the end, it worked. Here's another good page summarizing the issue and how to address it:
http://devcity.net/Articles/239/4/article.aspx
.
I feel your pain about Excel imports. If Excel and SQL Server weren't both MS products, I could understand the mismatches being a problem. MS clearly intended Excel to be a source/destination for SSIS: they didn't do their homework on this, IMO.
That said, once you get it working, it's a good thing to be able to pull in data from Excel. One of the things I use it for is when I need a department to review data for mass updates: I create an Excel file with all the information they need, I add a new column that they use to tell me the row should or should not be updated, then I pull the file back into a staging table with SSIS so I can update the correct data. Saves everyone a lot of time, and I have their Excel file for CYA
Rich
Post #913626
Gosta Munktell
Gosta Munktell
Posted Friday, April 30, 2010 11:50 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, April 26, 2013 4:05 AM
Points: 193,
Visits: 1,248
"I feel your pain about Excel imports. If Excel and SQL Server weren't both MS products, I could understand the mismatches being a problem. MS clearly intended Excel to be a source/destination for SSIS: they didn't do their homework on this, IMO. "
I started to use SSIS but gave up. VBA in Excel is easy to learn and you can run an Excel process
on the client computer fetching data or importing data from/to a remote server.
/Gosta
Post #914200
sqlusers
sqlusers
Posted Tuesday, May 04, 2010 3:04 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 10:50 PM
Points: 194,
Visits: 461
Hi Dinesh,
Nice Article!!!
Is it possible to use cell name rather than B3,B4? Why i am asking is, because if the position of the cell has changed in future then it will not taken values from the excel. If we can place cell name it will be so easy if sometimes cell position gets changed.
Please advice me if i am wrong.
Post #915147
william.ede
william.ede
Posted Thursday, May 06, 2010 7:22 AM
Valued Member
Group: General Forum Members
Last Login: Wednesday, September 29, 2010 6:36 AM
Points: 67,
Visits: 102
Hi,
Sorry to be a bit late joining the thread. I do quite a bit with excel and have found the best way to pick arbitrary cell values is to use a script task. I am using VB so I not sure of the syntax for C# for this.
I use set Option Strict Off, then define the variables I need for the access as objects (this will be latebinding so there aren't tool tips) then get a reference to Excel by using createobject("Excel.Application"). You can get help with code by writing a sub using the objects you want in the Excel VB Editor and pasting it in and modifying it. However you will need to use the full VBA expression to read from the cell as the default won't be recognised (i.e. sheet.range("A1").Value not sheet.range("A1")) but when writing to it the later will work.
This can be run in a try catch block for error handling. Usually in the catch block I use the code
If Not wb Is Nothing Then wb.Close(False) (wb being the workbook object). This will close the excel file without saving changes, objects will need to be disposed of properly too. The parameter false is needed to stop excel launching the Do you want to save changes? dialog box.
Another point, you will need to use the actual integer values for Excel Enumerations where needed as the enumeration won't be recognised in the script task using this method.
I use Script tasks to get and set specific cell values as well as to loop through each sheet in a workbook to save it as a csv file are then loaded using SSIS. This avoids the Excel ADO problem with columns containing mixed datatypes.
Post #916932
sburcombe
sburcombe
Posted Thursday, May 06, 2010 11:32 AM
SSCommitted
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:53 PM
Points: 1,615,
Visits: 253
This works brilliantly, thank you so much! It solved a very messy problem for me.
Simone
Simone
Post #917255
stuntwoman
stuntwoman
Posted Monday, August 30, 2010 9:20 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, March 20, 2012 3:57 AM
Points: 1,
Visits: 33
Hello
I just can't get this to work when I run the package as a job on the server. I don't seem to be able to add the COM references to the script component, I only get the option of .NET - is this the trouble?
Can anyone shed any light on this? The reason I am trying it out is I have to pick about 50 cells of data from all over a spreadsheet and this seems like a good method. it works locally anyway!
Post #977396
« Prev Topic
|
Next Topic »
28 posts, Page 3 of 3
««
«
1
2
3
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
