Importing a remote CSV file

Discussion created by DavidZakary on Aug 6, 2013
Latest reply on Aug 6, 2013 by BowdenData

A client needs the ability to import remote CSV files. The files are automatically generated and stored on a 3rd-party providers server. We can easily calculate the name of the CSV file as it is based on the date. A new file gets generated every day. Each file contains 40,000 to 45,000 rows and about 7 columns of data. This needs to come into FM 12.


We've been working on building an automated system that would run as a server script every day.


The current system, calculates the name of the CSV file and does an insert from URL into a single field. We then parse through the rows of the CSV and put the values into a new record. Works pretty well, except that it is very slow - each day's records takes about 45 minutes to process. Well beyond the limits set for the hosting company's server script time out.


We'l like to avoid using a plugin to grab the CSV if possible and we can't do a direct import into the destination table from the remote server.


Any suggestions as to how to speed things up? My next thought is to get the CSV into a field (already there), export it as CSV to the server and then do a regular import. Just haven't had the time to try it yet. Wondering if others have found a magic bullet to make things faster.


BTW, the client is WAY behind on thier daily imports. They probably have 45-60 days to catch up on. So getting this to work as quickly as possible would be handy.


Dave Zakary