7 Replies Latest reply on Aug 6, 2013 2:48 PM by BowdenData

    Importing a remote CSV file

    DavidZakary

      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

        • 1. Re: Importing a remote CSV file
          mikebeargie

          by "parse" do you mean you're reading out of the raw CSV file?

           

          Are you sticking the CSV file into a text field?

           

          What if you used Insert from URL to stick the file in a container field, then exported the CSV file to a temporary path, and immediately imported that file?

           

          I'd imagine you need to import instead of "parse" for speed.

           

          Also, plenty of error checking required with the above approach, just in case the files are not there.

          • 2. Re: Importing a remote CSV file
            DavidZakary

            When we do the parsing of the CSV, we're taking the individual fields in the row and putting them into separate fields.

             

            The CSV is getting placed into a field via the Insert from URL script step. That works filne.

             

            When I export the text back to a CSV file and try to import it, the carriage rerturns aren't recognized and the entire thing wants to come in as a single row.

             

            The remote service that generates the CSV is rock solid. We error check for the file but its never been an issue.

             

            We want to get away from the parsing method as it takes way too long. Importing will be pretty quick, we just can't get the file into a format that we can use. We're stuck with the remotely generated CSV fomat - that won't change.

            • 3. Re: Importing a remote CSV file
              mikebeargie

              Parsing a 45,000 record loop is going to be slow. I'm sure you've already exhausted some optimization steps like "freeze window". An export and import I'd imagine would be much faster. It should be easy to script after what you've already done:

               

              Set Variable $path = "file:" & get(temporarypath) & nameOfFile.csv

              Export Field Contents table::csvStorageField to $path

              Import File $path

               

              You will just need to specify the import order.

               

              I would think the real issue is why would a CSV file change format just by exporting?

              • 4. Re: Importing a remote CSV file
                DavidZakary

                That's the $64,000 question. Tried multipe encoding formats with zero luck so far.

                 

                The import/export stuff is easy - that works fine. Its just the format of the exported file that isn't working. We've done the Insert From URL as well as loading the CSV into a web viewer and grabbing that. If we could just do an import from an http source that'd be great.

                • 5. Re: Importing a remote CSV file
                  ch0c0halic

                  David Zakary may be parting with a substantial amount of money ($64,000),

                   

                  Use Export field Content, not Export Records.

                   

                   

                  When exporting 'records' the return is the record delimiter so a return inside a field is not allowed in the exported content.

                  • 6. Re: Importing a remote CSV file
                    DavidZakary

                    Thanks Jimmy. That solved the missing carriage return issue. The remaining issue is that we'd like this to be a server-based, scheduled script. The Export Field Contents isn't server friendly.

                     

                    I guess we'll set up a robot machine or something similar to do the import.

                    • 7. Re: Importing a remote CSV file
                      BowdenData

                      David,

                       

                      Try putting your csv file in a field in a table that only has 1 record in it. Then you can do a regular export records on the FM server. A possible alternative would be to put the csv file in a global field and then export.

                       

                      Not sure if this will work in your situation, but think that it will.

                       

                      HTH

                       

                      Doug