I have created a plugin for import/export of User Code tables

[BikeMike]

Hi all,
I have some large user code tables, and maintaining these through the
web interface can be difficult. It is often easier to do the changes as
a "spreadsheet", particularly if getting mass updates from another system.

So I have created this plugin, with grammar and mapping for user code
tables from/to csv files.

Usage is simple, you just create a csv file with the name of the user
code table. It has up to 10 columns corresponding to leftcode,
rightcode, and attr1-8. (example included in plugin)

Put this file in botssys/infile/UserCodes and Bots engine will import it
to a corresponding table. Records are only updated or added, deletions
must still be done manually. You can use it to add just a few rows or
the whole table. Rows with no differences are not updated.

to export your table without importing anything create a single record
csv file containing only 2 blank fields...
--- begin file ---
"",""
--- end file ---

Once your updates are imported, the entire table is exported again to
botssys/outfile/UserCodes. You can then edit this file and repeat the
process as needed.

I have found Libre Office (formerly OpenOffice) best for editing csv
files, as MS Excel can mess up numeric columns, removing leading zeros etc.

--
Kind Regards,
Mike

[BikeMike]

Updated... I forgot to include the communicationscript for naming the
output file. Also channel deletes input file.

Kind Regards,
Mike

[BikeMike]

Also now added a routescript. This will automatically export all code
tables first run, so you don't have to create empty csv files to do
this. Once the csv files exist in outfile folder they are not
re-exported unless you delete them or put a new file in infile folder.

Kind Regards,
Mike

[BikeMike]

added column headings and the sample csv file was missing

[Harold DeWayne]

Mike... has this broken in v3.0?  I'm trying to use it and it appears to work  but I'm not finding the data in the tables when I look at them in the web interface.


Thanks for EVERYTHING.  I'm looking forward to using this somewhat extensively as I learn to use BOTS.

Harold.

[BikeMike]

Hi Harold,
It should still work, but I have made some changes since first creating this.
I will check and post the latest code.

[Harold DeWayne]

Thanks... waiting with baited breath (yeah, smells like fish I know...........)

HD

[BikeMike]

Hi Harold,
Try this... I think everything needed is there.
If you run the UserCodes route it should export all your user code tables as csv to botssys/usercodes/out.
You can edit and save to botssys/usercodes/in then run the route again to import the changes.
You need to create the empty folders first; the paths are set in the channels so can be changed.

Kind Regards,
Mike

[Harold DeWayne]

Sorry Mike... this one doesn't work either.  I've tried to make it work but it's failing (after restoring my system) with

• Backup plugin "/var/www/html/bots-3.0.0/bots/botssys/backup_plugin_20130501160259.zip" is written successful.
• Error reading plugin: "Error writing plugin to database. Nothing is written. Error: IntegrityError: channel.testpath may not be NULL ".

The original reads fine and after I correct the paths (I'm on a LAMP system) it gives one file in and one out but the output file looks empty.

Input file:

"Leftcode","Rightcode","Attr1","Attr2","Attr3","Attr4","Attr5","Attr6","Attr7","Attr8"
"Line01","This is myUserCodes test","Attribute 1","Attribute 2","Attribute 3","Attribute 4","Attribute 5","Attribute 6","Attribute 7","Attribute 8"
"Line02","import from infile/UserCodes","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"
"Line03","export to outfile/UserCodes","January","February","March","April","May","June","July","August"
"Line04","leftcode must be unique",,,,,,,,
"Line05","rightcode must not be empty",,,,,,,,
"Line06","csv data updates or adds to table",,,,,,,,
"Line07","deletes must be done in Bots GUI",,,,,,,,

Output file:
"Leftcode","Rightcode","Attr1","Attr2","Attr3","Attr4","Attr5","Attr6","Attr7","Attr8"

Harold.

[BikeMike]

Hi Harold,
sorry, not sure... it works for me :-/

So data from the csv is loaded into the code table ok?
But not exported again?

Note that the route does nothing if there is already an output file and no input file.
Deleting output should cause it to be recreated.
New input should import-export and overwrite previous output.

[Harold DeWayne]

Hi Mike...

I'm not sure what i did, but I got it working.   Thanks.

If you can explain how to create a plugin for me, I'll send you what I have... I know that there's a button on the web interface to do this with, but it doesn't appear to be very fine-grained... looks like it'll create a really big plugin.

Harold.

[BikeMike]

Hi Harold,
Glad you got it working.

The plugin creation includes everything, you can't select specific routes etc. You can edit the plugin once created and remove what you don't want from usersys etc.. The hardest part is editing the database configuration in botsindex.py in the plugin root folder.

Kind Regards,
Mike