Using native SQL imports

Importing data directly into the SQL server is the fastest way to get your members added to the Aurea List Manager database. The following fields should be imported or otherwise filled in the Aurea List Manager members_ table:

• EmailAddr_: the recipient's email address.
• Domain_: everything after the @ sign in lower case.
• UserNameLC_: everything before the @ sign in lower case.
• List_: the name of the list the address should be assigned to.

The SQL database server provides many methods to import data. You should use the program that you feel most comfortable with to accomplish this task.

Documented below is one method that uses the program BCP (Bulk Copy Program) that ships with Microsoft SQL Server. This BCP reads a format file to know how to read the data file, and then rapidly adds data to the SQL Server.

The data below gives an example of a format file. In this example, the absolute minimum number of columns is provided to load data into the members table. In this case, the EmailAddr field is the only one required, and the rest is defaulted or auto-computed by the SQL Server.

This example should not serve as a substitute for the documentation provided by Microsoft for the BCP utility.

In this case, BCP is expecting a file with a single email address that is no more than 100 bytes long and is terminated by >\r\n. If you want to bulk load additional columns of data into the member table, you can add columns to this format file.

7.0
1
SQLCHAR 0 100 "\r\n" 11 EmailAddr_

The basic arguments to the BCP program are:

BCP members_ in Members.TXT –fmembers.fmt –Sserver –Uuser –Ppassword

Where:

• members_ is the name of the members table.
• Members.TXT is the text file with email addresses separated by \r\n.
• members.fmt is the name of the format file described above.
• Server is your server name
• User is your user name on that server that has rights to bulk-copy into the database
• Password is the password for this user (if any).

There are other arguments to BCP that may apply to your situation. Please consult the provided documentation from Microsoft for further details.

Using DTS (Data Transformation Services) to import to Microsoft SQL server

• There is a bug in DTS that prevents CSV files with identity columns from being imported. The workaround is for this can be found on the Microsoft site.
• It involves importing the data into Microsoft Access, mapping the Access columns to the SQL server columns, and then doing the import. The second example is the example that relates directly to the Aurea List Manager members_ table.
• An easier way to import CSV data is to use the Members > Add Members > Import Members from the CSV file feature.