Implementation of Timer in Excel Import Loop

Implementation of Timer in Excel Import Loop

Hi All,

I have a webscreen that allows me to browse for a csv file and initial importing that file into my database through an import loop. It works for the most part but times out if the number of rows in the csv file exceeds 2000. I need to upload a csv file with 120,000 rows. I've been told that I can accomplish this with a timer that allows the loop to upload 1000 at a time, a break, and then the importing of the next 1000 etc. Does anyone have any advice for me? I've never used this feature.

Hi Julius,

Yes, you are right about the way to do it. I have done it in the past, so let me try to share my advice - and take it as advice, not as best practices :)

1 - Create an entity with the following attributes:
      - FileContent (Text)
      - LineNumber (Integer)
      - FinishedProcessing (Boolean)

2 - When you upload the file, create a new record of that entity where:
      - FileContent is, well, the file content :)
      - LineNumber is 0
      - FinishedProcessing is false
and call the "WakeTimer" action.

3 - The timer action you have must do the following:
      - Query the database for records of that entity where FinishedProcessing = false;
      - Get the first one;
      - Start processing the FileContent from the line with number "LineNumber";
      - Process a predetermined amount of lines (this may either be a hardcoded number, or if you'd rather, a Site Property). Depending on the time it takes, I'd say that 1.000 is a good limit.
      - After processing that many lines, update the LineNumber with the NEW Value for LineNumber (i.e. the next line you need to process), and update the record;
      - If you have finished the file in the meanwhile, update the record with FinishedProcessing = True.
      - Call the WakeTime action.
      - End :)

and that's basically it, give or take some small details such as making sure that you're not importing the same records twice, or having a screen to check on the status of the importing process, but that's the gist of it!

I sure hope this helps.


Paulo Tavares