Working with database questions from Excel spreadsheets
This section provides information on working with database questions that are derived from Excel spreadsheets. Creating database questions from an Excel spreadsheet is similar other database providers.
Note 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). Failure to set the property to False may result in 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 the UNICOM Intelligence Interviewer - Server Admin will have 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 currently 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 appropriate database question type (Insert > Question > Database Question).
3 Select the Microsoft OLE DB Provider for ODBC Drivers option from the Data Link Properties' Provider tab.
4 Select the Excel File option from the Connection tab's Use data source name field (or select the Use connection string option to manually enter a connection string).
5 Navigate to the Excel file location and select the appropriate Excel file.
6 Click OK on the Data Link Properties dialog to return to UNICOM Intelligence Author.
7 The Connection sting field will resemble the following example:
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;"
8 You will need to manually 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 modified connection string should resemble the following:
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;"
9 You should now be able to select the appropriate table(s) and further define the database question.
Notes
▪If you use MSDASQL.1 as the provider in the connection string, the Excel ODBC DSN must be available on the UNICOM Intelligence Interviewer - Server Admin, otherwise the connection string will fail with a "no default driver specified" error. The DSN name on the UNICOM Intelligence Interviewer - Server Admin must match the name on the client computer.
▪The %Project_Directory% variable is replaced with the project folder path before connecting to the Excel file. Before previewing a database question, you must verify if an empty Excel file (excelfile.xls in above example) is already present in the project folder. If an empty Excel file is present in project folder, close the Author application. Closing Author removes the empty Excel file from the project folder. After the empty Excel file is removed from the project folder, copy the intended Excel file to the project folder. Database questions can now be previewed and activated.
See also