none
Getting a null value when i am importing data from excel column having a different data type. RRS feed

  • Question

  • Hello All,

    I am getting a null value when i am importing data from excel column having a data in different data Type ex: if excel column containing a  "double-precision float [DT_R8]" type of data and in one of the cell value is like'xysddd', when i am pulling a data from  excel it is showing a null value in respective cell.

    I have changed the excel driver but still the same issues.

    After having a r&d we found that, ssis engine read first eight rows and based on data type SSIS engine decide the perticular column data type and if we are getting a different data type data in same column on 10th row, we will get null value in perticular cell.

    Can anyone resolve this issues if he/she had in past...Please let me know.

    Thanks

    Rahul

    Wednesday, February 8, 2012 7:18 AM

Answers

All replies

  • Rahul,

    Can you provide sample data?


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Wednesday, February 8, 2012 7:30 AM
  • Hi Rahul,

    Take a look at the following URLs. They might be useful to you:

    http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/54dc94c5-280b-41e5-82fe-26925f9eb95b/

    Best Regards,
    Datta
    ----------------------------------------------------------------------------------------------------
    Dattatrey Sindol
    My BlogDatta's Ramblings on Business Intelligence 'N' Life
    The information provided here is "AS IS" with no warranties, and confers no rights.
    Please mark the post as answered if it solves your problem.

    Wednesday, February 8, 2012 7:42 AM
  • Copy data and paste into a excel.

     Payment Other Charges Total Monthly Payment
    605.7   657.27
    877.96   1685.54
    849.64   1686.73
    565.24   749.44
    123.2   452.1
    512.14   839.54
    509.56   619.71
    828.55   1242.18
    538.1   844.73
    abc   xyz
    659.67   784.49

    thanks

    rahul

    Wednesday, February 8, 2012 8:05 AM
  • Hello Datta,

    I already went through with all and did not find any resolution.Problems arrived when input data type is "double-precision float [DT_R8]" and we are trying to change input datatype to unicode string, data type is not changing in input column.

    if the data type chane before 8 th row then it works but we dont know in later position....problem arrived after 8 th row.

    let me know if you required more detail.

    thanks

    rahul

    Wednesday, February 8, 2012 8:25 AM
  • Hello Datta,

    I already went through with all and did not find any resolution.Problems arrived when input data type is "double-precision float [DT_R8]" and we are trying to change input datatype to unicode string, data type is not changing in input column.

    if the data type chane before 8 th row then it works but we dont know in later position....problem arrived after 8 th row.

    The first article Datta linked to explicitly explains that you need to set the TypeGuessRows registry setting. If you set it to 0, it will scan all rows to determine the datatype.
    Which provider are you using? Jet or ACE?

    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...

    Wednesday, February 8, 2012 9:28 AM
  • Hello Koen,

    I am using a ACE --Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MICAFiles\BAC20122701.xlsx;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=1";

    Yes i went through with TypeGuessRows registry setting, As discussed with Production DBA we can't change the registry setting on Production box. Is there any way to resolve this issues?

    Please let me know ASAP.

    Thanks

    Rahul

    Wednesday, February 8, 2012 9:49 AM
  • There is another way:

    insert a dummy line in the beginning of the Excel file (as the 1st row) holding a string value for the column your struggling with.
    Hide this dummy line.

    In the SSIS package, you can get rid of this dummy line using a conditional split.

    edit: and tell your production DBA not to be so paranoid :)


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...


    Wednesday, February 8, 2012 9:53 AM
  • We can't edit excel file, in every hour we are receiving a feed in excel format from different sources and all filea are coming on a shared folder we need to use it as it is without doing any modification.

    Thanks

    Rahul

    Wednesday, February 8, 2012 10:06 AM
  • Hi Rahul,

    As Verbeeck said its better to insert a dummy line in the beginning of excel sheet so that it will use the string DataType while moving the data into database.

    Jai

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you


    Jai Shiva Naidu Verizon

    Wednesday, February 8, 2012 10:13 AM
  • We can't edit excel file, in every hour we are receiving a feed in excel format from different sources and all filea are coming on a shared folder we need to use it as it is without doing any modification.

    Thanks

    Rahul

    You don't have much choices. Either trick the JET/ACE provider with a dummy line or change the registry setting.
    Even custom scripting won't work, as .NET also uses those providers to access Excel files.

    What you can try is to convert the Excel file to a .CSV file and import the data as a flat file.


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...

    Wednesday, February 8, 2012 10:17 AM
  • Hi Rahul,

    You are also right if you are getting n number of files every time you cannot keep on changing the files every time,

    you can do one thing, you can use DataConversion tool and change the column to String and then push the data.


    Jai Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you


    Wednesday, February 8, 2012 10:55 AM
  • Hi Jai,

    I tried with data conversion trasnformation, but if we are getting a float data in excel, SSIS engine converts this data type to "double-precision float [DT_R8]" in external Input column and when we try to change, it could not change.

    The problem is SSIS engine which has default registry entry value is 8 rows...

    Rahul

    Wednesday, February 8, 2012 1:18 PM
  • The problem is SSIS engine which has default registry entry value is 8 rows...

    That is not the fault of the SSIS engine. It is the JET and ACE OLE DB providers who cause the problem. Its their registry values that you should update.

    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...

    Wednesday, February 8, 2012 1:21 PM
  • To be honest, the major issue here is not ssis OR the drivers - the issue is the data you are receiving - why on earth would you want to import "xyz" into a field that is dealing with "total monthly payments"?

    If it is some kind of heading or label for an aggregate line then ignore it and create the aggregate number in SSIS or the database

    If it is not some kind of heading for an aggregate line then what is it doing there and why do you need to import it?


    Rgds Geoff ----------------------------------------------------------- Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.


    Wednesday, February 8, 2012 11:53 PM
  • Hello Geoff,

    Actually we want to pull all data what ever is in excel and if we found any invalid data in any column based on business rules that row we will redirect to invalid data template and the same template will revert back to the concern team.

    Everyday we received the excel feed from different sources, my concern is if it is a typo mistake may by either from frontend or portal , that we need to track from ETL. It is also possible that in the front end application they dont have any validation that's why user is entering anything in respective column and we are getting in excel feed.

    But the issues which we are facing is, Excel source itself is not picking all data from excel in SSIS, and we are not setting any data type in excel sheet,we are using whatever we are receiveing.

    Let me know if you have any resolution.

    R'gds

    Rahul

    Thursday, February 9, 2012 8:15 AM
  • There is no proper resolution within your constraints.  You can't use the ACE driver to read these files reliably.  It will always interpret non-numeric data (regardless of "format" specified within the spreadsheet) as NULL when they appear in what it has determined to be a numeric column based on the first eight rows of data.

    Your only options as I see them are:

    • Prevail on your data sources to send you comma delimited files instead of spreadsheets.
    • Use the OpenXML API in a Script to read XSLX files instead of ACE/JET.
    • Try a third-party Excel file reader (see SSISCTC)

    None are particularly easy.


    Todd McDermid's Blog Talk to me now on

    Friday, February 10, 2012 12:28 AM
    Moderator
  • Another option is to convince the DBA to change the registry key, so that the ACE provider doesn't scan 8 rows, but the whole column.


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...

    Friday, February 10, 2012 6:20 AM
  • Hi Rahul,

    For your scenario, considering the constraints (like cannot change registry entry in production etc.), I think a better option would be to directly read the data from excel file using a Script Task in SSIS as you will be knowing the column names, format etc. of the data contained in the excel.


    Best Regards,
    Datta
    ----------------------------------------------------------------------------------------------------
    Dattatrey Sindol
    My BlogDatta's Ramblings on Business Intelligence 'N' Life
    The information provided here is "AS IS" with no warranties, and confers no rights.
    Please mark the post as answered if it solves your problem.

    Friday, February 10, 2012 9:52 AM
  • For your scenario, considering the constraints (like cannot change registry entry in production etc.), I think a better option would be to directly read the data from excel file using a Script Task in SSIS as you will be knowing the column names, format etc. of the data contained in the excel.

    Doesn't .NET also uses the JET/ACE provider to connect to Excel? If yes, the problem persists...

    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...

    Friday, February 10, 2012 10:12 AM
  • For your scenario, considering the constraints (like cannot change registry entry in production etc.), I think a better option would be to directly read the data from excel file using a Script Task in SSIS as you will be knowing the column names, format etc. of the data contained in the excel.

    Doesn't .NET also uses the JET/ACE provider to connect to Excel? If yes, the problem persists...

    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...


    That's correct. It's a driver specific problem...

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

    Friday, February 10, 2012 10:22 AM
    Moderator
  • Using a Script Task, you can use the COM API to read specific cells - it does get around the problem.  However, it presents an entirely new (unsolveable) problem - the COM API is not designed for server-side unattended use, so you're likely to get hangs and crashes as a result.

    Todd McDermid's Blog Talk to me now on

    • Proposed as answer by Eileen Zhao Tuesday, February 14, 2012 7:28 AM
    • Marked as answer by Eileen Zhao Thursday, February 16, 2012 6:44 AM
    Friday, February 10, 2012 6:24 PM
    Moderator