Each month, I import a csv product data file consisting of several hundred lines. (The line count varies each month.) The import takes several minutes; therefore, I’m investigating whether this can be migrated to the server. Question: Is it practical to import the csv file to a single field, then loop through those lines and create individual records?
Manually parsing the data line by line from a field is not something that I would absolutely rule out as an option, but it is pretty far down on the list of what I might reach for.
If import speed is the only issue
and there is no need for customization/massaging of the data as part of the import process, then I would consider moving the import to server. I would also investigate/experiment to see if I could determine if anything is unnecessarily slowing down the import, e.g., unused indexing, or auto-enter calcs, etc.. **
If there is a need to massage/customize the data,
then my MO has always been to use a two stage process:
- First, import the file into a temp/import-staging table
- Then, traverse records in the staging table to create the real data records.
Both the two-stage approach and directly parsing the data file require looping with record creation.
I avoid the line-parsing approach because
I feel that doing so results in code that I could more confidently hand off to someone else to maintain. Avoiding coding the parsing also obviates the need for familiarity with CSV format rules related to things such as the handling of double-quote and newline chars.
Another thought on import speed:
** Another reason why imports can slow down is if it is an "import-matching" type of update where FMP has to look at existing records in the table for matching data that needs to be replaced/updated. These types of imports can slow down over time as the table size grows.
It's been a while since I have worked on strategizing about these types of imports, but I seem to recall that one approach I used was to try to leverage any available business knowledge to see if I could reduce the found set of existing records prior to performing the import with matching. This is idea stems from this behavior:
When the target file contains a found set, only the found records are updated.
In other words, if I know that I can identify records in the table which are certain to not be updated, then I start with a found set which excludes those records, so as to give FMP less work to do.
Kind of long answer, I know. Hope some of it is helpful.
several minutes for just several hundred lines sounds to me like an auto-enter is causing the issue. If you're importing with the auto-enter option on, then you likely have auto-enter calculations firing during the import on the new records. I suspect one of those is rather inefficient, almost certainly looking through a relationship to multiple records to obtain a value.
Basically for every single record imported, a relationship would need to be evaluated adding overhead. Then, depending on the nature of that relationshp, you are possibly needing to download every related record to decide the result of the calculation - if the relationshpi is sorted for example, or if your auto enter is an aggregate function, then however many records are found through that relationship are being pulled from server to client.
PSOS will certainly mitigate this issue, but the real issue might be one that is better resolved by addressing the root cause - whether it be an auto enter calc or something else. If you're getting this performance hit on an import, then you're likely also getting a performance hit when any record in the table is created by other means also.