Solved

Importing multiple files from a remote FTP location and running import scenario

  • 29 October 2021
  • 17 replies
  • 1291 views

Userlevel 2
Badge

I’m working on a new project and I could use some help with the steps I need to complete.  I’m trying to automate the importing of data from an external source on a nightly basis.  Here is what I have:

  1. External system exports multiple files to an FTP location on a nightly basis.
  2. I configured a data provider to connect and sync to the remote FTP location.
  3. I wrote an import scenario to import the external file.

Here is my problem.  I can successfully run the import scenario with a single file, but how do I do it for multiple?  When I perform the “File Synchronization", I end up as multiple versions (one for each new file).  How do I configure Acumatica to execute my IS nightly and to import all the new files it finds?  

icon

Best answer by PRapnikas 29 November 2021, 16:53

View original

17 replies

Userlevel 1
Badge

I have the same problem.  Although multiple files will sync from FTP with different time stamps, only the latest version is picked up by the prepare. Previous unprocessed files are not processed. Must be missing something?

 

Userlevel 7
Badge +9

Hi @PRapnikas For automating the Import scenario on nightly basis, try using the Process Import Scenarios screen.

 

Thanks

Userlevel 7
Badge +9

Hi @PRapnikas Please find the below screenshot showing the scheduling options. The “Name” condition be assigned with your import scenario. So, when the process runs automatically, it will process only the specified Import Scenario.

 

Userlevel 7
Badge +9

Hi @PRapnikas Please find the below additional info for reference on the configurable parameters for nightly scheduling. 

 

Userlevel 3
Badge

Here is my problem.  I can successfully run the import scenario with a single file, but how do I do it for multiple?  When I perform the “File Synchronization", I end up as multiple versions (one for each new file).  How do I configure Acumatica to execute my IS nightly and to import all the new files it finds?  

@PRapnikas - I understand your problem. The testing and research I have done shows that it does not support processing “multiple revisions that have not been imported”.

There are multiple functional gaps in the import + file sync functionality. It’s not usable for business critical automation of data imports like importing orders from external systems.

Import Scenario + File Sync functional gaps:

  1. A scheduled import scenario with file sync enabled does not trigger the file sync. It needs to be scheduled separately. Since the file sync needs to run before the import… humans need to manage the automation schedules to ensure they execute in the right order.
  2. No out of the box error notifications if scheduled import fails. It’s doable but needs to be constructed using GI’s against the Process Import Scenarios DAC, with a business event.
  3. No error notifications if the file sync fails. If this happens, then the import scenario will still run and re-import the same revision from last time.
  4. If the file sync (network share or ftp) syncs multiple files as revisions, only the most current revision is imported on the next import.
  5. There is no indication of which file revisions have been prepared, imported, and/or had errors. 

In its current state I would look at using external etl/automation tools to solve this.

-Chad

Userlevel 1
Badge

Thanks so much for this confirmation and analysis. 

Userlevel 2
Badge

I spent some more time testing this and was able to find a workable solution.  Thanks for the help guiding me in the right direction.  Here is what I did.

  1. Created an FTP File Sync that synchronizes to a remote FTP location with multiple files.  I have the checkbox set to "Synchronize Folder Content" so it grabs all the new files.
  2. Using the direction above, I created a File Sync schedule to sync up my files with the latest versions.  
  3. Created my Import Scenario like I normally would using the Data Provider I created above.  However, in the "Sync Type" field, change to "Incremental - New Only" or "Incremental - All Records".  My FTP location only contains the latest version of the files, so either works for me.  You might need to test the different types to see how it works with your data.
  4. Create an Automation schedule for your Import Scenario.  It should perform both Prepare and Import together.

This gets me most of what I need.  First, it syncs my data files and loads into Acumatica.  Next, it performs a series of "Prepare and Import".  In your IS, if you set the "Sync Type" to "Incremental - New Only" or "Incremental - All Records", it will execute the Import Scenario for EACH version of the file you synchronized.  

What it doesn't give me is error checking or reporting (so far).  If I have 10 files to import, it first prepares and imports the first "new" file from the revision list.  If there are no problems and it completes the import, the next time the automation job executes the IS, it will prepare and import the next revision in the list.  However, if there is a problem, it stops there.  Every time the IS executes, it will try and prepare and import the same file over and over.  It will not move on to the next revision.

There is a manual work around.  I can open my Import by Scenario and click the "Prepare" button.  If all my revisions completed, I will get a message that there are no new revisions to process.  This tells me that everything is good and I don't need to do anything.  If I Prepare and get a new list of data, I can then "Import" the data.  Expect an error because if it there was none, it would have moved on to the next revision.  Just fix the error(s) and complete the import.  Once you have successfully completed that revision, you don't have to manually execute for the remaining revisions, your Automation Schedule (if it's still active and running) will pick up the next revision and prepare and import it.

It would be nice if I could get an email or something telling me that an IS didn't complete so I can investigate.  For now, I just go to each of my "Import by Scenario" jobs and click the "Prepare" button.  If I get an error message that there are no revisions to import, I know it's completed and I don't need to do anything.

I am on Acumatica Cloud ERP 2021 R1, Build 21.110.0032.

Userlevel 3
Badge

Thank you PRapnikas!

I’ve been trying to get this running for importing orders from our sites; sent the below to our Acumatica partner last week but haven’t heard back yet.  Looks like you’ve solved my problem #1 below with the Sync Type.  I’ll give that a try!

 

Implementing a way to import purchase orders without changing out our superintendents work.

We’re going to skip Requests & Requisitions & so far we’ve:

  1. Modified their online .xlsx order form to produce a sheet importable by Acumatica
  2. Created Import Purchase Orders scenario
  3. SFTP external folder mapped to local drive here (to place the .xlsx orders when received)
  4. Setup SFTP synchronization to the above folder under File Maintenance for the file attached to the import scenario
  5. Created File Synchronization Automation Schedule (PO File Sync)
  6. Created Process Import Scenario Automation Schedule (Import PO Schedule) - for 1 min. after step 5 above

Thought process is that our Purchasing manager receives the .xlsx order from the Superintendent, reviews and if acceptable just copies it into a folder & the purchase order would be created automatically.

 

There’s a few issues I haven’t sorted out and would like some direction.

  1. If more than one file is placed in the folder at the same time, I believe the file sync uploads them all, but due to versioning only the latest is processed by the import scenario.
  2. If there are no new files sync’d then the import scenario continues to import the existing PO creating duplicates (currently deactivated so a new PO isn’t created every 2 min).
Userlevel 2
Badge

In the FTP sync, it seems that Acumatica is tracking the file date/time stamps and will not load it more than once.  I have remote directories with multiple files generated daily.  Also, with the change in “Sync Type”, your IS should Prepare and Import each version once until completed.  So, if no new files are found, it should not duplicate your PO’s.  If you’re experiencing something different, let me know and I can help determine why it might be doing that.

Userlevel 3
Badge

@PRapnikas 

Thanks for the super detailed update! I poked at that same exact setup for multiple hours - and could not get it to work “consistently” - but the way you explained it would account for the inconsistent behaviors I was seeing. I’m going to revisit this!

I’m curious as to “where” Acumatica is storing what revisions have been imported/processed - I will look at this. 

Import Scenario + File Sync functional gaps:

  1. A scheduled import scenario with file sync enabled does not trigger the file sync. It needs to be scheduled separately. Since the file sync needs to run before the import… humans need to manage the automation schedules to ensure they execute in the right order.
  2. No out of the box error notifications if scheduled import fails. It’s doable but needs to be constructed using GI’s against the Process Import Scenarios DAC, with a business event.
  3. No error notifications if the file sync fails. If this happens, then the import scenario will still run and re-import the same revision from last time.
  4. If the file sync (network share or ftp) syncs multiple files as revisions, only the most current revision is imported on the next import.
  5. There is no indication of which file revisions have been prepared, imported, and/or had errors. 

ALSO - based on feedback from @PRapnikas - #4 above is not correct! Which was one of the major gaps. Will research #5 further - code must be tracking that somewhere.

-Chad

Userlevel 2
Badge

For #5, I believe you can see what has been prepared and imported.  Go to the specific Import by Scenario and then look at the HISTORY tab.  It lists the Version it is preparing/processing.  From that screen, you can also click on the FILES link at the top to open the files and versions associated with that IS/Data Provider.

Userlevel 3
Badge

@PRapnikas OMG - I’ve been staring at that data for a year. Thank you.

Using a combo of that data and unprocessed rows - a business event could probably be built for email notifications.

Userlevel 1
Badge

The “Versions” tab on the File Maintenance screen seems to have vanished recently. I have queried this with our partner. So I found the table UpdateFileRevision and a Generic Enquiry joining Sales Orders created by the Import Scenario (Customer order nbr is in the original file name) at least checks that all the versions were imported. 
 

 

Userlevel 3
Badge

Believe I have this sorted out for what our needs are.

Import Scenario settings:

 

The file used is an empty PO simply with the structure.

File Maintenance with the SFTP with Synchronize Folder Content & an Import File Reg. Expression.

File Synchronization Automation Schedule set to start at 6am end at 6pm, process every 60 minutes (with no Conditions).

Automation Schedules for Import PO Schedule set for the same hours as above but to process every 2 minutes.

All new files in the SFTP will be retrieved every 60 minutes.  The Import Purchase Orders scenario will process 1 file at a time every 2 minutes.
As long as our order load doesn’t exceed 30 PO’s an hour the process will be able to keep up. 

If there are no new files the Import PO Schedule processes with an error of “All revisions of this file have been processed.”

I had an issue before where the scenario would continue importing the last PO creating the duplicates, but I think the Sync Type above set to Incremental solved that.

 

Thanks @PRapnikas and all others commenting here.  Super helpful!  This lets us keep our order forms the same (not making old dogs on-site learn new tricks) and we were quoted 30k to customize Acumatica to mirror our current PO structure.  So this saved us that 30k, plus the purchasing manager not having to approve requests, create requisitions and then finally the PO; we just import POs !

 

Userlevel 3
Badge

I am trying to get this same scenario to work. I am having no luck getting multiple files to sync. I tells me the Last Import date but the files are not updating. I am assuming I would see them in the Versions tab? Unless these would be Imported elsewhere.

 

I can do a single file with no issue, just cannot do a full folder of files…

 

If anyone has any input or suggestions please let me know. Thank you so much.

Userlevel 3
Badge

I am trying to get this same scenario to work. I am having no luck getting multiple files to sync. I tells me the Last Import date but the files are not updating. I am assuming I would see them in the Versions tab? Unless these would be Imported elsewhere.

 

I can do a single file with no issue, just cannot do a full folder of files…

 

If anyone has any input or suggestions please let me know. Thank you so much.

 

Do you have the Import File Reg Exp set?  We have it as a prefix, so any file that shows up in the ftp with it’s name starting as such gets imported:

 

 

And yes, it displays on the Versions tab:

 

 

Userlevel 2
Badge

Yes, first confirm if you are loading all the files from your FTP source.  Go to your File Synchronization screen and find your file source.  Then click the link to get a list of your versions.  Make sure have the checkbox set for “Synchronize Folder Content”.  When I sync, I grab all the files in the folder, so I do not have a Reg. Exp.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved