June 6, 2011 at 1:51 pm
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
June 7, 2011 at 7:00 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 7, 2011 at 7:36 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 8, 2011 at 12:27 am
Thanks for the replies. I will make the changes in the registry and try it. Hopefully my admin will allow it:-)
June 8, 2011 at 2:26 am
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,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 8, 2011 at 7:42 am
Could you please elaborate the steps?. I am sorry for the trouble..i am not able to follow it...
June 8, 2011 at 7:50 am
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,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 8, 2011 at 8:40 am
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...
June 8, 2011 at 8:57 am
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,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 9, 2011 at 12:57 am
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"
June 9, 2011 at 2:00 am
hmm, lost me then. A tickle on google seems to suggest it's corrupt "DB" files. I am assuming you can still open excel.
Try applying jet SP4?
http://support.microsoft.com/kb/829558
Important Jet 4.0 SP8 is not the latest update for the Jet 4.0 Database Engine when you are using Microsoft Windows 2000, Windows XP, or Windows Server 2003. However, the downloads for Jet 4.0 SP8 are still available.
Can't hurt.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 9, 2011 at 2:13 am
alexanderbebe (6/9/2011)
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"
Do not do this. You cannot create an xlsx file simply by renaming an xls file.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 9, 2011 at 1:21 pm
Crispin Proctor (6/8/2011)
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)
Hi Crispin, what is the function of HDR? I have used HDR = 'Yes' in similar cases.
Ususally, i would open the excel file and scan through the column, also format cell as "Text" and then viewing the first 200 rows in the preview window pretty much gives me an idea about what is going to be loaded into the table.
But, in this case he doesn't have that privelage to open an excel file. Hopefully, the registry setting update will work.
🙂
________________________________________________________________
"The greatest ignorance is being proud of your learning"
June 10, 2011 at 2:36 am
as an aside, I've not had much luck with changing the reg setting so that it scans more than 8 (7 if you have a header). I found the tasks really slow to start (guessing it was scanning the file) and then annoying in its "Oh, look Mr Creator, the column has changed".
Seriously, let me tell you what the column is and you do your best to give it to me. Try not guess....
/rant.
As for the header things, if the first row of your file is not a header row, this causes the columns to return as F1, F2 etc.
The files I am working with have the first 9 (:crazy:) rows as a summary of the file so column names where all over the place.
as another aside, if you cannot open the file on the server due to lack of permission or lack of excel, why not create a linked server to it. Will allow you to have a browse.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply