Spring Sale! Save 30% on all books w/ code: PLANET24
Web Dev + WordPress + Security

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:

[ Screenshot: User Data in CSV Format ]

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:

[ Screenshot: User Data in CSV Format ]

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:

[ Screenshot: CSV User Import Plugin ]

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 = Web Developer. Security Specialist. WordPress Buff.
SAC Pro: Unlimited chats.

12 responses to “Importing WordPress Users via CSV Files”

  1. Thomas Scholz 2010/11/04 5:00 pm

    I have done this so often … and every time I learned something new. :)

    One note on custom user meta data: You have to register them before you import your data or WordPress will just drop these fields.

    Disable all plugins which hook into any ‘user’ action or filter. To be sure just disable all plugins.

  2. Good tips as always, Thomas – thanks for sharing. This particular import technique doesn’t fiddle with any usermeta data, only the essentials required to populate the users table enough to create registered users. I’m sure you know this, but I just want to clarify for anyone who is working thru the tutorial.

  3. Gregory Luce 2010/11/06 2:37 am

    This is fantastic, and I thought I was already pretty decent with Excel. This may be a question for the plug-in, but after the import does it auto-send the username and password?

  4. Hi Gregory, the plugin only sends emails, but there are a number of membership-type plugins that look like they will send username and password to registered users. The Mass Mail plugin works well for sending email to all users, but it doesn’t include any user information.

  5. hey jeff,
    thanks for your tutorial i followed above steps and voyla.. it worked as you said.

  6. When you’re creating usernames, after you’ve pasted in the formula, make sure your mouse appears as a *black* plus sign before dragging it down the column. There’s also a white plus sign. You can find the black one by selecting the field and moving your mouse to its lower-right corner. I have no experience with Excel and got hung up on that for a while.

  7. Hi, thanks for tutorial – how do I import the data under the correct author name, what is happening with me is all the posts are placed under admin which is what I logged in as, instead of being allocated for each registered user I have on my site.

    I added user ID in the CSV but it is not recognising this as the author

  8. Resolved the issue: csv_post_author

  9. Thanks for the post. This an extremely useful tip, which I will be using on wordpress sites in the future.

  10. Thank you so much for all the codes & instructions.. it helped me a lot. :)

  11. Jason Touw 2012/09/14 2:21 pm

    How do i use this but not have teh system automatically email the user their login information? We are doing a big unveiling of our membership only site and only want them to receive their username and password form us in our official email format.

    • Jeff Starr 2012/09/14 2:32 pm

      I think that the plugin does not send an email to the imported users, so you’re probably safe — but I would check by importing a few test users/emails and seeing if anything is sent.

Comments are closed for this post. Something to add? Let me know.
Welcome
Perishable Press is operated by Jeff Starr, a professional web developer and book author with two decades of experience. Here you will find posts about web development, WordPress, security, and more »
The Tao of WordPress: Master the art of WordPress.
Thoughts
I live right next door to the absolute loudest car in town. And the owner loves to drive it.
8G Firewall now out of beta testing, ready for use on production sites.
It's all about that ad revenue baby.
Note to self: encrypting 500 GB of data on my iMac takes around 8 hours.
Getting back into things after a bit of a break. Currently 7° F outside. Chillz.
2024 is going to make 2020 look like a vacation. Prepare accordingly.
First snow of the year :)
Newsletter
Get news, updates, deals & tips via email.
Email kept private. Easy unsubscribe anytime.