Import From Excel

netFORUM provides a quick and efficient way to import directly into netFORUM from a Microsoft Excel spreadsheet. The data imported can insert new data, update existing data, or both.

Important! In order to import files to netFORUM, you must have installed the 64-bit version of the Microsoft Access Database Engine 2010 Redistributable components. For more information or to download this utility, see https://www.microsoft.com/en-us/download/details.aspx?id=13255. If you encounter issues importing files into netFORUM, contact your system administrator and make sure this component is installed on the netFORUM Enterprise server.

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 can 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 might 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.

reg_key	reg_cst_key	reg_evt_key	reg_registration_date
	0F11FBEE-E859-4133-B37E-5F40CD34988F	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	2/1/2010
	16A02E40-783D-4999-A355-5BC893DB3939	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	2/1/2010
	B223D445-1976-4BDF-97E8-2E4BCB13ACBB	8d3920f-82c1-4081-a1f8-9fcbacd5b62a	2/1/2010
	90E6C5F5-43CC-49C2-BBEE-5435775F6782	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	2/1/2010
	CF897314-9FA5-4169-9090-8548254CB5AA	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	2/1/2010
	C4E992E6-78CF-46BB-9CC7-681F33578800	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	2/1/2010
	D2ADD189-7054-4988-9BEF-A1C41F82FEF1	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	2/1/2010
	D4B84B87-B4F9-4B46-926F-A7DD3241CE71	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	2/1/2010
	8D593CB1-2ACE-4EAF-A2F3-EE05DD158884	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	2/1/2010
	E964E1B6-001E-45C4-AA10-E77E62FCFCCA	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	2/1/2010

Note that the reg_key is blank, which causes 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 might want to specify other fields as well. See mb_committee_x_customer for a complete list.

Here is a sample:

cmc_key	cmc_cst_key	cmc_cmt_key	cmc_start_date
	0F11FBEE-E859-4133-B37E-5F40CD34988F	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	1/1/2011
	16A02E40-783D-4999-A355-5BC893DB3939	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	1/1/2011
	8D593CB1-2ACE-4EAF-A2F3-EE05DD158884	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	1/1/2011
	E964E1B6-001E-45C4-AA10-E77E62FCFCCA	78d3920f-82c1-4081-a1f8-9fcbacd5b62a	1/1/2011

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.