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

[joao.ciocca]

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:

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

[gutlez]

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

[joao.ciocca]

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.

[toms_]

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.

[joao.ciocca]

weird thing is: I only get this problem with reading XLSX - XLS reading through the network works just fine.

[kobush]

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.

[Mick_data]

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

[joao.ciocca]

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

[Giovanni]

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!

[gutlez]

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

[Giovanni]

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 ?

[Ana GH]

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

[gutlez]

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.

NET USE X: \\Server\Share
kitchen -file:c:\path\to\job.kjb
NET USE X: /DELETE