Reading Custom formatted data from Excel in SSIS

  • alexanderbebe

    SSC-Addicted

    Points: 407

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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

  • Phil Parkin

    SSC Guru

    Points: 244732

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • alexanderbebe

    SSC-Addicted

    Points: 407

    Thanks for the replies. I will make the changes in the registry and try it. Hopefully my admin will allow it:-)

  • Crispin Proctor

    SSCoach

    Points: 18161

    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!

  • alexanderbebe

    SSC-Addicted

    Points: 407

    Could you please elaborate the steps?. I am sorry for the trouble..i am not able to follow it...

  • Crispin Proctor

    SSCoach

    Points: 18161

    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!

  • alexanderbebe

    SSC-Addicted

    Points: 407

    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...

  • Crispin Proctor

    SSCoach

    Points: 18161

    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!

  • alexanderbebe

    SSC-Addicted

    Points: 407

    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"

  • Crispin Proctor

    SSCoach

    Points: 18161

    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,
    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!

  • Phil Parkin

    SSC Guru

    Points: 244732

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • SQLFanatic

    SSCommitted

    Points: 1640

    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"

  • Crispin Proctor

    SSCoach

    Points: 18161

    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,
    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!

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply