Open topic with navigation

Import From Excel

netFORUM provides a quick and efficient way to import directly into netFORUM from a Microsoft Excel spreadsheet. The data imported may insert new data, update existing data, or both. This feature was added in the 2007.01 release of netFORUM.

To Import Data From an Excel Spreadsheet:

1. On the List page, click the Excel icon on the List Page Action Links in the upper right hand corner.
2. The Excel Import Utility page will display.
3. Follow the instructions at the TOP of the text box.

Example SCENARIO: Importing a List of Individuals

1. Create and run an Individuals query.
2. Export the results to Excel using the List Page Text Export feature.
3. Open the file in Excel. Depending on how you exported the data, you may be promoted to select the delimiter and other settings.

   Below is an example of how this might look in Excel:

4. Edit some data in the cells, for example change the first or last name or email address(Note: if you copy/paste a row and clear the first column (ind_cst_key), it will be considered a new row). Remove any columns that are not being updated, with the exception of the primary key column which netFORUM will need.
5. Ensure that there is a header row in the spreadsheet. The header rows must be database column names and not column descriptions. For example, instead of Primary Key you must change it to ind_cst_key, for example.
6. Right-click the upper left cell to highlight all of the data, then select Copy.
7. Go back to netFORUM (List page in this scenario).
8. Click the Import From Excel icon in the Group Item Action Links to launch the Import From Excel Utility page in a pop-up window.
9. Click in the text box, right click and select paste (You can "Hover" the cursor in the text box and press CTRL+V).
10. Click Save. The records will update. You may not get a message; wait for the Done message to appear on the browser window status bar. When you see this, you can close the window.
11. The changes should display on any of the records that were edited.

Additional Information

netFORUM has the ability to put security on an Import From Excel. This is done through using the Set Group Security feature. The Import From Excel link is in the Admin module, Icon Security.

Advanced Information

This feature should be used only by advanced users who understand the data and the implications of updating and inserting the data.

The import process initiates from the column names in the first row (This will not work if they do not display). The process merges the column values for each row through our object layer and then does either an update or insert depending on the presence of the primary key guid in the first column.

If you do not know the database column headers, refer to the List Table setup.

Use caution in updating "complicated" fields. Obviously, you could not expect to update an invoice balance, for example. This feature is best used for "simple" data like general demographic fields or extender columns.

Data that is not changed will not be updated, but for prudence and to reduce processing overhead, omit columns you are not updating and if possible omit rows that do not have updates.

To avoid time out issues, work with a small set of data and then gradually increase the number of records.

The EnableImportFromExcel system option must be set to true to enable this feature.

Case Study

Event Registrants

This case study does not create invoices or session registrations, but it can import event registrants directly from the Event Registrant listing (search results) page.

The main Event must have the registration required unchecked on the Event profile; this setting allows for registrants to NOT have a registration fee and associated invoices.

Here is an example of the Excel data that we pasted into the UI and easily imported registrants at the event level.

Note that the reg_key is blank, which will cause a new registrant to be inserted. The reg_cst_key is the Customer Key and the reg_evt_key is the primary key of the Event from the ev_event table.

Committee Participants

Similar in concept to Event Registrants above but with a different table, you can use this feature to mass add Committee Participants.

At a minimum, you must specify the following fields:

• cmc_key - leave blank to force an insert
• cmc_cst_key - the Customer Key. You can get a list of these by running a query from the Individual group item containing the individuals who should be added to the committee, and exporting the results. That export list can serve as a starting point for the Excel file you'll use for the import.
• cmc_cmt_key - The key of the committee. You can determine this from Key querystring value from the Committee profile
• cmc_start_date

You will likely want to specify other fields as well. See mb_committee_x_customer for a complete list.

Here is a sample:

As always, test with a couple of records first before importing a large list.

FAQ

Import link not visible

Q. I run a List of records and don't see the Import from Excel link. Why?

A. Ensure EnableImportFromExcel is enabled. Also, rather then doing an un-filtered List, try instead running a Find with some simple criteria (like last name = "Smith") and from the List results, you will see the link.