How do I import issues and articles from spreadsheet?

asmecher · January 24, 2018, 8:30pm

The most recent releases of OJS require PHP5.6 at least, and my dev environment runs on PHP5.6 without problems. If you’re changing your PHP module configuration, have you restarted your web server afterwards?

Regards,
Alec Smecher
Public Knowledge Project Team

troelsk · January 24, 2018, 8:43pm

I’ve now managed to run your script on a php-server with version 7.0. Unfortunately, I just get another type of error, for some reason. What php version did you use originally, @ajnyga?

Fatal error: Uncaught Error: Class 'ZipArchive' not found in /home/isbuddet/public_html/troelskolln/ojs/tsvconverter/phpexcel/Classes/PHPExcel/Reader/Excel2007.php:94 Stack trace: #0 /home/isbuddet/public_html/troelskolln/ojs/tsvconverter/phpexcel/Classes/PHPExcel/IOFactory.php(268): PHPExcel_Reader_Excel2007->canRead('eksempel.xlsx') #1 /home/isbuddet/public_html/troelskolln/ojs/tsvconverter/convert.php(60): PHPExcel_IOFactory::createReaderForFile('eksempel.xlsx') #2 {main} thrown in /home/isbuddet/public_html/troelskolln/ojs/tsvconverter/phpexcel/Classes/PHPExcel/Reader/Excel2007.php on line 94

asmecher · January 24, 2018, 8:44pm

Hi @troelsk,

See e.g. php - Fatal error: Class 'ZipArchive' not found in - Stack Overflow for instructions on installing ZipArchive support. It’s available for all modern PHPs.

Regards,
Alec Smecher
Public Knowledge Project Team

troelsk · January 24, 2018, 8:45pm

Hi @asmecher. Well, I don’t really need OJS to be running for this, I just need to get the xml-files created, so that’s not a concern for me. But thanks for the tip about restarting the server.

ajnyga · January 25, 2018, 5:55am

Hi,
I used PHP 5.6, but it should work with PHP 7 as well. The problems you are experiencing are related to missing PHP extensions Alec also refererred to:
php_zip
php_xml
php_gd2

The PHPexcel library does mention that the zip extension is only needed if you use it with xlsx-files. So maybe you could test if a .xls files works?

troelsk · January 25, 2018, 6:19am

Thank you again (also to @asmecher)!

All the extensions should now be installed on a php 7.0 installation. I’ve also tried with an .xls-file. However, I now a get a similar, but slighly different error 10 lines later.

Fatal error: Uncaught Error: Call to undefined function mime_content_type() in /home/isbuddet/public_html/troelskolln/ojs/tsvconverter/convert.php:268 Stack trace: #0 {main} thrown in /home/isbuddet/public_html/troelskolln/ojs/tsvconverter/convert.php on line 268

Btw, I completely understand if you don’t have time to go through all this. I am very grateful, though. This is by far the closest I’ve been to a solution so far!

Oh, and here is a photo of my php-installation (via cPanel):

ajnyga · January 25, 2018, 6:47am

Hi,

You could try enabling fileinfo extension there and add the custom function I pasted in my earlier post?

Or, as second alternative, try to enable the mime_magic extension in cPanel. I have no good idea how you would do this, because I do not use cPanel myself.

troelsk · January 25, 2018, 7:06am

HALLELUJA! It works. Thank you so much

Now I just need to test the xml-file, and for some reason my OJS-installation is now broken, but that should be easy to fix (I get the error that I need to use a php version less than 7).

ajnyga · January 25, 2018, 7:38am

With PHP7, you probably need switch using mysqli. You can search the forum for details.

You can also try going back to PHP5 and enabling the fileinfo extension (and other needed extensions) with that version.

troelsk · January 25, 2018, 7:48am

That works, thanks. So the xml-file gives me this error:

Element '{http://pkp.sfu.ca}affiliation': This element is not expected. Expected is one of ( {http://pkp.sfu.ca}url, {http://pkp.sfu.ca}orcid, {http://pkp.sfu.ca}biography ).

Seems to me like it doesn’t recognise that element. Actually, I don’t even need have that data to upload, so I guess I could just remove it from the script.

ajnyga · January 25, 2018, 7:51am

Can you show me what the data looks like (you can send the xls to ajnyga@gmail.com) if you do not want to show it here.

And, what OJS version are you importing to. I only used 3.0.2. so it could be that something changed recently in OJS XML.

ajnyga · January 25, 2018, 8:07am

Actually probably the easiest would be, if you would send me the XML file. I have imported authors with affiliations, but you perhaps have a combination of data that I have not had.

The author element in your XML should look like this. Does your element look different? Do you for example have multiple affiliation elements there?

    <author primary_contact="true" include_in_browse="true" user_group_ref="Author">
      <firstname>XXXXXX</firstname>
      <lastname>XXXXXX</lastname>
      <affiliation locale="en_US">XXXXXX</affiliation>
      <country>XXXXXX</country>
      <email>XXXXXXX</email>
      <biography locale="en_US">XXXXXX</biography>
    </author>

Edit: I made a new release of the script: Releases · ajnyga/tsvConverter · GitHub
It should now check for the two alternative ways for mime type and I also changed the way the script handles localized affiliations. Not sure if it helps in your problem, but you could give it a try.

troelsk · January 25, 2018, 2:23pm

First of all: amazing help, thanks!

Second: I have attached the xml-file. It returns the same error in both your versions of the script (when uploading to OJS, that is).

The spreadsheet I use is based on your sample, with only the file names of the pdf’s changed (to test my own files). You can see the xml-file here and the spreadsheet here.

When I manually remove the affiliation tag from the xml-file, it seems to upload correctly to OJS - but I get the error “Unknown uploader admin”. That might be something to do with the setup of my OJS test site.

Edit: I am importing into OJS 3.1.0.1.

ajnyga · January 25, 2018, 3:06pm

tsvConverter 1.0.2 will solve all your problems: Releases · ajnyga/tsvConverter · GitHub

The affiliation field just had to be before the email it seems.

Also, I added a new setting to the beginning where you can define the name of the uploaded account. The default there is “admin”, but you can change the name of your own admin account there. See tsvConverter/convert.php at master · ajnyga/tsvConverter · GitHub

troelsk · January 25, 2018, 3:31pm

I could cry with happiness! THANK YOU. Amazing.

troelsk · January 26, 2018, 2:10pm

Just a quick update @ajnyga: I needed to import middle names, too, which was easy to insert in your code. If others need the same, just add the following at line 222 in the convert.php (and use a new column called ‘authorMiddlename’ in the spreadsheet):

                fwrite ($xmlfile,"\t\t\t\t\t<middlename>".$article['authorMiddlename'.$i]."</middlename>\r\n");

ajnyga · January 26, 2018, 2:28pm

thanks, I’ve missed that! I will add it later today.

ajnyga · January 30, 2018, 2:00pm

Note to anyone using the script.
After doing a test import, check the submission_files table that there are no entries there where genre_id is set to NULL. We may have had one case where an import did not include a valid file genre name and thus lead to a situation where the field was set to NULL.

ajnyga · February 9, 2018, 1:41pm

fyi that the above problem is fixed (happened because required data was missing).

Also note that I changed the way the converter handles locales. You can read about it in the readme file: GitHub - ajnyga/tsvConverter: Excel to OJS3 XML conversion tool. Seems to make more sense now and much easier to handle multilingual data.

troelsk · July 7, 2018, 7:42am

Hi again

I just tried uploading xml-files created using you amazing plugin on an OJS 3.1.1.2 website and got this error:

Element '{http://pkp.sfu.ca}revision', attribute 'user_group_ref': The attribute 'user_group_ref' is not allowed.

Do you have any idea what the solution could be?

Thanks