Monthly excel reports to single google sheet

Yes this should be doable.

How cool is FME!

virtualcitymatt wrote:

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

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.

Try the advanced reader properties:

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

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

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

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.

@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!