Autoscanning for excel files in folders to load to different SQL Server tables.

Ideally would want to move the file to a different folder once it has been processed.

Also it would be handy to record the date of the file and date of the load and put this in two fields of the sql server table.

I have some experience of FME workbench, but none of FME server.

Several options, but the simplest is probably to look into the Directory Watch Reader which is described here: https://knowledge.safe.com/questions/35862/directory-watch-reader.html

Another option is to use the Directory and File Pathnames reader to scan the directory and use something like the ChangeDetector to look for new files. You can save the list of current files to e.g an FFS between runs.

If you have FME Server then you should of course start by looking into the Directory Watch publisher: https://docs.safe.com/fme/html/FME_Server_Documentation/ReferenceManual/Directory_Watch_Publisher.htm

This. User FeatureReaders and FeatureWriters. Directory and File Pathnames Reader to scan a directory and expose the filedate. Excel reader to read the file. Write data to SQL Server. FileCopy Writer to move the processed file.

This is all possible. You can use a FeatureReader to read the Directory and File Pathnames format and select the directory you want to process. This reader is able to expose the file 'path_created_date'. Use an AttributeCreator to set the current date @DateTimeFormat(@DateTimeNow(),%Y%m%d)

Then use a FeatureWriter with 'File Copy' format to move the file after processing.

This is really helpful, I have setup a Directory and File Pathnames reader to read the filenames.

The problem I have is with the excel readers.

Because I do not know the names of the excel files, I cannot create EXCEL readers manually.

Am I missing a trick?

Because I do not know the names of the excel files, I do not know how to create a reader for them into FME ie (if I do not have the filename).

I want it to to scan all the file names.and create readers of a variety of excel files without having to setup a reader for each file one. Is this possible? Thanks for all the help.

You need to use a dynamic reader when reading Excel-files with an unknown schema.

When creating the writer make sure you select Single merged feature type:

Also make sure that the Feature Types to Read is blank (=all).

When reading the Excel files, you will find the Excel sheet names in the "fme_feature_type" attribute.

Use a FeatureReader with Format Excel, connect the output from the FeatureReader with Format Directory and File Pathnames to this input, and set the Filename parameter to the Path_Windows attribute, and set the Output Ports to Single Output port.

Or pick Output ports One per Featuretype and after clicking OK select a Data file that is an example of what you could expect.