Data Export with ConfTool – FAQ

This page addresses some questions that may arise while using the data export function of ConfTool. You find the related function in ConfTool on this page:

Overview =>Data Import and Export =>Export Data

Hints and recommendations for using the export function:


(A German version of this article is available.)

Data Export at the End of Hosting

At the end of the hosting period, we will contact you to allow you to export your data. Please make sure that you have exported all required data before we remove the installation. For an overview of options to save data from the system please consult the ConfTool Support Forum entry:  Exporting all data from ConfTool Pro.

Warning Message

When opening the downloaded excel file, an alert message appears saying that the file "could be corrupted or unsafe" (see Image). Please click "Yes" to proceed.

There is a simple explanation for this message: The file is in the documented Excel XML format (other formats by Microsoft are not well documented), but as the extension is ".xls" and not ".xml", this message is shown. If we would use ".xml" as extension, the file would not be opened with Excel automatically, but with the Internet Explorer or another tool. If you edit and save the file, we recommend saving it in Microsoft Excel file format (".xls").

Protected View

The file was opened in "Protected View" with a message that says "This file originated from an Internet location and might be unsafe. Click for more details" (see image).

This message is shown whenever a file was downloaded from the Internet (as files from the Internet could contain viruses and other harmful content embedded in them). Please click on "Enable Editing" to edit and use the file. Please note that the Excel XML file format used to export data from ConfTool is safe, i.e., there cannot be any harmful code in files with this format, only data content.

Language Column Headings

The headings of the columns are always in English and cannot be switched to another language. The reason for this is that it simplifies the electronic processing of data when the names of the fields are consistent, for instance for bulk/mail merge letters.

The Meaning of Numerical Codes

a)   If you choose the export option "Export Participant Details", the list titled "Export Participants" will be created.  This list contains the column "canceled_registration" and the digits in this column represent these ideas:
0 means "User has registered for conference",  
1 means "Canceled participation with cancelation fees",  
2 means "Cancelled Participation without fees".

b)   If you choose "Index of Authors", you will get a list titled "Subsumed Authors".
In column "personID" you may get information that allows you to check the consistency of data. For instance, there will be messages if there are name or email address mismatches between author and user account. Possibly, information about the author of the related submission needs to be corrected: "name mismatch" means that a user account with the same the e-mail was found, but the user name is not identical to the author name, and "email mismatch" means that the a user with the same name was found, but the e-mail is not identical.
You find additional information on how to identify discrepancies between authors and user accounts in this entry in the ConfTool support forum entry: Identify presenters who are not yet participants and create user accounts for them / invite them to participate.

The column "submitter_of" lists the number of submissions the user has contributed as "submitting author". The column "presenter_of" shows for how many submissions the authors was selected as "presenting author".

For each submission the author is related to (see column "paperIDs"), you find relevant information in column "sessions_short_index". Examples: The code "Main Panel:2" means that the author will give the second presentation in the session called "Main Panel". If your session short title is "Pre-Conference 05" the data "Pre-Conference 05:3" signifies that the author will give the 3rd presentation in this session. (Sub-papers of symposium submissions are currently not considered.)

Overview of Last Users Log-ins

If you want to get an overview of all users log-ins, please use "Export Registered Users" (alternatively you can use the online list when you go to
Overview =>User and Participant Management=>List of All User Logins).

Please use the option "Include columns with many user details like title, nationality, region, passport ID, visa information, remarks and customized fields". For instance, if you want to figure out when each reviewer has logged in last, please choose the export filter "Only Users with Status" to select "Reviewer" accordingly (see image). In the list you can find the required information in the column "user_last_login". An empty field means that the user hasn’t logged in yet.

Check the Downloads of Submissions

In case you want to know if all reviewers have already downloaded the files of the papers they are supposed to review, please use "Export of Reviews" and "Include open (newly assigned) and draft (incomplete) reviews". The column "download_date" shows the related information. If the download date cell is empty, the reviewer hasn’t downloaded this paper yet.

In the system, you also have the option to check the reviewer download dates of assigned contributions when you go to
Overview => User and Participant Management => Program Committee and Reviewers
and click on "Assign Reviews", or when you go to
Overview => Submissions & Reviews => List of Submissions
and click on "Assign" respectively.

Tracking status and progress of registrations

a)   If you want to get an overview on how many people have registered over time, you can do so easily with the export function, too. Please choose "Export Participant Details".

In this list, you will find the column titled "registration_date" which allows you to survey the registration progress. Please sort the registration by date in ascending order (click on "Filter", select the column, and click on "Sort Oldest to Newest" - see Image 1).

b)   To create a line chart as a visual representation,  insert a new column right of the column "registration_date" and call it "Number of Participants". Please enter a subsequent number into each cell In this column (1, 2, 3, …) by typing in 1 and 2 in the first two cells, selecting the cells containing the starting values, and then simply dragging the fill handle (box) down across the range that needs to be filled in. Excel will then automatically fill in the other cells.

Then, select the two columns "registration_date" and "Number of Participants", click on the "Insert" tab (in the Charts group) and choose "Line" (see Image 2), to create a chart that provides an overview of the progress of registrations.

Prepare data for name tags of accompanying persons

Please choose "Export Items / Events booked by Participants" and set the drop-down list "Events and Items" to "Accompanying Person". The relevant part of the list is the column with the heading "text".

If you have used the Extra Input Option "Several text input fields" when creating the Event/Item "Accompanying persons", you will get the text inserted regarding the accompanying person in multiple lines of data per cell.
 
In case you need to split this text into different cells, please proceed as follows:
 
1.   Add as many empty columns right of the column as you want to split it into, e.g. if you have 4 input fields, please add 4 columns.
 
2.   Now please select "Data > Text to Columns" to start the Excel tool that will split this field into several Excel columns (see Image 1).

3.   Please select "Delimited" on the first page and (see Image 2) on the second page use "Other” as delimiter and enter "CTRL+J" in the text field (Image 3).

Filter all users with a specific status

If you want to get an extra column with all users with a specific status, please:

1.   Create an extra column right of the status column

2.   Use the formula =ISNUMBER(SEARCH(substring,field)) where "substring" is the status you are looking for and "field" the name of the status field. Example: =ISNUMBER(SEARCH("member",S2))

Filtering the exported data by using the AutoFilter function of Excel and LibreOffice

If you need to filter specific information from the exported data, you can use the AutoFilter function in Excel or LibreOffice.
You can receive the following information, for example:
- A list of all participants with a certain status like “Student” / “Member”
   (The status can be defined by the organizers on this page: Overview => Settings => Manage Participant Groups)
- A list of all participants who want to pay with a certain payment method (like bank transfer)
- A list of all submissions on a specific topic

By filtering information in a worksheet, you can find values quickly. You can filter on one or more columns of data. Filtering allows you to control not only what you want to see, but also what you want to exclude. When you filter data, complete rows are hidden if the values in the corresponding columns do not match the filtering criteria.

Please follow these steps to apply the AutoFilter in Microsoft Excel:
1.   Select the data that you want to filter. You can select the whole sheet by typing CTRL and A.
2.   On the Data tab, in the Sort & Filter group, click Filter (see image 1).
        Now you will see small arrow buttons in the column headers of the database export.
3.   Click the arrow in the column header to display a list in which you can select a filter.
4.   Decide whether you want to select specific values or search for values.
5.   Filter by selecting values: Uncheck (Select All) to clear all checkboxes, and then select the checkboxes for each value you want to display (image 2).
6.   Filter by searching: Use the Search box to enter text or numbers that you are looking for (see image 3).
7.   Click OK to apply the filter. Only those rows are displayed whose content matches the filtering criteria. The other rows are hidden.
8.   Remove filter: Clicking the Filter button once more will display all rows again.

The free software LibreOffice Calc also offers the option to apply an AutoFilter:
1.   Select the data that you want to filter. You can select the whole sheet by typing CTRL and A.
2.   Click on the AutoFilter button in the toolbar.
        Now you will see small arrow buttons in the column headers of the database export (see image 4).
3.   Click the arrow in the column header to display a list in which you can select a filter.
4.   Decide whether you want to select specific values or search for values.
5.   Filter by selecting values: Uncheck (All) to clear all checkboxes and then select the checkboxes for each value you want to display (image 4).
6.   Filter by searching: Use the Search items box to enter text or numbers that you are looking for.
7.   Click OK to apply the filter. Only those rows are displayed whose content matches the filtering criteria. The other rows are hidden.
8.   Remove filter: Click the AutoFilter button on the toolbar once more to remove the filter and display all rows again.

You will find further tutorials on the topic AutoFilter on these pages:
https://www.wallstreetmojo.com/auto-filter-in-excel/
https://www.contextures.com/xlautofilter01.html
https://help.libreoffice.org/3.6/Calc/Filtering_Cell_Ranges