Skip to main content
Question

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


Forum|alt.badge.img

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.

8 replies

Forum|alt.badge.img
  • Author
  • June 5, 2019

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.


Forum|alt.badge.img
  • Author
  • June 5, 2019

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


david_r
Celebrity
  • June 5, 2019

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


nielsgerrits
VIP
Forum|alt.badge.img+54
david_r wrote:

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.


jkr_wrk
Influencer
Forum|alt.badge.img+29
  • June 5, 2019
si wrote:

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.


Forum|alt.badge.img
  • Author
  • June 5, 2019
david_r wrote:

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.


david_r
Celebrity
  • June 5, 2019

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.


jkr_wrk
Influencer
Forum|alt.badge.img+29
  • June 5, 2019
si wrote:

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.

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.

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings