Have an idea?

Visit Sawtooth Software Feedback to share your ideas on how we can improve our products.

Can I import external data to use as a reference for survey questions?

I have data in CSV format which provides information for a set of projects in terms of which people are on those projects, their job role etc.

I have created a feedback form to be filled out at the end of each project and I want to be able to reference the data to do the following:

1. Display the people on that project as options for a question. E.g. One of the questions will be " Who are you giving feedback to?" and the options should be just the people on that project. I would be happy to do this by creating a constructed list if it's feasible.

2. Show people different content within questions based on their job role.

E.g. We have a question asking if the person has exceeded/met/not met each required competency, though the criteria required to achieve 'met' changes depending on seniority and specialism. Presently we have assigned people to a quota based on what number they are in the list of people to give feedback to (ordered from lowest position to highest), then we've used the tooltip function to provide hover-over details of what is required depending on their role. This works to an extent but requires a lot of updating whenever job roles change or someone joins/leaves, so we'd like to do it by linking to an external source as mentioned if it's possible. See below for our code within the 'Met' option on this question.

 [% Begin Unverified Perl
if(GETVALUE("JobRoleQuota")<3) {return TOOLTIP("Met*", "<u>Level 1</u>
<br />-Drawing sound conclusions based on all the evidence, in line with objectives", 200,40); }
if(GETVALUE("JobRoleQuota")>2 && GETVALUE("JobRoleQuota")<6) {return TOOLTIP("Met*", "<u>Level 2</u>
<br />-Dealing with complex and inter-related information to interpret patterns and links", 200,40); }
End unverified %] 

So far the only way I can see that might achieve this is to upload the CSV file to a web server then use File > Data Management > Get Data > From web server, to save it.

I've looked on the forum and Sawtooth help but I can't find any way to reference this data within questions once it is imported. So my questions are:
-    Is it possible for me to use this method?
-    If so, could someone advise me how to do it in terms of what function(s) to use and how to reference the database file with the imported data?
-    If not, please could you advise if there is an alternative method which could achieve the same goal?


asked Mar 1, 2018 by Mike
It's possible to read your CSV from the /graphics folder with a script like this:

[% Begin Unverified Perl
my $in = '../../graphics/test.csv';
open (my $reader, '<', $in) or die;

my $header = <$reader>; # Discard first line of file
while (<$reader>) {
    my $line = $_;
    # ...
close $reader;
End Unverified %]

(Be sure to remove one of the "../" before uploading your survey.)

Line 8 is where the remainder of the code would go.  You will want to test each line with some criteria to determine if that is the relevant line for the current respondent, then use the Sawtooth Script function SetValue to record a value from the relevant line.  The exact code needed to implement this depends on how the information in your CSV is formatted.
It should be noted that file I/O is not always fast.  If you want your information stored in a CSV, it may be wise to conduct some performance testing on your survey before releasing it to the public.  However many people you believe may be taking your survey at the same time - you want to make sure that that many people can read from the file at around the same time.
Thanks very much for your reply Zachary! Do you know what commands could be used to return particular values within that CSV file?
What criteria do you want to use to determine what cell to read?  Something akin to "on the row where column 2 is equal to this pass-in field, get the value from column 4."

1 Answer

0 votes
You can also link your survey with a database using perl::DBIConnect. Sometimes there occurs a lag while reading data fron csv file and this comes in handy. You shall import the csv data in a database which will be on the same server on which the survey is hosted. You can then use perl::DBIConnect to directly access the data kept in database using simple SQL commands.

Let me know if this helps!
answered Mar 3, 2018 by Arpit Thukral Bronze (905 points)