Our Research company has got 3 servers (in house), 1 hosts Drupal - outward facing, 2nd hosts LimeSurvey - outward facing, 3rd is the database server (db for Drupal and LimeSurvey) - sits inside institute's firewall.
Drupal contains Participant's database, LimeSurvey hosts multiple Surveys (which are really long surveys varying from 200 - 400 questions!).
I want to import the survey responses from each survey into separate custom tables in Drupal EVERYDAY. (custom tables because don't want to create Content type which in turn creates 200 tables, 1 for each field!)
QUESTION: What is the best way to achieve this?
Work done so far:
- was able to automate daily csv export from LimeSurvey and copy to Drupal server in a private secure folder.
- tried multiple Contrib modules for Drupal to import csv:
LimeSurvey Sync was the better solution out of all other. works fine without having to automate csv export, copying, etc., except it's real time and don't really want to connect Drupal - LimeSurvey real time (want Drupal to be separate and super secure, and not connected to any other system!) This also had some glitches with very long surveys. (random errors connecting to LimeSurvey)
Import CSV Data Could create only 1 custom table, could not find out how to create separate custom tables for each survey
Data along with Feeds module. Could not get this working! Created a new custom table using Data module config page. This was reflected in Feeds Imported config page. Configured to Attach to "Use standalone form" in basic settings, file uploader, csv parser, Node processor. And didn't know what to do next, how do I schedule import?
Please suggest if I am in the right way OR if there is any other better way of achieving this?