Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Import users to Vanilla

2»

Comments

  • peregrineperegrine MVP
    edited February 2014

    there are hundreds of ways to do it. you just need to focus and pay attention for each step along the way and see if what you think you have it at each step is what you need and have.
    fould one step up and the rest will of course be wrong. so do a step and verify.

    you could do what x00 suggests

    and here is an alternative way.

    DO NOT use word or DOC files, easiest to use a straight text editor if modifying a csv or sql file. e.g. notepad++

    look at live database that you want to export into.

    • 1

    go onto phpmyadmin and look for the largest UserID.

    if you see the largest userID as 2034

    then you could add users starting with 3000.

    • 2
      go to the other database you want to merge into the live database

    export the user table into csv format.
    load the csv into a spreadsheet.

    you could delete all columns except the following (these are the minimum)
    the photo field could possibly overlap someone elses if they created photo at some time.
    other fields like comment count and count discussions would be wrong so need to import,
    or use the fileds underdog suggested.

    UserID,Name,DateLastActive,Password,Email,DateInserted

    put 3000 in the userID column in the first user and drag the mouse down the column, which should increment the number for each row.

    the order of the field columns doesn't matter as long as the

    UserID,Name,DateLastActive,Password,Email,DateInserted

    matches the correct fields in the rows that follow it.

    then export the csv with comma delimited fields and " as the character to place around fields.
    you should now have

    you should have something like this first field is UserID followed by other fields.
    each field separated by a comma and a " around each field.

    3000,"test","2010-12-26 21:51:00","[need-new-pass-hash]","test@test.com","2009-12-11 18:24:45"

    put a ( in front of each record and and ), at the end of each record.

    you could then preface it with

    the last line needs );

    -5 so it will look like this and rename it to userimport.sql

    INSERT INTO `User` (UserID,Name,DateLastActive,Password,Email,DateInserted)
    VALUES
     (3000,"testA","2010-12-26 21:51:00","[need-new-pass-hash]","test@test.com","2009-12-11 18:24:45"),
     (3001,"testB","2010-12-26 21:51:00","[need-new-pass-hash]","test@test.com","2009-12-11 18:24:45");
    

    go to the user table in your database and import the userimport.sql file/


    next you need to add users to the userrole table.

    every user added must have a role.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • DenisSDenisS My brain hurts Buriram ✭✭

    Ok i see what your doing I will try this weekend sometime and let you know, thanks.

  • DenisSDenisS My brain hurts Buriram ✭✭

    I would like to thank everybody for their help and advise. I have used all your advise to slowly get my head around the problem. 1: Was to make sure all the field were the exactly same.
    2: the SQL file turned out to be the one i could work with. 3: Do a custom download ( data only) with headers. 4: Downloaded a very good Text editor from http://notepad-plus-plus.org/ = Notepad++ I am having to re-number manualy at the moment but I have just combined 2000 members details into a test SQL database. So i think from now on I can do what I wanted. :)

  • DenisSDenisS My brain hurts Buriram ✭✭

    Ok as normal, one solved problem leads to another problem, Does anyone know where Users list gets this from:
    Search for users by name or enter the name of a role to see all users with that role.
    140 user(s) found. The new problem is i now have 911 user in this forum but it only shows 140 Even if I search a name the is in user data base it's says "user not

  • R_JR_J Ex-Fanboy Munich Admin

    Compare (with phpmyadmin) the record of one user that can be found and one user that can not be found

  • DenisS you are the one closest to the problem, it is near impossible to diagnose problem like through little sound bites like this. Think about it realisticly, would you be able to guess from this? my guess is the data is not 100% as is should be.

    Any queries data has to fit the criteria of the data model, you would need to look at he queries themselves by debugging, then you can look at what is wrong try to make corrections, then run the queries again.

    If you don't know MySql you need to learn it fully, then you need to understand the data model/schema of the app to make sure you have inputted data as is should be.

    grep is your friend.

  • @DenisS said:
    Ok as normal, one solved problem leads to another problem, Does anyone know where Users list gets this from:
    Search for users by name or enter the name of a role to see all users with that role.
    140 user(s) found. The new problem is i now have 911 user in this forum but it only shows 140 Even if I search a name the is in user data base it's says "user not

    I assume you did your merge. you may have noticed in my comment

    http://vanillaforums.org/discussion/comment/202426/#Comment_202426

    I mentioned ...
    next you need to add users to the userrole table.

    every user added must have a role.

    did you do this, if not that could be your problem

    what you can do is select the UseIid and Name from the user table.

    just export the current user table to a user.csv. the first two columns will be user and name

    next export userrole table and you will see two columns UserId and RoleID

    every non-deleted user in the user table must be in the UserRole Table, so you need to add them if you did indeed merge the user tables from two forums.

    UserID reflects the same UserID in both the the User Table and the UserRole Table.

    some UserID's may be in the UserRole Table multiple times if they have more than one role.

    and every valid User MUST have at least one Entry in the UserRole Table.

    take a brief look here
    http://www.w3schools.com/sql/default.asp
    http://www.tutorialspoint.com/sql/

    to get some knowledge. it will help you, I guarantee it.

    also, we can't guess what you did? we know what your ultimate goal was, bur we still don't know the steps you last did. It would aid in getting a suitable answer.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • UnderDogUnderDog MVP
    edited February 2014

    @DenisS Whatever you do ... backup what you now have. It's a good situation to start from (and learn from). Turn on the Debugging modus, so you can see the SQL statements that are executed. Copy and paste the statements regarding users and userroles in your notepad and then turn off the debug modus again.

    So when you look at your users you see 140 users that have a role. Great! Those were the 140 original users from when you started to import those other users. Now you need to add 911 - 140 users is ... x amount of roles somewhere (don't feel like doing math at the moment.

    Figure out which users do not have a role (that's an SQL statement, it should be copied / pasted in your notepad). Figure out which users do not have a role (that's an SQL statement, see the tutorial). Insert role for each user that does not have a role (that's an SQL statement, see the tutorial)

    __Follow peregrine's advice. I'm just re-iterating what he said__.

    There was an error rendering this rich post.

  • LincLinc Detroit Admin

    You might also check /role/defaultroles (this is the URL to the page, it doesn't exist in the nav) to see what your Applicant role is set to. Any user in that role will not show up in your User list.

  • DenisSDenisS My brain hurts Buriram ✭✭

    I'm having a little break away at the moment and will resume when I return, thanks again for all the info , but one thing I'm sure off the User data correct and all have roles transfered with their full data. I'll just keep going down this learning curve when i get back.

  • DenisSDenisS My brain hurts Buriram ✭✭

    Success at last: I have managed to amalgamate all of my members into one Sql file and can import part of the full file at will. User & user roles with total info. I would never have been able to do without Open Office & the new text editor I down-loaded called notepad ++ great to use and flexible > www.notepad-plus-plus.org Thanks to everybody who helped me without your advise i would never been able to understand the constuction of files.

Sign In or Register to comment.