Is there a way to autoscan for new excel files in a folder and then load them into SQL Server tables depending on the name.
Question
Autoscanning for excel files in folders to load to different SQL Server tables.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
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.
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
+64
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.
+36
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.
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.
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 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.
+36This 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.
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.
Now you have to Expose the Attributes that you need from the excel manualy with an AttributeExposer because FME does not know what data is in the Excel file on forehand.
Or pick Output ports One per Featuretype and after clicking OK select a Data file that is an example of what you could expect.