Morning,
Does anyone know if there is a way of taking monthly excel reports and adding them to a single google sheet with monthly tabs.
ie Monthly calls for each month are produced in excel - what we would like to do is when a new report is put into a centralised location fme server will detect a new spreadsheet and then the workspace would run and add the new month to the google sheet.
Thanks
Yes this should be doable.
FME supports creating tabs on existing google workbooks. You can set up FME server to use the Directory Watch (or if FME Server 2019+ then use automations) to watch for excel files.
Set your workspace up to read the excel file as a 'Single Merged Feature Type' and expose the 'fme_feature_type' format attribute - fme_feature_type is the sheet name for excel.
FME will read that in and then on the GoogleSheet writer you can set the sheet name (assuming that the sheet name of the input file is named by the month). If the month is in the excel file name then you can use the fme_basename attribute instead.
Note that Worksheet Handling must be set to 'Create If Needed'.
It might be worth adding in some kind of validation to check that the sheet doesn't already exist in order to prevent duplicates, however, this method could also be used if you have two files which have date from the same month - The first time the sheet would be created and then the second would simply inset new records below the existing data.
How cool is FME!
Yes this should be doable.
FME supports creating tabs on existing google workbooks. You can set up FME server to use the Directory Watch (or if FME Server 2019+ then use automations) to watch for excel files.
Set your workspace up to read the excel file as a 'Single Merged Feature Type' and expose the 'fme_feature_type' format attribute - fme_feature_type is the sheet name for excel.
FME will read that in and then on the GoogleSheet writer you can set the sheet name (assuming that the sheet name of the input file is named by the month). If the month is in the excel file name then you can use the fme_basename attribute instead.
Note that Worksheet Handling must be set to 'Create If Needed'.
It might be worth adding in some kind of validation to check that the sheet doesn't already exist in order to prevent duplicates, however, this method could also be used if you have two files which have date from the same month - The first time the sheet would be created and then the second would simply inset new records below the existing data.
How cool is FME!
Many thanks for the reply @virtualcitymatt - I have followed your instructions and set up the workspace below
the Path reader as follows
And the Google writer as below
It has out but to a google spreadsheet but only one and hasn't created a worksheet per tab with the data that is on the spreadsheet displayed which is ultimately what I would like to achieve
I suppose the files have to be split out as there is further manipulation of the spreadsheet that needs to be done as well but that can be done by testers and attribute managers
Thanks
Darren
Hi @darrenpage,
I think that it is not working for you because you are using the Directory and File Pathnames and the value of the fme_feature_type attribute resulting from this reader is probably PATH...
What happens if you use the Microsoft Excel reader? does that result in the correct value for the fme_feature_type attribute?
Hope this helps,
Itay
Hi @darrenpage,
I think that it is not working for you because you are using the Directory and File Pathnames and the value of the fme_feature_type attribute resulting from this reader is probably PATH...
What happens if you use the Microsoft Excel reader? does that result in the correct value for the fme_feature_type attribute?
Hope this helps,
Itay
@itay I have tried the excel reader but it is asking for a file in the dataset rather than a folder location.
@itay I have tried the excel reader but it is asking for a file in the dataset rather than a folder location.
Try the advanced reader properties:
Many thanks for the reply @virtualcitymatt - I have followed your instructions and set up the workspace below
the Path reader as follows
And the Google writer as below
It has out but to a google spreadsheet but only one and hasn't created a worksheet per tab with the data that is on the spreadsheet displayed which is ultimately what I would like to achieve
I suppose the files have to be split out as there is further manipulation of the spreadsheet that needs to be done as well but that can be done by testers and attribute managers
Thanks
Darren
Ah, it looks like you've used the PATH reader here instead of an Excel Reader. This is what in your output you simply have a list of filenames. You will actually need to read the data from within the excel files but you can still add all the files to the input to read all the data at once (as @itay has demonstrated).
Also I see that it looks like you want the tabs to match the files names? in that case use the 'fme_basename' attribute in place of fme_feature_type. The fme_basename attribute will hold the filename and if using this you will get one sheet with the filename as the tab name.
So when you run the workspace FME will read the data from the selected excel files into memory and then output the rows to new new googlesheet file. The data will be automatically grouped by filename (fme_basename) and placed on new sheets called with the filename as the name.
@virtualcitymatt and @italy Thank you so much for you help so - have never tried anything like before! I have selected the multiple folder/files option where the monthly reports are being held
then i am getting the following screen and then just adds multiple readers
@virtualcitymatt and @italy Thank you so much for you help so - have never tried anything like before! I have selected the multiple folder/files option where the monthly reports are being heldthen i am getting the following screen and then just adds multiple readers
You can still use the option of a Single Merged Feature Type with the multiple folders option as @virtualcitymatt suggested to avoid multiple feature types in your workspace.
You can still use the option of a Single Merged Feature Type with the multiple folders option as @virtualcitymatt suggested to avoid multiple feature types in your workspace.
@italy that has worked i now have a sheet per report but no data
@italy that has worked i now have a sheet per report but no data
Correct me if I am wrong but from the picture you posted I gather you are still using the Directory and File Pathnames reader and not the Excel reader....
@italy that has worked i now have a sheet per report but no data
Ahh looks like the output schema is still configured to get the attributes from the previous reader (when adding a writer by default FME will set the attribute schema to match that of the reader). The easiest thing is probably to delete the writer and re add it, alternatively you can double click on your output feature type, navigate to the 'User Attributes' tab. Here you should see a list of attributes from the old writer. You can change the schema from manual to automatic. This should update it to match the fields of your new excel input.
You can switch it back to manual to then delete attributes/columns which you don't need.
Ahh looks like the output schema is still configured to get the attributes from the previous reader (when adding a writer by default FME will set the attribute schema to match that of the reader). The easiest thing is probably to delete the writer and re add it, alternatively you can double click on your output feature type, navigate to the 'User Attributes' tab. Here you should see a list of attributes from the old writer. You can change the schema from manual to automatic. This should update it to match the fields of your new excel input.
You can switch it back to manual to then delete attributes/columns which you don't need.
@virtualcitymatt and @italy thank you for all your help today - it has finally produced an output on seperate sheets. Now to try and manipulate the date before it outputs to the sheet.
@virtualcitymatt and @italy thank you for all your help today - it has finally produced an output on seperate sheets. Now to try and manipulate the date before it outputs to the sheet.
Woo!