Import users to Vanilla

DenisS

I have been trying for about 3 days to amalgamate 3 forums, just the user files only into one user file. I have exported in SQL, CSV EXL.csv and Open Office S/S. If I export and then import the original file without altering anything in CSV & Exl it don't work. In SQL yes every time Open Office 90% of the time. I have about 2000+ users to put in the one forum which has about 350 members. I can make one file easily in all programs except SQL. I cannot import any of the CSV EXL.csv and Open Office S/S files if I append the data. I make sure all the fields are the same. Is there any way of importing direct into Vanilla 2.0.18.8. Any ideas?

R_J

Do you also try to import the UserID? That could be a problem.

I would try the following:
1. export as .csv
2. rename it to something.murks so that smartass excel will not try to auto detect the columns and change their values
3. open in excel and convert text to columns and beware that every column is treated as text
4. delete first column (containing UserID)
5. save as .csv
6. import to db

DenisS

Sorry No3 do you mean convert all columns to text?

Linc

"It didn't work". Can you elaborate a bit on what specific trouble you ran into?

Doing a Save As to CSV should give you a simple text file that any SQL interface can import fairly simply.

Do the names or emails have punctuation / non-latin encoding in them? You might open the CSV file in a text editor and give it a scan for obvious problems.

R_J

If you open a textfile in excel that has no known file type, excel normally shows all data in one column.
You'll have to convert the text to columns with a built in excel function (I don't know what it is exactly called but something like text to columns should be somewhere).
If you mark the first column with all data in it, choose text to columns, there will be a dialogue that let's you choose the text separator and later on the format of the columns. Mark all columns (simple by marking the first column, scroll to the right and shift+click on the last column and choose "text" as format. Otherwise a username like "007" will be converted to "7"

DenisS

Thanks, I'll try some of these today see how i get on. I screen shot the errors

DenisS

SQL files no problem but you can't merge these, can you?
I tried everything I know today and still cannot import an appended file, all files type's that you can produce with/ without headers. The most common fault is ( Import csv )
" Invalid column count in CSV input on line 1." I have 45 columns the input files has 45 columns. You would think it would be an easy task to append two Vanilla forums user's but no!
A file import Plugin might be a good idea, not that i can do one.
Some of the errors and file structure on attached do.

R_J

@DenisS, @whu606
Not sure but I think it would be better to delete that file.

R_J

That looks really buggy:

INSERT INTO `hannahas_vnl3`.`GDN_User` (`A`, `B`, `C`, `D`, `E`, `F`, `G`, `H`, `I`, `J`, `K`, `L`, `M`, `N`, `O`, `P`, `Q`, `R`, `S`, `T`, `U`, `V`, `W`, `X`, `Y`, `Z`, `AA`, `AB`, `AC`, `AD`, `AE`, `AF`, `AG`, `AH`, `AI`, `AJ`, `AK`, `AL`, `AM`, `AN`, `AO`, `AP`, `AQ`, `AR`, `AS`) VALUES ('UserID', 'Name', 'Password', 'HashMethod', 'Photo', 'About', 'Email', 'ShowEmail', 'Gender', 'CountVisits', 'CountInvitations', 'CountNotifications', 'InviteUserID', 'DiscoveryText', 'Preferences', 'Permissions', 'Attributes', 'DateSetInvitations', 'DateOfBirth', 'DateFirstVisit', 'DateLastActive', 'LastIPAddress', 'DateInserted', 'InsertIPAddress', 'DateUpdated', 'UpdateIPAddress', 'HourOffset', 'Score', 'Admin', 'Banned', 'Deleted', 'CountUnreadConversations', 'CountDiscussions', 'CountUnreadDiscussions', 'CountComments', 'CountDrafts', 'CountBookmarks', 'DateAllViewed', 'prosongurl', 'ReceivedThankCount', 'CustomPermissions', 'CountProfilevisitors', 'CountBadges', 'RankID', 'RankProgression'),

It looks as if there is one line to many in the file that you try to import, containing A, B, C, D, E, etc.
I wouldn't use Excel files for any export or import job (although I adore Excel) but always .csv files. You can look with simple notepad at them and see if they are in a total mess or if they look sane.

UnderDog

@DenisS after this is over, make sure you change your password. All those errors are a bit strange. Make sure you know what you are doing before you import those users.

Let's say there are 10 columns in the User table : ('UserID', 'Name', 'Password', 'HashMethod', 'Photo', 'About', 'Email', 'ShowEmail', 'Gender', 'CountVisits')

Then the insert statement is as follows:

INSERT INTO hannahas_vnl3.GDN_User ('UserID', 'Name', 'Password', 'HashMethod', 'Photo', 'About', 'Email', 'ShowEmail', 'Gender', 'CountVisits')
VALUES
(1, "UserName", "$%^%%^$#R%$%TR", 'Vanilla', NULL, NULL, "denis@spammers.com", 1, "M", 123)

You see ... The Values match the column names. So no more (A, B, C, D, E, F, G), that's just nonsense.

Export a user table from a working Vanilla install. You will see an example of what you need.

Whenever you make CSV files, open them with text editors. UltraEdit is a good editor to try.

If you look in your DocX file, the top information, where you show your gmail address and your password (great!) is actual good information. It's the exact example of how all your user rows should look like (with their own information of course!)

R_J

My concern is the column UserID. If I merge 3 forums, I will have three times UserID 1, three times UserID 2,...
UserID is unique, so I'd say that must fail.

hgtonight

If you are carrying the discussions/comments/conversations over from 3 forums, you are going to need to massage the data to account for the three different ID sets as @R_J points out.

Depending on the size of these forums, you could theoretically just bump ALL ids up 1000 or 2000 (assuming you had less that 1000 of each type of data (users, discussions, comments, conversations, messages, etc.)

UnderDog

If you're merging Users only, then keep the original forum with all your users, and make the column UserID NULL while importing, so the new userID's get generated by Auto_increment Primary key.

DenisS

ok Underdog, that seems to make sense, thanks. I'll let you know. I take it you mean with importing the sql file?

peregrine

As long as you back up your database prior to doing inport. you could try what works for you and then report back. I would think it could be done with a properly creating csv file or sql file. as long as columns are correct.

with sql file you have a bit more control though as far as insertion.

whenever I export to csv and vice versa, I use | pipes for field delimiters and no quotes of any kind.

DenisS

Yes i have tried almost all combinations of file I have removed all duplicates, re-numbered all imported users. I'll try again today. Thanks everybody for help on a problem that should have been an easy exercise but has not turned out that way.

peregrine

Thanks everybody for help on a problem that should have been an easy exercise but has not turned out that way.

you must be a good friend of murphy, as in Murphy's Law

http://en.wikipedia.org/wiki/Murphy's_law‎

DenisS

Another day did have a little success in merging two SQL files, then it all went to pot again, I think I'll give up for a day and come back fresh.

DenisS

All I'm trying to do is get all the users from 3 small Forums about 100 to 200 user into the one forum that is having some success. Nothing else except the user the basic user info.
Underdog: I did try your suggestion: >> Tried but it overwrote the original file with NULL & Auto In ticked.
With just Null ticked and auto turned off it still overwrote and the SQL file reset on import to Auto on and Null off. I did turn it off in the SQL file but made no difference.
peregrine: tried yours as well no success.

DenisS

Nothing ever come easy: ( To me ) peregrine quote: >you must be a good friend of murphy, as in Murphy's Law

x00

if you have the correct data and a text editor that supports regular expression / replace you can make valid sql, or csv should work. it really down to you to understand the data and format it correctly. Don't expect some software to know exactly what you wan to do with it. I also thinks the errors are fairly self explanatory, you need to work through it debug, there no simpler way. Don't do this on a production database create a clone.

Note if new user are being added to the live forums. You may get users in conflict when you overwritten then databases, this could be solved by setting the auto index value a bit higher on the import database. Then when you have migrated you can redo the index column.