VirtueMart – User list import (EN)

Do you want to share your Knowledge Base, code strips or ask something about open source CMS Joomla and it's i-shop extension VirtueMart? All of you are welcome.

VirtueMart – User list import (EN)

Poslaťod Palo-admin » 18.11.2008, 19:37

I had to migrate users from old e-shop into VirtueMart. After short research on web I found tool for import Joomla users (userport, author: Parvus) but no one for VM.
After another research I found tables which must be filled, but also with main problem: How to generate "user_info_id", solved by this function:
md5( date_format( date_add( sysdate( ) , INTERVAL FLOOR( 1 + ( RAND( ) *998 ) ) MICROSECOND ) , "%Y%m%d%H%i%s%f" )

My “quickly prepared” solution use several steps and tools described below:
1) Joomla component – userport
2) MS Excel tool for user import from any user list source
3) MS Access query – for extract new user only (not assigned to VM shopper groups)
4) MS Excel tool for bulk MySQL query

- (author: Parvus),com_mtree/task,writereview/link_id,5430/Itemid,35/

!!! If there is a problem to intall direct on the web !!!
1) Install on localhost "c:\xampp...

2) Copy files "c:\xampp\htdocs\your_project\administrator\components\com_userport"
- via ftp (Tip: check files with current date)

3) Update Joomla db - table jos_components
(TP: see attached XLS, sheet "1_InsertInto_jos_components"

Administrator / Components / userport

1) Set parameters (e.g. fields separator: vertical bar)
!!! e-mail adresses must be Unique

2) Copy / Paste users – and run “Add and Update”

3) Check VirtueMart - Users tables:
t: _users, _vm_user_info, _vm_shopper_vendor_xref (via _vm_shopper_group)

*main user table: jos_users.
*shopper groups table: jos_vm_shopper_group (the shopper_group_id column).
*glue that holds them together: the jos_vm_shopper_vendor_xref.

???: Main problem: How to generate "user_info_id"
From the source code, this field is: md5( uniqid(_VIRTUEMART_SECRET ))

0) Update db - tbl "Components" (when userport must be installed on localhost )
see attached XLS, sheet "1_InsertInto_jos_components"

1) Prepare user list in Excel,
!!! – check, if e-mail addresses are unique
!!! – password should be min 5 characters

2) Joomla Administrator / Components / userport / Add&Update
!!! Set parameters first
from sheet "2_CustomerList ", clm "CSV_list" / Copy-Paste data into userport

3) Joomla Administrator / Components / virtuemart/ Users
(only Check if you can see new users)

4) MS Access: select new user only (… if you have connected MySQL db via MySQL ODBC)
SELECT jos_users.*
FROM jos_users LEFT JOIN jos_vm_user_info ON = jos_vm_user_info.user_id
WHERE (((jos_vm_user_info.user_id) Is Null));

5) Copy-Paste into MS-Excel
into sheet "3_NewUsers" clm’s "B:N" / Copy-Paste

6) Copy-Paste from Excel into
... clm "vm_shopper_vendor_xref" / and run

7) Copy-Paste from Excel into
... clm "vm_user_info" / and run

MS EXCEL - Before prepare query fo MySQL check ID’s for shopper groups in VM (e.g. '1', '8') first !!!
INSERT INTO `xxx_yourdb`.`jos_vm_shopper_vendor_xref` (`user_id`, `vendor_id`, `shopper_group_id`) VALUES ('99', '1', '8');
MS Excel tool for user list import
(8.25 KiB) 4129 krát
Administrátor stránky
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Späť na Joomla + VirtueMart "affairs"

Kto je on-line

Užívatelia prezerajúci fórum: Žiadny registrovaný užívateľ nie je prítomný a 1 hosť