<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
»
SQL Server 2005
»
SQL Server 2005 Integration Services
»
Reading Custom formatted data from Excel in...
14 posts, Page 1 of 2
1
2
»»
Reading Custom formatted data from Excel in SSIS
Rate Topic
Display Mode
Topic Options
Author
Message
alexanderbebe
alexanderbebe
Posted Monday, June 06, 2011 1:51 PM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:11 AM
Points: 5,
Visits: 332
I have an excel file which contains first ten rows with information about that excel and the actual data from 11th row.In the Actual Data i have few columns with Custom formatting (#,##). My problem is when i read the data from excel using SSIS, i am not able to get the decimal values. For example, one row i have actual value as 1.009876 but the formatted value shows it as 1. I need to retreive the complete decimal value.
I am using IMEX=1. Since excel is not installed in my db server, i cant use microsoft.office.interop.excel. any suggestions will be highly appreciated
Post #1120570
Koen Verbeeck
Koen Verbeeck
Posted Tuesday, June 07, 2011 7:00 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 6:54 AM
Points: 9,364,
Visits: 6,462
Besides using the connectionstring property IMEX=1, you should also set the registry setting of TypeGuessRows for the JET provider to 0.
The JET provider guesses the data type using the first 8 rows. If they are mixed, the string data type is chosen (that's what IMEX is for). With the registry setting to 0, you tell the JET provider to scan the whole column instead of the first 8 rows.
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 #1120960
Phil Parkin
Phil Parkin
Posted Tuesday, June 07, 2011 7:36 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 5:12 AM
Points: 4,226,
Visits: 9,458
Note also that custom formatting in Excel has no effect on the data which is stored or extracted - it is a display format effective only in the Excel application.
____________________________________________________________________________________________
Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1121008
alexanderbebe
alexanderbebe
Posted Wednesday, June 08, 2011 12:27 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:11 AM
Points: 5,
Visits: 332
Thanks for the replies. I will make the changes in the registry and try it. Hopefully my admin will allow it
Post #1121535
Crispin Proctor
Crispin Proctor
Posted Wednesday, June 08, 2011 2:26 AM
SSC Eights!
Group: General Forum Members
Last Login: Tuesday, April 16, 2013 8:31 AM
Points: 917,
Visits: 399
Seeing as I am back in the painful world of Excel....
Try using a OLE connection to the file, add "EXCEL 8.0;IMEX=1;HDR=No" in the extended properties and select from the connection using a SQL connector.
All your outputs will be Nvarchar but the full values.
Depending on how "clean" your data is, Excel might be kind and give back all your rows. if it is feeling like an @rse, it'll keep giving you grief...
I'm in the grief stage ATM.
But as Phill said - front end formatting is just that, formatting...
HTH
CP
Cheers,
Crispin
I can't die, there are too many people who still have to meet me!
It's not a bug, SQL just misunderstood me!
Post #1121566
alexanderbebe
alexanderbebe
Posted Wednesday, June 08, 2011 7:42 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:11 AM
Points: 5,
Visits: 332
Could you please elaborate the steps?. I am sorry for the trouble..i am not able to follow it...
Post #1121761
Crispin Proctor
Crispin Proctor
Posted Wednesday, June 08, 2011 7:50 AM
SSC Eights!
Group: General Forum Members
Last Login: Tuesday, April 16, 2013 8:31 AM
Points: 917,
Visits: 399
Sure,
1) Create a OLE DB connection.
2) Provider is Microsoft JET (note, this will only work on x86 machine or package running as x86)
3) Set filename to be full path to xls file.
4) user and pass remain default (are actually ignored and cannot be used for xls)
5) Click on All, scroll up to Extended Properties. paste in options I mentioned.
6) Close
7) In data flow, create new OLE DB Source
8) Choose the connection you've just created.
9) Select tables to see what sheets you have exposed.
10) Change from table to SQL query. Select F1, F2, F3 etc from [myworkbook$]
11) Click ok.
12) Add rest of components (a union all as a consumer to test with)
13 Run
Cheers,
Crispin
I can't die, there are too many people who still have to meet me!
It's not a bug, SQL just misunderstood me!
Post #1121767
alexanderbebe
alexanderbebe
Posted Wednesday, June 08, 2011 8:40 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:11 AM
Points: 5,
Visits: 332
I tried it. i have oledb connection with the provider (native oledb\microsoft jet 4.0 oledb provider). i have mentioned the complete path of the xls file. but i am getting the error saying "unrecogized databae format". am i missing something...
Post #1121804
Crispin Proctor
Crispin Proctor
Posted Wednesday, June 08, 2011 8:57 AM
SSC Eights!
Group: General Forum Members
Last Login: Tuesday, April 16, 2013 8:31 AM
Points: 917,
Visits: 399
Is the file and xls (i.e. not excel 2007/2010) file?
have you put "EXCEL 8.0;IMEX=1;HDR=No" in the Extended Properties? (No quotes)
Cheers,
Crispin
I can't die, there are too many people who still have to meet me!
It's not a bug, SQL just misunderstood me!
Post #1121824
alexanderbebe
alexanderbebe
Posted Thursday, June 09, 2011 12:57 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:11 AM
Points: 5,
Visits: 332
it is 2003 excel file. yes i gave without quotes.
Later, i tried by renaming the excel file from xls to xlsx but i am getting the error as "Could not find installable ISAM"
Post #1122231
« Prev Topic
|
Next Topic »
14 posts, Page 1 of 2
1
2
»»
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.
