E2V Self Hosted Migration

To migrate data from an E2V "Self Hosted" solution, you'll need access to your database server.

This guide assumes you're using MySQL, PerconaDB or MariaDB, and you have unrestricted access to your server.

If you run into any problems at all, please get in touch by sending an in-world IM to CasperHelp Resident.

Migrating sales data

The following MySQL command will create a file compatible with our importer. Be sure to replace /tmp/sales.csv with the location that you want to output the file to.

SELECT 'date', 'custname', 'custkey', 'item', 'region', 'cost', 'paytype', 'recipname', 'recipkey' UNION ALL SELECT FROM_UNIXTIME(`date`), `custname`, `custkey`, `item`, `region`, `cost`, `paytype`, IF (`recipname` = '', `custname`, `recipname`), IF (`recipkey` = '', `custkey`, `recipkey`) FROM `sales` INTO OUTFILE '/tmp/sales.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

If you have multiple stores, add WHERE `storeid` = (your store id), like so:

SELECT 'date', 'custname', 'custkey', 'item', 'region', 'cost', 'paytype', 'recipname', 'recipkey' UNION ALL SELECT FROM_UNIXTIME(`date`), `custname`, `custkey`, `item`, `region`, `cost`, `paytype`, IF (`recipname` = '', `custname`, `recipname`), IF (`recipkey` = '', `custkey`, `recipkey`) FROM `sales` WHERE `storeid` = 1 INTO OUTFILE '/tmp/sales.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

To import the file, go here and click "Start new import", then "Upload CSV". Then select the file you wish to upload.

Then, choose "Sales Data" and hit Next, then choose "Those are field labels!" and hit next again.

Match the fields as follows:

date -> Date/Time -> YYYY-MM-DD (ISO) --> Second Life Time custname -> Purchaser Name custkey -> Purchaser UUID item -> Product Name (note you may have to deselect this from one of the other fields before it is available). region -> Location cost -> Gross paytype -> -- IGNORE -- recipname -> Recipient Name recipkey -> Recipient UUID

Then hit "Upload Now". The upload may take some time, please be patient!

Migrating store credit

The following MySQL command will create a file compatible with our importer. Be sure to replace /tmp/credits.csv with the location that you want to output the file to.

SELECT 'custkey', 'custname', 'balance' UNION ALL SELECT `custkey`, `custname`, `balance` FROM `credits` INTO OUTFILE '/tmp/credits.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

If you have multiple stores, add WHERE `storeid` = (your store id), like so:

SELECT 'custkey', 'custname', 'balance' UNION ALL SELECT `custkey`, `custname`, `balance` FROM `credits` WHERE `storeid` = 1 INTO OUTFILE '/tmp/credits.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

To import the file, go here and click "Start new import", then "Upload CSV". Then select the file you wish to upload.

Then, choose "Store Credit" and hit Next, then choose "Those are field labels!" and hit next again.

Match the fields as follows:

custkey -> Purchaser UUID cutname -> Purchaser Name balance -> Balance

Then select one of the options ("Add" is recommended), and choose Upload Now. The upload may take some time, please be patient!

Important: By default, CasperVend refunds customer balances on purchase - this is usually unfamiliar to E2V users. To fix this, please go to the Customers tab and unselect that option.