Hitachi Vantara Pentaho Community Forums
Results 1 to 13 of 13

Thread: Excel Input - XLSX - POI can't find file in network

  1. #1
    Join Date
    Feb 2011
    Posts
    840

    Default Excel Input - XLSX - POI can't find file in network

    sooo... hello everyone, how are you all doing? After a brief time away from here, I come back with a huge problem - Excel Input, when set to use Apache POI for XLSX files, can't seem to find files in network. I'm using ${Internal.Transformation.Filename.Directory} and such other variables to set the filename on it - if I'm set on a XLSX file, I get something like this:

    Code:
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Error processing row from Excel file [file:////df5008fs201/GECAP/GECAP08_INTEGRACAO_RISCOS/Dados/CAPITAL/DLO/2013/2013-10/DRG 2013-10 2041.xlsx] : org.pentaho.di.core.exception.KettleException: 2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 - java.io.FileNotFoundException: file:\df5008fs201\GECAP\GECAP08_INTEGRACAO_RISCOS\Dados\CAPITAL\DLO\2013\2013-10\DRG 2013-10 2041.xlsx
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 - file:\df5008fs201\GECAP\GECAP08_INTEGRACAO_RISCOS\Dados\CAPITAL\DLO\2013\2013-10\DRG 2013-10 2041.xlsx
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleException: 
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 - java.io.FileNotFoundException: file:\df5008fs201\GECAP\GECAP08_INTEGRACAO_RISCOS\Dados\CAPITAL\DLO\2013\2013-10\DRG 2013-10 2041.xlsx
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 - file:\df5008fs201\GECAP\GECAP08_INTEGRACAO_RISCOS\Dados\CAPITAL\DLO\2013\2013-10\DRG 2013-10 2041.xlsx
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 - 
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 -     at org.pentaho.di.trans.steps.excelinput.poi.PoiWorkbook.<init>(PoiWorkbook.java:78)
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 -     at org.pentaho.di.trans.steps.excelinput.WorkbookFactory.getWorkbook(WorkbookFactory.java:38)
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 -     at org.pentaho.di.trans.steps.excelinput.ExcelInput.getRowFromWorkbooks(ExcelInput.java:603)
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 -     at org.pentaho.di.trans.steps.excelinput.ExcelInput.processRow(ExcelInput.java:471)
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 -     at java.lang.Thread.run(Unknown Source)
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 - Caused by: java.io.FileNotFoundException: file:\df5008fs201\GECAP\GECAP08_INTEGRACAO_RISCOS\Dados\CAPITAL\DLO\2013\2013-10\DRG 2013-10 2041.xlsx
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 -     at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:83)
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 -     at org.pentaho.di.trans.steps.excelinput.poi.PoiWorkbook.<init>(PoiWorkbook.java:70)
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 -     ... 5 more
    2014/01/07 17:15:09 - Input: RWACPAD-DRG.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
    From the poor knowledge I have on this, it seems POI's parsing the filename's path erroneously - changing \\ to \ and making it not look at the network. I've already tried replacing PDI 5's POI 3.9 with the 3.10 Beta2 but it also didn't work. Any ideas?

    Funny thing - on PDI 4.4 and POI 3.8, it works...
    Last edited by joao.ciocca; 01-07-2014 at 03:28 PM.
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    From what I recall, UNC paths have never been guaranteed to work.
    Map the UNC path to a drive letter, and it should work.

  3. #3
    Join Date
    Feb 2011
    Posts
    840

    Default

    that won't serve the needs... guess I'll just have to downgrade back to PDI4.4/POI3.8 - mapping the UNC path to a drive letter will work for dev, but not for the running process.
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  4. #4
    Join Date
    Apr 2013
    Posts
    15

    Default

    As a workaround, I use the "copy files.." step to copy the files located on the network to a temporary location. Use the $(java.io.tmpdir) variable to retrieve the location of temporary files.

  5. #5
    Join Date
    Feb 2011
    Posts
    840

    Default

    weird thing is: I only get this problem with reading XLSX - XLS reading through the network works just fine.
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  6. #6
    Join Date
    Feb 2013
    Posts
    1

    Default

    I have the exact same problem, xlsx files cannot be read from a network location, but xls files can be read from the exact same location (and path). Using PDI 5.0.1 on Win8 64-bit.

  7. #7
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Can you file a bug in jira, please?
    http://jira.pentaho.com
    -- Mick --

  8. #8
    Join Date
    Feb 2011
    Posts
    840

    Default

    http://jira.pentaho.com/browse/PDI-11273

    just pretend that that "latest tested" is a "last tested" ._. I didn't pay enough attention on reviewing before posting =p
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  9. #9

    Default

    Hello to all,

    I got the same issue.
    If I read the xlsx from a PC drive it works as expected : D:\EOS\CUSTOMERS_SK\Inventario sistemi Elisa.xlsx
    If I read the xlsx from a network location it doesn't work : //server03\EOS\Collaboratori\Elisa\Inventario OLD\Inventario sistemi Elisa.xlsx

    I think that a bug fixing is needed ...

    2018/04/23 12:25:09 - Spoon - Using legacy execution engine
    2018/04/23 12:25:09 - Spoon - Trasformazione aperta.
    2018/04/23 12:25:09 - Spoon - Esecuzione trasformazione [INVENTARIO]...
    2018/04/23 12:25:09 - Spoon - Esecuzione trasformazione avviata.
    2018/04/23 12:25:10 - Spoon - La trasformazione è terminata!
    2018/04/23 12:51:46 - Spoon - Salva come...
    2018/04/23 12:51:46 - Spoon - Save file as...
    2018/04/23 13:59:36 - D:\EOS\CUSTOMERS_SK\KETTLE_kjr\INVENTARIO_A2HOSTING.ktr : INVENTARIO_A2HOSTING - Spedizione iniziata per la trasformazione [D:\EOS\CUSTOMERS_SK\KETTLE_kjr\INVENTARIO_A2HOSTING.ktr : INVENTARIO_A2HOSTING]
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) : Errore nellelaborazione della riga dal file Excel [{0}]: {1}
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - java.io.FileNotFoundException: file:\server03\EOS\Collaboratori\Elisa\Inventario OLD\Inventario sistemi Elisa.xlsx
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - file:\server03\EOS\Collaboratori\Elisa\Inventario OLD\Inventario sistemi Elisa.xlsx
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 -
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - at org.pentaho.di.trans.steps.excelinput.poi.PoiWorkbook.<init>(PoiWorkbook.java:81)
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - at org.pentaho.di.trans.steps.excelinput.WorkbookFactory.getWorkbook(WorkbookFactory.java:41)
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - at org.pentaho.di.trans.steps.excelinput.ExcelInput.getRowFromWorkbooks(ExcelInput.java:553)
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - at org.pentaho.di.trans.steps.excelinput.ExcelInput.processRow(ExcelInput.java:431)
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - at java.lang.Thread.run(Thread.java:748)
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - Caused by: java.io.FileNotFoundException: file:\server03\EOS\Collaboratori\Elisa\Inventario OLD\Inventario sistemi Elisa.xlsx
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:246)
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:222)
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:201)
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - at org.pentaho.di.trans.steps.excelinput.poi.PoiWorkbook.<init>(PoiWorkbook.java:73)
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - ... 5 more
    2018/04/23 13:59:36 - INVENTARIO_XLS.0 - Elaborazione terminata (I=0, O=0, R=0, W=0, U=0, E=1)
    2018/04/23 13:59:36 - D:\EOS\CUSTOMERS_SK\KETTLE_kjr\INVENTARIO_A2HOSTING.ktr : INVENTARIO_A2HOSTING - Rilevata trasformazione
    2018/04/23 13:59:36 - D:\EOS\CUSTOMERS_SK\KETTLE_kjr\INVENTARIO_A2HOSTING.ktr : INVENTARIO_A2HOSTING - La trasformazione sta uccidendo gli altri passi!

  10. #10
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    If you read the bug that Joao filed, you will find that it is closed as "Not a bug"/"Won't Fix" as it was never a supported feature.

    There is a "Feature Request" that you can vote for if this is critical to your work method, but it is unlikely to go very far. https://jira.pentaho.com/browse/PDI-13193

  11. #11

    Default

    Sorry for not being a tech guy but if you give access to several users to the same trasformation how the named network drives work. In my case could be E:\......... for other users can be Y:......... Z:....... and so on. How do you fix this mess ? Is there any workaround ?

  12. #12
    Join Date
    May 2016
    Posts
    282

    Default

    With ftp (better sftp) instead of a network drive, you'll need to set up an ftp server to do that. Ask your system administrator to do that, surely there's something already set up.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  13. #13
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by Giovanni View Post
    Sorry for not being a tech guy but if you give access to several users to the same trasformation how the named network drives work. In my case could be E:\......... for other users can be Y:......... Z:....... and so on. How do you fix this mess ? Is there any workaround ?
    In the script that you are having them use to run the transformation, you specify what drive letter to map.
    Code:
    NET USE X: \\Server\Share
    kitchen -file:c:\path\to\job.kjb
    NET USE X: /DELETE

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.