Importing memberdata

From MyMemberSoftware wiki
Jump to: navigation, search

The data of members is saved in a flat table, called 'leden'. Based on a simple excel file you can import your member data.

Steps

  • Prepare the data in excel by following the rules of the Data dictionary below.
  • Import the data with a tool like CSVI pro or Phpmyadmin.

Data dictionary

In your excel file, the following rules apply to the values.

  • Member id: unique, only numbers. For example: 200, 201, 301, 302. This is the number associated to the membership. Make sure you don't have duplicates in the member id; it's a unique key!
  • Member type: a number, which you should look up in the backend of MMS: components -> MyMemberAdministration -> Roles. Use the id that is displayed at the membertype. For example: id = 1 for "Member". Use the ID's from the Joomla backend of your site. Go to Users/Groups and use the ID of the groups you need.
  • Gender: in the database stored as "M" (Male) and "V" (Female). If emtpy, then its unknown.
  • Position in the household; head of household / family member.
  • E-mail: unique, with a valid e-mail syntax. Make sure you don't have duplicates in e-mail id; it's personal and an unique key!
  • Zip: In Holland, postal codes are 4 numbers, a space, and 2 Capital characters. For example: 1111 AA. In Belgium, it is always 5 numbers. For example: 12345
  • Country: 2 characters ISO. For example NL, BE, DE, UK.
  • Payment method: K = Invoice, I = Direct debit, A = Acceptgiro (Deposit Transaction Card).
  • Department
Location unix date.png
  • Date columns should be in this format: yyyy-m-d. You can accomplish this by changing the short date format in Windows settings to yyyy-MM-dd, or by selecting the date values in excel, right click, and change the cell properties to location = "English (United Kingdom) format "yyyy-mm-dd". In Micosoft VBA you can accomplish this with the VBA "Format" clause. Example: Startdate = Format("01-01-2016", "yyyy/mm/dd". Beware that the output field "Startdate" should be a string and NOT a Date field.
  • Values of columns should be consistent; for example if a column consists of a date, only a date should be in the values and not reasons for ending subscriptions. If a Y/N value (checkbox) is in the column, no X, Y or Z should be in it.
  • Yes or No values fit in a checkbox, so recode Yes to "on", and No to "off" or "" (empty).

Check this example file to see if your data are in the correct format.

Import the data

The steps needed to import your data in the table are:

  • make an excel file, with the exact same columns as this example.
  • save as csv file, fields separated by ";" is easiest. Make sure there is no ";" in the values though!

Data preparation

Customize date format.png
  • Windows: in the regional settings, select the data format as yyyy-M-dd. This is important to import dates correctly, in the unix date format.
  • Gender: save F and M in the column, and let CSVI change it to M and V.

Choose a tool to import your data in the database, 2 valid examples:

CSVI pro hints
For CSVI the file needs to contain the "UTF8" character set. Open the csv file in notepad, choose "save as" and change the character encoding from ANSI to UTF8. Save the csv file.
For CSVI pro we made a template that you can download here. The procedure to import CSVI templates is documented on this page
  • make a CSVI template with Action = "Import", Component = "CSVIPro", Operation = "Custom tables" and "use file for configuration" = No.
  • Edit the template, at Options, specify the name of the table. This name should have the name "leden". This value can be selected from the dropdown list.
  • Specify the fields in the template to import. (go to Edit Template Fields). Select New for each field.
  • select the field-name in the dropdown-list (these are the field names in the Joomla table 'leden'). the second field is "your field name". Type in the name of the field in your csv file. In the third field you can specify a default value if "your field name" is empty (not required).
  • Make the order of the fields in the CSVI-template the same as the order of the fields in your csv-file. The order of the fields in the template should match with the order of your fields in the csv file.
PHPMyAdmin hints
If you didn't save the CSV to UTF8, but use default windows: use charset iso-8859-1 for importing. "; delimited", "first row contains column headers"
  • import the data to the database.
  • check the values in the database.

Joomla users

Now that the member data are in MMS, the members need Joomla accounts to edit their own data and get permissions on the website for the intranet. The match is being made on the e-mail address (which should be unique in both MMS and the Joomla usertable) from the Joomla user to the Member in MMS.

The accounts can be made manually if there are not many members, or use for example CSVImproved for this job.

Maintenance

The idea of MMS is that the administration is mainly done by the members. So at the same time the member applies for the organization, he/she can create a Joomla account and choose his own password. In the future he can log in to edit his member data.