Data Import Utility

Modules>Admin>File Definition

The Data Import Utility allows you to configure data mappings and upload data into netFORUM.

This utility is slightly different from the Import Wizard found in the Sales module.

Using the Data Import Utility

To use the Data Import Utility, you must complete the following steps:

  • Prepare the file you will import. This file can be .xls, .xlsx., csv, or .txt.
  • Add or select a File Definition
  • Upload the file into netFORUM
  • Edit the File Definition
  • Map the desired file columns to netFORUM fields
  • Save and import the data

Preparing the File

You may prepare your import file to meet your needs. While there is no mandated way for the file to be organized, you must include all of the required fields for the type of record you are importing. For example, if the list being imported is a list of individuals, you must include each of the fields that would be required if you were adding an individual manually in netFORUM.

You should also be very thorough in your file design. Making sure you have all of the fields you desire for an import will save you time from having to go back and edit each record individually in netFORUM, should you wish to add a field.

To create your file, complete the following steps:

  1. Create your file with a column for each field you wish to populate in netFORUM, such as Organization ID, First Name, Last Name, and so on. You only need to include those fields that you desire for your import, you are not required to include all of the netFORUM fields available. However, you do have to include all of the required fields for the object you are importing (i.e. individuals.)

    To find the list of required fields for an object, navigate to the Add form for that object type and take note of those fields that are required. Those are the minimum fields needed to complete the import.

  2. Enter your individual or organization information to each of the appropriate columns, for each record.
  3. Save your spreadsheet in one of the supported formats (note that the file name should not include any spaces).

Note: When creating your file, you have the option to enter multiple records for a mass import of data or you can simply add a single row of data for validation purposes and save the import as a File Definition. Once it is saved as a File Definition, it can be used for future imports of data.

Note: When you save the file, be sure there are no spaces or non-alphanumeric characters in the file name.

Add or Select a File Definition

  1. Click the Module Menu and select the Admin option. This will open the Admin Overview page.
  2. Expand the File Definition group item and click the Add Definition hyperlink.
  3. To use a previously entered File Definition, expand the choose file definition drop-down menu. This will display a list of all of the previously saved File Definitions.
  4. Select the File Definition you wish to use by clicking on its name. You will notice the form will adjust to your selection and you will be ready to select the spreadsheet you will upload.
  5. To add a new File Definition, do not make any selection on the file definition drop-down menu. Leave this menu set to the Please Select option.
  6. Choose the type of record you wish to import from the object display name drop-down menu. This choice will correspond with whether the plan for this File Definition is to be used with imports of individual records, organization, or centralized order entry records.
  7. Choose the import edit form. This is the format in which the data will be displayed during the edit phase of the import.

    Once you have completed the fields for the new File Definition, you are ready to select a file for import.

Upload Spreadsheet

After either choosing a saved File Definition or adding a new definition, you must browse and select the file you wish to upload.

  1. Click the Browse button. This will open the Choose File to Open pop-up window. Using this window, navigate to the location of the file you wish to upload
  2. Double (left) click on the desired file.
  3. If desired, click the top row as headers checkbox to have the first row of the spreadsheet used as headers in the upload. If this is left unchecked, the left column of the grid will show column numbers.
  4. Click the Next button.

    Your file will be uploaded and you will now proceed onto the Edit File Definition page where you can provide a name for your new File Definition and map the data fields.

Edit the File Definition

The Edit File Definition page allows you to give your new file definition a name (code) as well as set other optional values. In addition, this page displays information such as the path of the download, the format of the file uploaded, and the type of upload to be completed.

    If you chose to add a new file definition, you may want to give it a descriptive name in order to find it later from the list of saved file definitions.

  • Enter the name you wish to give the new file definition in the code field. By default, the name entered in this field is the same as the file you uploaded.

    The code must also be unique. If you attempt to use a code that has been used previously, you will get an error when you try to schedule the data import.

  • For .CSV and .TXT file uploads, additional fields will be displayed. These fields allow you to configure how the upload will take place. These fields are:
    • text qualifier - allows you to specify a character (string) to be used to ignore the delimiter.

      For example, if the delimeter used in your uploaded file is the comma (,) but you would like to be able to upload values such as Dallas, Texas as a single value you must specify a text qualifier. If you specify the quotation mark (") as the text qualifier for this import, and surround "Dallas, Texas" in quotation marks, netFORUM will ignore the comma between "Dallas" and "Texas" and upload that as a single value.

    • fixed flag? - clicking this checkbox will specify that the file you are uploading uses a fixed width value to separate the values in each row.
    • line count - the line count field allows you to specify exactly how many lines/rows will be uploaded from the selected file.
    • end of file - allows you to specify a character that will signify to netFORUM that there is no more data to be uploaded past that character in the file.

Once this information is set, you can map the data fields for the new file definition.

Map Column Names to netFORUM Fields

The field mapping portion of the Edit File Definition Page contains the fields necessary for mapping the file columns to the netFORUM fields.

Field Mapping Columns

When looking at the field mapping portion of the Edit File Definition page, you will see three editable columns; column name, netFORUM field name, and default value.

Column Name

The column name column displays a list of the column headers from the file that you uploaded.

Note: The column names from the file will only display if the top row as headers checkbox is checked on the File Definition Selection form. If you do not select the top row as headers checkbox, only row numbers will display in the column name column.

If you wish to delete a row from the upload, click the delete icon on that row.

netFORUM Field Name

The netFORUM field name column contains a list of drop-down menus that each contain all of the available netFORUM fields for the object name you selected (either individual or organization.) Those netFORUM fields that match the values in the column name field will be automatically mapped for you.

Notice the had_adr_primary row has not been automatically mapped in the image above. This is due to the fact that there is not a netFORUM field name that exactly matches had_adr_primary. In this case, you must manually select a netFORUM field name from the corresponding drop-down menu, or change the column name value in the spreadsheet to match a netFORUM field name and re-upload the file.

The center column drop-down menu of netFORUM Field Names also has an asterisk next to columns which are required and should be populated

When mapping your columns, you do not have to map them all. You have the option to map as many or as few as you need for your import. The most critical step in mapping the columns is to match the column with the correct netFORUM field name. Failing to do this will cause the data to import into the wrong fields in the individual records.

Note: Deleting a column during field mapping will tell netFORUM to ignore that column during the import.

Default Value

The default value column allows you to specify a default value for a specific row in the data upload. The default value will only be applied to rows that have no value uploaded and will not overwrite any uploaded data.

In the image above, you can see that the value NATW will be added to all records who do not have a value uploaded into the cst:: Organization name (cst_org_name_dn) field.

Note: If you have a field you would like to set a default value for, that was not included in the upload, you may add the new row to the field mapping grid and specify a default value for all records in the default value column.

Fixed Width

If applicable, enter the character width for a column in the fixed width field. Note, the fixed width column is not displayed in the screen shots above. It is located to the right of the default value column and can be accessed by using the vertical scroll bar located at the bottom of the field mapping section of the page.

Map the Columns

  1. Go through the list of columns in the column name column and confirm that those that were automatically mapped were done so to your satisfaction.
  2. Continue through the list of rows and find the first column name you wish to map (that was not automatically mapped, or you wish to change.)
  3. When you locate that column name, expand the drop-down menu in the netFORUM field name column that is next to the column name you wish to map.
  4. Scroll through the list of available netFORUM field names until you find the field you would like the data from the selected column name to map into in netFORUM.
  5. Click that netFORUM field name to highlight it. The netFORUM field name drop-down menu will close leaving the netFORUM field name you chose displayed in the center column next to the column name you are working with.
  6. Repeat this process until all of the columns you wish to map are completed.
  7. If you wish to delete a row from the upload, click the delete icon on that row.
  8. Click the import prev button to preview the data in the import.
  9. Click the Save File Definition and Schedule Import button to save the upload and schedule the import into the netFORUM database.

    Note: When your file is ready to be uploaded, netFORUM will check to see if an import with the same name and same file definition already exists to avoid duplication of records.

Preview the Upload

If you would like to see a preview of what your data import will look like, click the import prev button found below the mapping fields. Clicking this link will launch a FileDefinition preview.

Click the Close button to close the preview.

Schedule the Import

After mapping the columns and previewing the file definition, you will be taken to the Schedule Import form where you can enter the date and time you wish this import to occur.

  1. Enter the date (MM/DD/YYYY) and time (HH:MM:SSAM/PM) in the run task datetime field. Clicking the Now link will schedule this data import to occur in five minutes from the current time.
  2. If you wish to be notified via email as your data import processes, select the appropriate email template from the notify template drop-down menu.
  3. Enter the email address you wish the notifications to be sent to in the notify e-mail field.
  4. Click the checkboxes next to the task events that you would like to be notified about. Your choices are:
    • on start?
    • on success?
    • on failure?
    • on completion with errors?
      You may select as many of these notification types as you desire.
  5. Expand the recurrence drop-down menu and select how often this import will occur. The default setting for this drop-down menu is Once. The other choices available are:
    • Daily
    • Weekly
    • Date of the Month
    • Week of the Month
  6. Click the Schedule Task button.

You will be taken to the Import File Profile page for this upload.

Important! The Data Import Task can also be linked to run after another task completes. For more information on linking tasks, visit the Linking Tasks section of the Task Scheduler page.

Scheduling the Import from the Marketing Module

Most users will schedule data imports from the Marketing module. Scheduling from the Marketing module only allows users to upload files and does not grant access to modify file definitions.

To schedule the import from the Marketing module, complete the following steps:

  1. Go to the Marketing module.
  2. Click the Import File group item to display the list of group item links.
  3. Click the Import File group item link. This opens the File Import page.
  4. Expand the file definition drop-down menu and select the file definition to be used for this data import.
  5. Click the Browse button to locate the file to be uploaded.
  6. Enter the date (MM/DD/YYYY) and time (HH:MM:SSAM/PM) in the run task datetime field. Clicking the Now link will schedule this data import to occur in five minutes from the current time.
  7. If you wish to be notified via email as your data import processes, select the appropriate email template from the notify template drop-down menu.
  8. Enter the email address you wish the notifications to be sent to in the notify e-mail field.
  9. Click the check boxes next to the task events that you would like to be notified about. Your choices are:
    • on start?
    • on success?
    • on failure?
    • on completion with errors?
      You may select as many of these notification types as you desire.
  10. Expand the recurrence drop-down menu and select how often this import will occur. The default setting for this drop-down menu is Once. The other choices available are:
    • Daily
    • Weekly
    • Date of the Month
    • Week of the Month
  11. Click the Create Task button.

You will be taken to the Import File Profile page for this upload.

Import File Profile Page

The Import File Profile Page lists all of the information about the file definition you created.

The Import File Information section of the profile displays the name of the file definition and provides task information.

The Scheduling section of the profile page displays the scheduling information for this data upload. If you would like to make a change to the upload schedule, you can click the Edit button to return to the scheduling form.

The Preview Import hyperlink opens a preview window for the data import. Here you can a make any changes to the import before it is finally committed to the database.

Important! You must have selected an import edit form when creating the file definition or this hyperlink will not be available on the Import File Profile page.

The history child form displays an upload history for this file definition.

The import file save task child form lists all of the import tasks for this file definition.

Duplicate Checking

During the import, the task will check records for duplicates, using the Duplicate Check Child Form logic for the Object being imported. See Retry Task for an example of how this is used.

Retry Task

If the import task completed with errors, you can attempt to retry the task.

For example, this task completed with two errors because of suspected duplicates:

If you click the Retry link on the form, then you can check the save anyway checkbox which will instruct the import task to ignore the duplicate check warning when the import task runs again. You should also check the retry task checkbox.

A new Task will be created for the retry task.

Preview the Import

Before the import is fully committed to the database, you must preview the import and make sure everything is correct. Once you determine that everything is accurate, you can schedule the final import to the database.

To preview the import, complete the following steps:

  1. Click the Preview Import hyperlink located on the Import File Profile.

    This will open the Preview Import page. This page lists every record that is being imported.

  2. Verify the data associated with each record.
  3. Make changes to the records as needed.

    You have the following options:

    • edit - clicking this icon will open the Add - Import File Save Detail pop-up window. You can make changes to several of the fields associated to this record in this window.
    • delete - clicking this icon will remove this entire record from the final import. It will not be committed to the database.
    • exclude - clicking this checkbox will exclude the record from the current import. This is useful if you notice a record is a duplicate and you don't want to delete it, you simply want to exclude it from the import.
    • field-level editing - you may also click in the individual fields and make changes as needed.
  4. Click the Save button to save your work. This will not import the data into the database.
  5. Click the Schedule Import button to save your work and schedule the data to be imported into the netFORUM database.
  6. Follow the steps outlined in the Schedule the Import section of this page to finalize the import.

Import File Save Task Child Form

The import file save task child form displays a list of scheduled data imports for a specific import file.

Import File Save Task Child Form Highlighted on the Import File Profile Page

Clicking the Go To icon next to a task will take you to the Import File Save Profile.

Import File Save Profile

The Import File Save Profile displays information about the import task that has been scheduled.

The Import File Save Profile has three hyperlinks that provide additional insight and functionality into the import task.

  • Parent Task - clicking this hyperlink opens the Import File Profile.
  • Manage Duplicates - clicking this hyperlink opens the Edit - Task Retry pop-up window. This window displays a list of possible duplicate entries for the scheduled data import. From this window you can decide how you will handle duplicate entries.
  • Preview Import - clicking this hyperlink opens the Preview Import page.

The Import File Save Profile has two child forms.

  • history - displays a list of events for this scheduled task. Expanding the grandchild icon will show a list of items in the import and their results. The status column displays the status of the scheduled task. The succeeded column displays the number of records that were successfully imported, while the failed column displays the number of records that failed.
  • import details - displays a line by line account of each record in the import. Clicking the grandchild icon next to a record number will display a message showing the individual status and message for that record.

Importing Case Studies

Importing Customers

To set the web password of a customer, enter the clear-text value of the password in the cst_new_password and cst_new_password_confirm fields (making sure both columns have the same value for each row, obviously). Don't set cst_web_password -- this field will eventually store the hashed version of the customer password, and netFORUM will set that property itself based on what is entered in the first two password fields.

To match to existing customers, map to the cst_id field, not the cst_recno field.

Importing Invoices

The Data Import Utility also supports the mass importing of invoices. The steps to import invoices is very similar to those outlined above, however there are a few differences. See Data Import Utility Invoice on the Wiki for steps on importing invoices.

Importing Memberships

Although the Data Import Utility can import simple invoices for items like merchandise (see the Importing Invoices section above), at this time a more complex transaction like a membership does not work in the Data Import Utility. An alternate approach to the Data Import Utility is to use the WEBCentralizedShoppingCartMembershipGetNew xWeb web method.

Importing Individuals

You may set relationships fields such as ixo_rlt_code and ixo_start_date. To link to the individual to an organization, you may set the organization's Customer Key in ixo_org_cst_key. For more information, search Customer Key on the Wiki.

Importing Registrants

The Data Import Utility can be used to mass-import event Registrants for customers in netFORUM. The utility will not be able to import invoices, payments or session registrations, but it will import basic flat registrant information so you'll know who attended the event.

The Data Import Utility won't be able to create new individuals from the spreadsheet. Therefore, if you have new individuals, first create then as described in the Importing Individuals section. Next, put the customer IDs of the new individuals into the registrant import spreadsheet into the cst_id column. When creating new individuals, we recommend using a distinct individual Source Code related to the particular event. This way, immediately after you import the new individuals, you can run a Query to get a list of the newly created individuals by querying against the individual source code. Be sure to include the cst_id field plus name, address, etc. You can paste this output into a new sheet in your original registrant spreadsheet and use a VLOOKUP function to populate the cst_id field by looking up the individual.

The following contains more detail:

  • The assumption is that you're starting with a spreadsheet (or text file) where you do have IDs or keys relating to existingnetFORUM customers.
  • If you have some registrants who are not existing netFORUM customers, then extract them into a new spreadsheet and import them as individuals as described in Data_Import_Utility#Importing_Individuals. In order to get their new IDs back, include a new source code (cst_src_key) in the upload file to help you identify exactly which individuals get added. We are assuming you have enough data fields to insert a new individual.
  • Run a query of the newly added individuals (using the source code as a filter) and include the Customer ID as well as the same fields you used in the import; this will be needed to cross reference them in the original spreadsheet.
  • With the output of the query, include that in a new "sheet" of your original spreadsheet.
  • Using Excel's VLOOKUP function, lookup the Customer IDs of the newly-imported individuals into the original spreadsheet.
  • You should now have a final copy of the import spreadsheet, containing a list of registrants with their netFORUM Customer IDs, some of which already existed, and others that you have just imported. You may now import these registrants into netFORUM.

Field mappings

At a minimum, you will need the following fields in your import file to designate the individual and the event:

  • Customer ID or Customer Key that should be mapped to cst_id or cst_key respectively.
  • Event Code that should be mapped to evt_code.

    Note: The event code for the event must not be duplicated. If you have two events with the same event code, then the import may not work.

If you track attendance, then we recommend including:

reg_attendance_flag - set to 1 if the person attended, 0 if they did not

Importing Customer Actions

If you want to import Customer Actions, see main article for instructions and a template. Customer Actions are records that store condensed information about other records that occur in netFORUM or external systems. Data in this table is used by A-Score, Implicit Interest Codes and other purposes. Third-party systems can also insert data into this table via xWeb to log various activities that occur in systems external to netFORUM. Generally, you won't manually insert or update records in this area because other activities perform this role.

The Data Import Utility can be used to mass import Customer Actions. The ability to import customer actions starts in 2011.01.05.

Field mappings

At a minimum, you will need the following fields in your import file:

  • Customer ID or Customer Key that should be mapped to cst_id or cst_key respectively.
  • acn_action - brief description of the Action
  • cat_action_code or acn_cat_key. You can use the Action Code or Key.
  • acn_date

Troubleshooting

Microsoft.ACE.OLEDB.12.0 error message

If you get this error: 

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Then you need to install the Access Database engine on the web server. netFORUM solutions hosted by Abila will have this but if you're working on a new server that you are hosting yourself, then be sure to add that to the web server.

Column Mappings Drop Down List is Empty

If the list of columns from your spreadsheet is empty, verify that the value for the ScheduledImportSharedFolderPath system option is correct. If it's incorrect, then your file might not be getting uploaded properly and as a result netFORUM won't be able to "see" your columns or data.

Columns from Sample File do not appear

If the columns from the sample file do not appear, then see the Column Mappings Drop Down List is Empty section for resolution.