Date format for text file import

[PatrickSumner]

I'm in the midst of creating a text file to use with impex to import users from a custom BB. Two items I have for each user are the date of the lastpost and the date on which they joined. I want to import this information to VB 3.07 using the text file option in impex. However, I notice columns in the user table called lastpost and joindate, are apparently using elapsed time since Jan 1st 1971.

Do I need to convert my dates from YYYYMMDD to seconds elapsed since 01-01-1971 before writing them to a text file?

[Steve Machol]

The dateline fields in the vB tables are in Unix timestamp format, which is the number of seconds since midnight Jan. 1, 1970. You can use this online tool to convert Unix Timestamps to date and time:

http://www.onlineconversion.com/unix_time.htm

[Jerry]

The joindate currently is set to the time of the import by the text file importer, I would have to modify it to make the joindate another variable.

[PatrickSumner]

Jerry,


Is there any documentation available that describes what user attributes can be imported from a text file? I'm about to finish the script that dumps my current user data to a text file. I'm pulling out the following. I've renamed some columns to match up with the VB naming conventions. I've already created the groups within the VB install to match up with my current BB.

passwd varchar(6) [not encrypted- plain text]
username varchar(15)
email varchar(42)
lastpost date
postCount smallint(5) UNSIGNED
joindate date
groupVarA enum('yellow', 'brown', 'clear')
groupVarB enum('novice', 'intermediate', 'advanced')
groupVarC enum('can_edit', 'cant_edit')
location varchar(42)
biography text
browser varchar(120) [$HTTP_USER_AGENT]
isp varchar(80) [reverse DNS lookup of IP address they signed up with]
ipaddress varchar(16)

[PatrickSumner]

I can update the tables with a script to set the number of posts, joindate, lastpost date and ip address by setting the following fields in user where username='this user'

posts
joindate (Unix time stamp of date joined)
lastpost (Unix time stamp of last post)
ipaddress


I couldn't find a location or biography column in any of the user tables. Are they perhaps two of the "field" columns in userfield?

I don't think VB users HTTP_USER_AGENT anywhere, so I may just throw this information in a new table for storage in case I hack something to use it later. Let me know if there's an appropriate column in some table for this.

Thanks...

[Jerry]

Is there any documentation available that describes what user attributes can be imported from a text file?

In the importer itself; userid, username, password, email address (mandatory).

I can update the tables with a script to set the number of posts, joindate, lastpost date and ip address by setting the following fields in user where username='this user'

You could, though using :

UPDATE SET X=Y WHERE importuserid=Z; 

Would be better.

I've already created the groups within the VB install to match up with my current BB.

I would suggest importing them in batchs of users per user group them moving them into the correct group, then importing the next etc.

I couldn't find a location or biography column in any of the user tables. Are they perhaps two of the "field" columns in userfield?

There is userfield and usertextfield depending on if it is a default or custom field.

I don't think VB users HTTP_USER_AGENT anywhere

That would be an example of a custom field.

[Jerry]

Might be eaiser to go straight from the origional dB to vB via impex opposed to dumping out a file and then having to add all the other data.

Is the origional database MySQL ?

[PatrickSumner]

I would suggest importing them in batchs of users per user group them moving them into the correct group, then importing the next etc.

I couldn't find a way to move the one group to another using canned stuff. Is this something that can be done only user-by-user? Looks like I could just update the user table changing the usergroupid for all the newly imported members. Is that correct?

Might be eaiser to go straight from the origional dB to vB via impex opposed to dumping out a file and then having to add all the other data.

Is the origional database MySQL ?

Yes, it's in a data base. Is there a generic data base import option that will transfer more than just id, username, email, and password? I didn't see anything that looked generic other than the txt_file option.

Thanks

[Jerry]

I couldn't find a way to move the one group to another using canned stuff. Is this something that can be done only user-by-user? Looks like I could just update the user table changing the usergroupid for all the newly imported members. Is that correct?

Basically, yes. If you create the new group (say id 10) import all the users into the default group (say id 15) then run the SQL :

UPDATE user SET usergroupid = 15 WHERE usergroupid = 10;

Yes, it's in a data base. Is there a generic data base import option that will transfer more than just id, username, email, and password? I didn't see anything that looked generic other than the txt_file option.

Thanks

There isn't a default database importer yet, though I am looking at a way of trying to do that at the moment.

Though if you want to post the schema of the user table, I'll show you how to use the ImpEx API to create one for yourself.

[PatrickSumner]

All member data is in one table named member
id smallint(6) UNSIGNED ZEROFILL No auto_increment
passwd varchar(6) Not null
username varchar(15) Not null
email varchar(42) Not null
lastpost date Yes NULL
postCount smallint(5) UNSIGNED Yes 0
memberSince date Yes NULL
groupVarA enum('yellow', 'brown', 'clear') Yes NULL
groupVarB enum('novice', 'intermediate', 'advanced') Yes NULL
groupVarC enum('can_edit', 'cant_edit') Yes can_edit
location varchar(42) Yes NULL
description text Yes NULL
browser varchar(120) Yes NULL
isp varchar(80) Yes NULL
ip varchar(16) Yes NULL

passwd is plain text.

memberSince corresponds to your joindate and is YYYYMMDD date format. I've already written a perl script to convert this and lastpost to Unix time stamp so I can convert it prior to running any VB import scripts and alter these columns in the table to integer type.

location is where the person lives and can be null if they don't choose to state it. description corresponds to VB biography. Browser is HTTP_USER_AGENT they registered with. I use it as part of a troll control system. isp is a reverse dns lookup run on the ip address they registered with. ip is the ip address.

Thanks Jerry, I appreciate the help.

[Jerry]

Do the memebers have to be in one of each of the following or can they be NULLs ?

groupVarA enum('yellow', 'brown', 'clear') Yes NULL
groupVarB enum('novice', 'intermediate', 'advanced') Yes NULL
groupVarC enum('can_edit', 'cant_edit') Yes can_edit


i.e.

A yellow-novice-can_edit, or can a user be a yellow-NULL-NULL or a NULL-NULL-NULL ?

[Jerry]

http://www.vbulletin.com/forum/showthread.php?p=822785#post822785

[PatrickSumner]

Do the memebers have to be in one of each of the following or can they be NULLs ?

groupVarA enum('yellow', 'brown', 'clear') Yes NULL
groupVarB enum('novice', 'intermediate', 'advanced') Yes NULL
groupVarC enum('can_edit', 'cant_edit') Yes can_edit

i.e.

A yellow-novice-can_edit, or can a user be a yellow-NULL-NULL or a NULL-NULL-NULL ?

Null for groupVarB or C would be fine. I can easily edit them in later since virtually everyone falls into one of those sub-groups. yellow/brown/clear-NULL-NULL is fine.

[Jerry]

Null for groupVarB or C would be fine. I can easily edit them in later since virtually everyone falls into one of those sub-groups. yellow/brown/clear-NULL-NULL is fine.

Well either way the import, takes groupVarA as the primary and then adds members to 2nd or 3rd depending on if they are or not.

[Jerry]

http://www.vbulletin.com/forum/showt...785#post822785

The importer here was built a a demo from your table, that should do what you are after.