Uploaded image for project: 'Pentaho Data Integration - Kettle'
  1. Pentaho Data Integration - Kettle
  2. PDI-19300

Variable environment substitution is not working for password field for Excel Input step

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Severity: High
    • Resolution: Unresolved
    • Affects Version/s: 8.3.0 GA, 9.1.0 GA
    • Fix Version/s: Backlog
    • Component/s: Step
    • Labels:
      None
    • Story Points:
      0
    • PDI Sub-component:
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
    • Steps to Reproduce:
      Hide
      1. Download attached MS Excel SampleWithPassword.xlsx
      2. MS Excel file is password protected with: pentaho123
      3. Open attached transformation on Spoon: ExcelWithTypedPassword.ktr
      4. Notice under Transformation properties the EXCELPASSWORD variable is been set to the value of pentaho123
      5. Run the transformation and notice the error
      Show
      Download attached MS Excel SampleWithPassword.xlsx MS Excel file is password protected with: pentaho123 Open attached transformation on Spoon: ExcelWithTypedPassword.ktr Notice under Transformation properties the EXCELPASSWORD variable is been set to the value of pentaho123 Run the transformation and notice the error

      Description

      When users try to use a transformation parameter in the password field of the Microsoft Excel input step an error is thrown in the UI as the variable's value is not accepted. The error shown is here:

      2021/08/31 11:07:11 - ExcelWithTypedPassword - Dispatching started for transformation [ExcelWithTypedPassword]
      2021/08/31 11:07:11 - org.pentaho.di.trans.steps.excelinput.poi.PoiWorkbook@172718d8 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Unable to open spreadsheet.  If the spreadsheet is password protected please double check the password is correct.
      2021/08/31 11:07:11 - Microsoft Excel input.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Error processing row from Excel file [C:\Users\carlopez\Desktop\SampleWithPassword.xlsx] : org.pentaho.di.core.exception.KettleException: 
      2021/08/31 11:07:11 - Microsoft Excel input.0 - Password incorrect
      2021/08/31 11:07:11 - Microsoft Excel input.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : org.pentaho.di.core.exception.KettleException: 
      2021/08/31 11:07:11 - Microsoft Excel input.0 - Password incorrect
      2021/08/31 11:07:11 - Microsoft Excel input.0 - 
      2021/08/31 11:07:11 - Microsoft Excel input.0 - 	at org.pentaho.di.trans.steps.excelinput.poi.PoiWorkbook.<init>(PoiWorkbook.java:87)
      2021/08/31 11:07:11 - Microsoft Excel input.0 - 	at org.pentaho.di.trans.steps.excelinput.WorkbookFactory.getWorkbook(WorkbookFactory.java:46)
      2021/08/31 11:07:11 - Microsoft Excel input.0 - 	at org.pentaho.di.trans.steps.excelinput.ExcelInput.getRowFromWorkbooks(ExcelInput.java:546)
      2021/08/31 11:07:11 - Microsoft Excel input.0 - 	at org.pentaho.di.trans.steps.excelinput.ExcelInput.processRow(ExcelInput.java:426)
      2021/08/31 11:07:11 - Microsoft Excel input.0 - 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
      2021/08/31 11:07:11 - Microsoft Excel input.0 - 	at java.lang.Thread.run(Thread.java:748)
      2021/08/31 11:07:11 - Microsoft Excel input.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
      2021/08/31 11:07:11 - ExcelWithTypedPassword - Transformation detected one or more steps with errors.
      2021/08/31 11:07:11 - ExcelWithTypedPassword - Transformation is killing the other steps!
      2021/08/31 11:07:11 - ExcelWithTypedPassword - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Errors detected!
      2021/08/31 11:07:11 - Spoon - The transformation has finished!!
      2021/08/31 11:07:11 - ExcelWithTypedPassword - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Errors detected!
      2021/08/31 11:07:11 - ExcelWithTypedPassword - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Errors detected!
      

        Attachments

        1. error.png
          error.png
          123 kB
        2. excelinput - password.png
          excelinput - password.png
          44 kB
        3. ExcelWithTypedPassword.ktr
          15 kB
        4. PDI-19300.PNG
          PDI-19300.PNG
          35 kB
        5. SampleWithPassword.xlsx
          17 kB
        6. tab parameters.png
          tab parameters.png
          34 kB

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            Makko Makko
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: