HOW-TO: Import and Export Data Using OJS

[jlaherty]

Hello!
Is appendix B of this file still available somewhere online and where? I'm curious to see the sample file for importing users and don't have access to the files
Thank you!
Jennifer

[jmacgreg]

Hi Jennifer,

There's a sample file included with the plugin -- take a look at plugins/importexport/users/sample.xml.

Cheers,
James

[netfunda]

I have created a tool to generate XML that can be further used to import data into OJS 2.2. Please go to http://www.infofacility.com/tools/xmlbuilder/

Thanks,
Rahman

[thebigchuckster]

what rules does OJS refer to when generating usernames from imported data?

I've got a largish file (~1200 names) to load but just my test file of 20 names produces usernames of user2, user3, etc until user11 when it stops and halts with a "DB error:column 'first_name' cannot be null".

Would spaces preceeding the name bork the process?

[jmacgreg]

Hi thebigchuckster,

I can't replicate this, even trying with spaces in various locations. What's the first_name value for user 11 in your sample XML file?

Cheers,
James

[thebigchuckster]

hi James,

many thanks for looking into my query.

I had spaces preceeding/postceeding (?) the data for each entity. When I ripped the data out of excel as a CSV i forgot about the space char i had used as a delimiter. In hindsight, I should have used another char and globally zapped it.

fwiw: I setup the XML file in XLS, inserted columns before/after each entity to hold the tags, then exported as a CSV and am polishing it in XML CopyEditor. I do have some high-order chars buried in it and am trying to spot/replace them.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE users PUBLIC "-//PKP/OJS Users XML//EN" "http://pkp.sfu.ca/ojs/dtds/users.dtd">
<users>
...
<user> <salutation> Dr </salutation> <first_name> Lynn </first_name> <last_name> SURNAME </last_name> <biography locale="en_US"> Queensland University of Technology </biography> <email> l.SURNAME@DOMAIN.edu.au </email> <interests locale="en_US"> Change Management; Organisational Learning; </interests> <mailing_address> </mailing_address> <role type="reviewer"></role> </user>
</users>

(personal details are obviously edited for privacy, some records have a mailing address entity hence the empty entity in this example)

I'm going to try another set of names with the erroneous spaces removed to see if that stops things. I've noticed after 2 attempts (2nd was ~20 names) that it appears to stop after 11 users.

setup details:
OJS instal 2.2.3.0 (with some minor local hacks)
OS platform Linux
PHP version 4.3.9
Apache version Apache
Database driver mysql
Database server version 4.1.22


cheers and thanks,
Charles

edit: i was wrong, i found the user and user1 accounts so my claim that "it doesn't create a user1" is bogus

[thebigchuckster]

I'm no longer getting errors with a small upload file (2 sample names) but am still getting 'user2' or 'user3' as the infered username. I think the spaces were borking things.

sample upload:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE users PUBLIC "-//PKP/OJS Users XML//EN" "http://pkp.sfu.ca/ojs/dtds/users.dtd">
<users>
<user>
<salutation>Dr</salutation>
<first_name>TestUser1FirstName</first_name>
<last_name>TestUser1LastName</last_name>
<biography locale="en_US"> Bond University </biography>
<email>tryoutuser1@ojsdev.org.au</email>
<interests locale="en_US">Performance Management &amp; Evaluation; Strategy Implementation; Cost Management </interests>
<mailing_address> </mailing_address>
<role type="reviewer"></role>
</user>
<user>
<salutation>Dr.</salutation>
<first_name>TestUser2FirstName</first_name>
<last_name>TestUser2LastName</last_name>
<biography locale="en_US">Bond University </biography>
<email>tryoutuser2@ojsdev.org.au</email>
<interests locale="en_US">Performance Management &amp; Evaluation; Strategy Implementation; Cost Management </interests>
<mailing_address> </mailing_address>
<role type="reviewer"></role>
</user>
</users>

For the time being I'll try to parse out the username in the email address and use that for the proposed system username.


edit: that's a fail. Username field (e.g., adding <username>tryoutuser1</username>) is being ignored and the system is assigning 'user1' etc. when creating the new account.


edit2: username set correctly if I include a password entity (e.g. <password>ojsojs</password>). Will try a larger file with more variation in details to see if my day has started to turn around (was dreading the thought of manually adding 1200 accounts)


edit3: I tried a bigger list of 300 names but it failed after importing 10 names, halting with a "DB error:column 'first_name' cannot be null" (note: is displayed as plain text, no template/formatting accompanies the error message).

It does display the entire confirmation page correctly so it's successfully parsing the uploaded XML file. The failure occurs after that.

WIll see if there's something in the server logs.

[thebigchuckster]

minor update - nothing meaningful in the logs (actually nothing at all from this subdomain so i have another issue to track down).

It happily imports 9 users, anything more and I get the "DB error:column 'first_name' cannot be null" message and it only imports the first nine names. At least I'm not getting 'user1' usernames.

I'm wondering whether it's failing because the XML file only contains the entities that I have data for? Or that PHP on this server has been hobbled in regards to creating large arrays? Ther later seems unlikely as when you upload an XML file with a larger number of names it displays them all in the 1st screen but then fails when you hit the import button.

For the time being i'm creting and importing a large number of XML files (oh the joy ) but I'll try to lodge a bug notice when I'm finished.

cheers,
Charles

[asmecher]

Hi Charles,

Have you validated your XML against the DTD?

Regards,
Alec Smecher
Public Knowledge Project Team

[thebigchuckster]

hi Alec,

just tried - it's failing as i'm only listing the entities that I have data for.

... Element user content does not follow the DTD, expecting (username? , password? , salutation? , first_name , middle_name? , last_name , initials? , gender? , affiliation? , signature? , email , url? , phone? , fax? , mailing_address? , country? , interests* , biography* , locales? , role*), got (salutation first_name last_name biography email username password interests mailing_address role)

I was hoping that it would be tolerant of me skipping over non-mandatory entities.

laziness = failure ?

note: am validating against http://pkp.sfu.ca/ojs/dtds/users.dtd

cheers,
Charles.

[asmecher]

Hi Charles,

Sorry, but the error message you're seeing is because of the missing required entities -- if you like, you can use placeholders (you could, for example, trick the system into accepting a blank space by using something like <element><![CDATA[ ]></element>).

Regards,
Alec Smecher
Public Knowledge Project Team

[thebigchuckster]

hi Alec,

oh well ... next time i'll put more thought into how i transform XLS data into XML. Am over halfway thru my list so I'll keep plowing on.

while i think about it - does the order of the entities matter? I know it would if it was highly structured/complex DTD but the uses.dtd is flat.

cheers and thanks,
Charles.

[asmecher]

Hi Charles,

This particular parser doesn't care what order the elements come in, as long as they're nested properly -- but in general it's a good idea to make sure you follow the DTD, or the behavior won't necessarily be predictable.

Regards,
Alec Smecher
Public Knowledge Project Team

[treitzel]

Over the past 3 years, our journals have transitioned away from OJS to another system, and for over a year none of our editors have used the system. Now I would like to export and archive our journals' OJS peer-review information (eg, reviewer names and reviews for each ms submission, editor decisions and dates, revision history, etc), but it seems that the export documentation and plugins are aimed only at exporting published material from OJS. Can peer-review data be exported from OJS, and if so, how?

[jmacgreg]

Hi Treitzel,

I'm afraid the only way to export that information is through the database itself, eg. using phpMyAdmin -- none of our tools currently support anything more than what you have described below.

Cheers,
James