Working with database questions from Excel spreadsheets
Creating database questions from an Excel spreadsheet is similar to other database providers.
When using an Excel spreadsheet as the data source, you must set the CacheConnection property to False on the Advanced Properties pane (the default setting is True). If you do not set it to False, there might be errors when questionnaires that use the Excel spreadsheet as a data source are reactivated.
The easiest method for using Excel files is to store them on a network drive to which UNICOM Intelligence Interviewer - Server Admin has access. The Excel file path will likely be the same from the client computer and the UNICOM Intelligence Interviewer - Server Admin.
1 To reference an Excel file that is on your system, put the file in the associated project’s _files folder. This ensures that the file will be included with the files that are uploaded to the UNICOM Intelligence Interviewer - Server Admin.
2 Insert the database question type: click Insert > Question > Database Question.
The Data Link Properties dialog opens.
3 On the Provider tab, select Microsoft Office 12.0 Access Database Engine OLE DB Provider.
4 In Data Source, specify the path to your Excel file.
If the Excel file is in the project’s _files folder, set the Data Source to %Project_Directory%\<filename>. %Project_Directory% is evaluated by UNICOM Intelligence Author and the server software, so the location can be used both locally while developing, and on the server after the project is activated.
If the Excel file is on a network drive, enter a full path that is accessible by both UNICOM Intelligence Author and the server software.
5 Click OK to return to UNICOM Intelligence Author.
6 Select the appropriate tables, and then further define the database question.
The Connection sting field looks like this:
Provider=MSDASQL.1;Persist Security Info=0;Extended Properties="DSN=Excel Files;DBQ=C:\Documents and Settings\<username>\
My Documents\My Surveys\DBDemo\dbDemo_files\excelfile.xls;DefaultDir=C:\Documents and Settings\Administrator\My Documents\
My Surveys\DBDemo\dbDemo_files;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
7 Replace the following highlighted sections with %Project_Directory%:
Provider=MSDASQL.1;Persist Security Info=0;Extended Properties="DSN=Excel Files;DBQ=C:\Documents and Settings\<username>\ My Documents\My Surveys\DBDemo\dbDemo_files\excelfile.xls;DefaultDir=C:\Documents and Settings\<username>\My Documents\ My Surveys\DBDemo\dbDemo_files;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
The connection string now looks like this:
Provider=MSDASQL.1;Persist Security Info=0;Extended Properties="DSN=Excel Files;DBQ=%Project_Directory%\excelfile.xls;
DefaultDir=%Project_Directory%;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
8 Select the appropriate tables, and then further define the database question.
Notes
▪The Microsoft Office 12.0 Access Database Engine OLE DB Provider must be available on the UNICOM Intelligence Author development computer and on the UNICOM Intelligence Interviewer - Server Admin server.
▪The %Project_Directory% variable is replaced with the project folder path before connecting to the Excel file. Before previewing a database question, check if an empty Excel file (excelfile.xls in this example) is already in the project folder; if it is, close Author. Closing Author removes the empty Excel file from the project folder. Then copy the intended Excel file to the project folder. Database questions can now be previewed and activated.
See also