Importing Members from CSV

Preparing Your Data

After setting up your member types you will likely want to add your members to the list of members. The easiest way to do this is to import them from a CSV (Comma Separated Value) file. CSV is a very common file format type that Excel and most other spreadsheet applications can export their data to.

No Comma’s in the CSV

There are some caveats for the CSV data that you need to adhere to in order to have a successful import. First and most importantly is that you cannot have any comma’s in your data, even if the data is quoted. Here is an example:

Comma’s in the data is by far the most common caveat that we see happening when new users are importing data for the first time.

Header Row

The first row of the data needs to be a header row. This is because this is used during the CSV import mapping step:

Here you can see we always consider the first row to be a header, and display the header (replacing spaces with underscores) in the CSV Source Fields column.

Add Appropriate Member Types

If you do not provide a member type the import members won’t have one set. In order to fix this now, go to your member types table, click on the gear icon button next to the Create button, in the columns dialog turn on the “ID” column, then close the columns dialog. You should now be able to see the internal ID’s for the member types.

Go to your CSV file (or back to your Excel file) and add a column named “member type” and set the appropriate internal ID for each member type to each member.

Date Formatting

Our system maintains 3 dates for each member. Their join date, their expiration date, and their updated date. During an import, if any of these dates are not provided, the system will use the current date. If you know what you want these dates to be set to, you can provide that in the CSV, however the date needs to be in ISO standard date format. This format is YYYY-MM-DD, so January 12, 2020 would appear as 2020-01-12. If the date cannot be interpreted, the current date will be used.

IDs

If you want to import your members IDs as part of the data, you can, however the IDs must be number, no letters, spaces, or any other characters, and each number must be unique. If you do not provide an ID the system will start assigning IDs sequentially starting from the next available ID number.

Start Importing

If you have gone through the preparation, as described above, you should be ready to import your data. To begin, click on the “Import” icon above the members table

The import dialog will apper

Click on “Choose File” or drag and drop your members CSV file into the area at the top where it says “Drop Files Here”. The system will run a pre-process on the data to try and determine if there are any potential issues with the CSV file.

When this pre-processing is complete, a CSV summary dialog will appear. You should take this opportunity to verify that everything appears correct.

• Total Lines is all the lines in the CSV, including header. If this is incorrect, then the import was unable to distinguish your line ending characters. Make sure the file uses CRLF or Windows type line endings
• Columns Found should match the number of comma separated columns you have in your first row
• Total Data lines should accurately be the number of lines excluding the header line (so one less than the total)
• Rejected Lines should not have any number

If there are rejected lines, they will appear in the middle area of the dialog, and it will try to report the line number (or close to the line number) where it found an issue.

If rejected lines are found, close this summary, and close the import dialog, go back to your CSV and correct the problem. Then come back to this step.

Close when you are done

Linking Columns

The next really important step is to link your CSV columns to the database columns. This is done by clicking one of the target column boxes on the left and one of the source columns on the right so that they are both highlighted in green

And then click the “Link” button to link them. They will change from green to salmon color

If you make a mistake and want to un-link columns, just select either the source or the target, both will appear as salmon color, and click the “Link” button again to unlink them.

Repeat this process until all the source fields are linked to an appropriate target field.

With phone numbers, our system can be used to send test messages to members. If you plan to use this feature you will need to specify which phone number the system should use for your members when sending a text message (usually a mobile number). If you know what phone numbers in your CSV file are mobile and which ones are not, we recommend separating them into 2 columns, one for mobile and one for other, and when linking columns link the mobile number to the “Mobile” target.

When you have completed linking all the columns, click the “Import” button.

The importing process will begin. During the import the system will try to determine if you already have a member in your database for each row you are importing. It tries to match on member id, mobile number, and name. If it finds a matching member, it will update the member with the data from the CSV, if it does not find a match it will create a new member with the data. When it is done the system will report how many members were created and how many were updated.