Importing WordPress Users via CSV Files

I recently did some time in Microsoft Excel, preparing large CSV files for import into WordPress. Each of these CSV files contained data for 1000 WordPress users. Here is a screenshot showing the structure of the file:

Conceptually, the idea is simple: import the data to create actual users for a WordPress-powered site. The trick is to clean the data as much as possible to ensure valid username and password information. Once the data is good, importing is easy using a plugin.

Here is a step-by-step tutorial that combines WordPress and Excel techniques to register users en masse from a CSV file.

Remember to backup your database and CSV file(s) before making any changes.

Step 1: Prepare the Data

This project begins with a CSV file containing only first & last names and email addresses. To create the usernames, we combine the first two cells and remove any whitespace:

=SUBSTITUTE(A2&B2," ","")

Place that in cell C2 and then drag the plus sign down the column to apply the formula and create usernames for all users. Then convert the formula data into actual values:

1. Copy the entire username column
2. Go to Edit > Paste Special
3. Then Paste as Values only

Now we have everything but the passwords. One way to create a column of random passwords in Excel is by creating a user-defined custom function:

Public Function RndPass(Length As Integer, Optional Lower As Boolean) As String

Dim Max As Integer
Dim Min As Integer

Dim RndPassLoop As String

Max = 126
Min = 48

Randomize Timer

If Length < 8 Then
Length = 8
End If

For i = 1 To Length
RndPassLoop = RndPassLoop & Chr(Int((Max - Min + 1) * Rnd + Min))
Next i

If Lower = False Then
RndPass = RndPassLoop
Else
RndPass = StrConv(RndPassLoop, vbLowerCase)
End If

End Function

To use this function, follow these steps:

1. Switch to VBA mode by pressing Alt+F11
2. Insert a new module by going to Insert > Module
3. Paste in the RndPass function
4. Save the new module by pressing Ctrl+S
5. Quit VBA mode by pressing Alt+Q

Then apply the function by pressing Shift+F3 and selecting the RndPass from the “User Defined” dropdown menu. The syntax is RndPass(password-length, lowercase). For example, we would use this to generate 16-character passwords that include both upper- and lower-case characters:

RndPass(16,false)

After generating the passwords, replace the formula data with actual values as we did with the usernames. Four columns of clean data is the goal here, so next step is to clean it up and prepare for import into WordPress.

Step 2: Cleaning the Data

At this point, the CSV file contains all of the required data:

Now we want to make sure everything is squeaky clean:

• Remove all special characters from firstname, lastname, and username
• Remove all invalid characters from the auto-generated passwords
• Quick check for email validity

Cleaning up the data is really the key to a successful import. If present, certain characters will cause errors, resulting in unusable user data and a big waste of time. By taking the time to prepare good data, we ensure that the plugin can do its job and that all imported user accounts will be valid and ready to use.

Remove all special characters from firstname, lastname, and username

For firstname, lastname, and username data, we want to remove anything that isn’t upper/lowercase alphanumeric. We could do this manually using Find & Replace for stuff like:

• | % . + ; , - ! _ ' ? * ~
• Characters such as é and ó
• Whitespace

Another option is filter WordPress’ sanitize_user function to allow such characters, but why modify WordPress when you can just clean the data instead? Here is a simple user-function for removing all non-alphanumeric characters (404 link removed 2012/06/16) from any cell data:

Function GETALPHANUMERIC(text)

str_all = "abcdefghijklmnopqrstuvwxyz1234567890"
For lenstr = 1 To Len(text)
    If InStr(str_all, LCase(Mid(text, lenstr, 1))) Then
        GETALPHANUMERIC = GETALPHANUMERIC & Mid(text, lenstr, 1)
    End If
Next

End Function

Just insert into your VBA module from Step 1 and apply the function to the entire firstname, lastname, and username columns. Viola! Squeaky clean user names! But the function isn’t perfect, and may return a zero (0) if a calculation fails. So just to be safe, Find any zero values and replace them with valid data.

Remove all invalid characters from the auto-generated passwords

From the intel I gathered, WordPress passwords may contain the following characters:

• Any lowercase/uppercase alphanumeric characters
• These symbols: . ? / ' " ; : | ] } [ { = + - _ ) ( * & ^ % $ # @ ! ~
• Whitespace

So more flexible than username data, but we still need to remove the following characters:

• The backslash: \
• Left-angled brackets: <
• Right-angled brackets: >
• The dreaded comma: ,

To remove these items from the passwords, we could modify the GETALPHANUMERIC function from above, or just perform the following Find-&-Replace steps:

1. Replace #NAME? with a valid, random password
2. Replace RndPass with a valid, random password
3. Replace = with nothing
4. Replace @ with any valid character (or nothing)
5. Replace \ with any valid character (or nothing)
6. Replace < with any valid character (or nothing)
7. Replace > with any valid character (or nothing)
8. Replace , with any valid character (or nothing)

The first two replacements fix any errors from the RndPass function from Step 1. The next two replacements are needed to prevent Excel from treating the passwords as formulas. And the final four steps remove invalid characters for WordPress.

Tip: Replace any formula data with actual values by copying the column and then Pasting Special as Values.

Once the names and passwords have been prepped and cleaned, spend a few moments to examine your CSV data and make sure everything looks good. If so, your file should be ready for import into the WordPress database.

Step 3: Importing the Data

Now for the fun stuff! To import our freshly pimped CSV data into WordPress, we use the CSV User Import plugin to do the job. From the Plugin Directory:

[CSV User Import] allows you to import a list of users taken from an uploaded CSV file. It will add users with basic information, including firstname, lastname, username, password and email address. Each user who is added will be a ‘subscriber’ by default, and be able to login to your site.

Exactly what we want. And there’s only one screen in the Admin, so everything is self-explanatory:

Find this screen under Users > Import. Before you select your CSV file and import your new users, a few things to keep in mind:

• WordPress stores user data in two tables, users and usermeta — backup these two tables individually before importing any data
• The imported CSV data will populate only the users table
• Depending on your server settings, you may need to break up the CSV file into smaller files before import
• If you’ve changed the default table prefix, you’ll need to edit a few lines in the plugin

Once everything is all set, import your data and check out your new users in the Admin. Make sure they have the correct permissions, and scan the columns for any missing usernames or email info. If you see any errors, you can either fix them manually or restore your backup and try again.

Again, this is the technique I used recently to import and register several thousand users into WordPress. I’ll try to help with questions, but Excel is certainly not my specialty ;)

Update Nov 22: I have found that this plugin imports the user email with newline/return characters appended to the address. To remove these characters, run the following SQL queries:

UPDATE wp_users SET user_email = Replace(user_email, char(13), '') ;
UPDATE wp_users SET user_email = Replace(user_email, char(10), '') ;

About the Author

Jeff Starr = Fullstack Developer. Book Author. Teacher. Human Being.