+4
Hello,
I have some shapefiles in a folder
Is there any way the workbench does select all files one by one and do the processing. The workbench that i have designed works fine for one shapefile and gives me the output in Excel in sheet 1.
Also i have created a workspacerunner.This is not giving me the desired output. I need output of all the shapefiles in excel in different sheets respectively.
Any help is much appreciated.
Best answer by gio
View original
+12
What you need is a second workspace with:
Directory and File path reader (to get a list of all the shape files). Make sure the filter is set to *.shp!
WorkspaceRunner calling the shown workspace, but set the source ESRI Shapefile to the attribute path_windows from the reader.
That should loop through all the Shape files.
Hope this helps.
+21
You can use a "fanout" on the Writer on fme_basename (or fme_dataset?) to create a single file per inputfilename - more information here https://knowledge.safe.com/articles/565/fanout-1.html
+4
What you need is a second workspace with:
Directory and File path reader (to get a list of all the shape files). Make sure the filter is set to *.shp!
WorkspaceRunner calling the shown workspace, but set the source ESRI Shapefile to the attribute path_windows from the reader.
That should loop through all the Shape files.
Hope this helps.
Hi @erik_jan, Thanks for the reply. By saying one more workspace, do you mean a total of 3 workbenches?
In 2nd workbench itself, it read the files.
Also, I am using a template file in 1st workbench.
I did what you suggested and now it is able to read the files but the output is still a single excel file with one sheet. The workbench should export the result from Workbench to one excel in 3 separate sheets if three files have been processed and likewise.
Could you please help in this regard?
Many Thanks.
+3
@mukhtarg
You want data from each shape read on its own sheet in a single xlsx, yes?
Then there is no need for a workspacerunner.
1 Read the shapes with a dynamic reader, this is a normal reader but with filter activated.
Point to your folder and add filter *.shp
Expose "fme_feature_type"
2 Proces.
3 Write with an xlsx in fanout mode.
In your image you should turn on the "Fanout dataset" and then use the fme_feature_type (this holds the shapfile name) as fanout attribute.
+4
@mukhtarg
You want data from each shape read on its own sheet in a single xlsx, yes?
Then there is no need for a workspacerunner.
1 Read the shapes with a dynamic reader, this is a normal reader but with filter activated.
Point to your folder and add filter *.shp
Expose "fme_feature_type"
2 Proces.
3 Write with an xlsx in fanout mode.
In your image you should turn on the "Fanout dataset" and then use the fme_feature_type (this holds the shapfile name) as fanout attribute.
@gio Thank you very much for the reply,
But the issue is that in the process I am using a pivot, which is pivoting all the shapefiles provided.
The requirement was to
1. read the single file then run pivot on it and produce the result in sheet1 of excel
2. read the second file then run pivot on it and produce the result in sheet2 of the same excel.
With what you suggested i am getting merged pivot result, which is fine but separate result is not possible.
Any help in this regard?
Thanks in advance.
+3
@gio Thank you very much for the reply,
But the issue is that in the process I am using a pivot, which is pivoting all the shapefiles provided.
The requirement was to
1. read the single file then run pivot on it and produce the result in sheet1 of excel
2. read the second file then run pivot on it and produce the result in sheet2 of the same excel.
With what you suggested i am getting merged pivot result, which is fine but separate result is not possible.
Any help in this regard?
Thanks in advance.
Pivoting is of no consequence to the process I described.
You need to expose "fme_feature_type" on the writers.
Because you process them file by file. Which is secured by the "fme_feature_type" .
If you write DATA dynamically, then all you need to do is activate it on the writer. This wil automatically fill the "sheet name"parameter with "fme_feature_type".
If you want ot write Summary dynamically you also need to pass the "fme_feature_type".
This latter you can do using a variable setter and variable retriever.
See image:
in image are 2 random shape files, their pivaot data will end up in separate sheets in same excel dataset.
I
+4
Pivoting is of no consequence to the process I described.
You need to expose "fme_feature_type" on the writers.
Because you process them file by file. Which is secured by the "fme_feature_type" .
If you write DATA dynamically, then all you need to do is activate it on the writer. This wil automatically fill the "sheet name"parameter with "fme_feature_type".
If you want ot write Summary dynamically you also need to pass the "fme_feature_type".
This latter you can do using a variable setter and variable retriever.
See image:
in image are 2 random shape files, their pivaot data will end up in separate sheets in same excel dataset.
I
@gio,
Would you please check the sample data attached herewith. As ia m not able to get through even after following what you suggested :(
Thanks in advance.
+3
Hi @mukhtarg
The zip file is empty.
In Workbench you can use file=>save as template to get the workbench and data in a archive.
But I see the problem I think.
Fan-out in the navigator panel on the xlsx writer, is a dataset fanout. This wil fan-out to excel-files (datasets).
The fan-out on the Writer it self (now adays wisely named "sheet name") is to fan-out to sheets.
You have them both set to "fme_feature_type"
This wil cause the wrtier to write a single sheet to a single excel file for every shapefile you read. Which is, in this case, not what you want.
If you want 1 dataset with a shapefile per sheet, you must clear the fan-out in the navigator.
See image. Remove (clear) the fan Expression in the Navigator panel (left side in image). Keep the highlighted one.
+4
Hi @mukhtarg
The zip file is empty.
In Workbench you can use file=>save as template to get the workbench and data in a archive.
But I see the problem I think.
Fan-out in the navigator panel on the xlsx writer, is a dataset fanout. This wil fan-out to excel-files (datasets).
The fan-out on the Writer it self (now adays wisely named "sheet name") is to fan-out to sheets.
You have them both set to "fme_feature_type"
This wil cause the wrtier to write a single sheet to a single excel file for every shapefile you read. Which is, in this case, not what you want.
If you want 1 dataset with a shapefile per sheet, you must clear the fan-out in the navigator.
See image. Remove (clear) the fan Expression in the Navigator panel (left side in image). Keep the highlighted one.
Hi @gio
I am sorry for the blank zip.
Please find it attached. I will check what you suggetsed, but still please try it at your end.
Pivot_Trial.zippivot.fmwt
+3
@mukhtarg
Hi,
I reviewed your workbench.
Apart form correcting the fan-out problem, I also added the schema reading and merging it to the data.
I removed the Variable setter (si not relevant here) and kept the retriever, to be able to write the Summary dynamically. If you remove it, it won't work...maybe safe could explain why this is.
You must set the warning on "don't warn me again" caused by the Variable retriever (as the variable is not set and or empty) if you intent to schedule it)
I did not manage (yet) tot get the template applied on the sheets though.
Maybe some of the kind Forum participators are willing to give pointers on template application.
The point being, just 1 template sheet for arbitrary sheets. Rather then having the same template sheet in Pivot_Mukhtarg.fmwtthe template file multiple times.
Latter I have done, but results are totally not what I expected. I have excluded it form the workbench I'm posting.
Pivot_Mukhtarg.fmwt
+3
@mukhtarg
Btw it also works like this.
You only need to have the schema reading on the canvas.
(still no template applied though..)
Essential are the correct settings for the relevant parameters.
Pivot_Mukhtarg_.fmwt
+3
@mukhtarg
I just found out that the application of the template seems still not working in the current versions.
I tried the script I posted out in FME(R) 2018.0.0.2 (20180414 - Build 18301 - WIN64) version. It works there.
I see now you have conditiponal coloring in your template, did not notice it before..
Wait for fix, or install older version paralelel...which then forces you to build to scripts as there is little back-wards compatibility.
Greets to all!
+4
@mukhtarg
Hi,
I reviewed your workbench.
Apart form correcting the fan-out problem, I also added the schema reading and merging it to the data.
I removed the Variable setter (si not relevant here) and kept the retriever, to be able to write the Summary dynamically. If you remove it, it won't work...maybe safe could explain why this is.
You must set the warning on "don't warn me again" caused by the Variable retriever (as the variable is not set and or empty) if you intent to schedule it)
I did not manage (yet) tot get the template applied on the sheets though.
Maybe some of the kind Forum participators are willing to give pointers on template application.
The point being, just 1 template sheet for arbitrary sheets. Rather then having the same template sheet in Pivot_Mukhtarg.fmwtthe template file multiple times.
Latter I have done, but results are totally not what I expected. I have excluded it form the workbench I'm posting.
Pivot_Mukhtarg.fmwt
@gio,
Thank you very much for your efforts. The output is exactly what I wanted, but still need to work on some process.
I am grateful for the help.
Thanks again :)
+11
@mukhtarg
I just found out that the application of the template seems still not working in the current versions.
I tried the script I posted out in FME(R) 2018.0.0.2 (20180414 - Build 18301 - WIN64) version. It works there.
I see now you have conditiponal coloring in your template, did not notice it before..
Wait for fix, or install older version paralelel...which then forces you to build to scripts as there is little back-wards compatibility.
Greets to all!
Hi @gio and @mukhtarg,
I suspect this is related to an issue filed (FMEENGINE-60964 or FMEENGINE-59830) where the template is being ignored unless the sheet being written has the same name. Tono and Debbi hash out some ideas for a limited workaround in this Forum question. I've added this Q&A to our filed issue and I'll let you know as soon as it has been resolved. Sorry for the inconvenience!
+4
@mukhtarg
Btw it also works like this.
You only need to have the schema reading on the canvas.
(still no template applied though..)
Essential are the correct settings for the relevant parameters.
Pivot_Mukhtarg_.fmwt
Hi @gio,
in the sample data i shared i need all the pivoted data to be populated. but in one of he filed QC Comment is not being populated.
I am trying at my side, but any help is still appreciated.
Thanks
+3
Hi @gio,
in the sample data i shared i need all the pivoted data to be populated. but in one of he filed QC Comment is not being populated.
I am trying at my side, but any help is still appreciated.
Thanks
@mukhtarg
Hi,
In your sample shape files you have QCResult with values "Missing" and with "Verify".
The corresponding QCcomment has no value in this sheet for these.
So after pivoting grouped by "UPDATE_REM QCResult QCcomment fme_feature_type" you end up with 1 QCREsult "Missing"and 1 "Verify", aggregated they have value "no value".
There is no value to populate them.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
