How do I import issues and articles from spreadsheet?

Hi!

I have a database of our publication’s previous issues and articles and would like to import that into a fresh install of OJS (currently version 2.4.8.1, but will be upgraded sometime during 2018).

How would I go about that? I am trying to import the issues first and have managed to a functional test-import by first downloading an xml-file of the issues, then manually changing the entries and uploading again. Now, my challenge is to convert my Excel-file to the xml-file.

Any help will be much appreciated :slight_smile:

(link to spreadsheet here)

I used a script to do this in the past. You can find it here:

After I completed the import, I discovered that this only worked for my OJS import because of a bug in the code, so YMMV.

OJS will automatically create the issues for you, if you provide issue information in the import file with the articles, so importing the issues first is probably more complicated than it needs to be.

1 Like

Thank you! I managed to create the xml from my spreadsheet and is now looking at importing the articles (sounds like I could have started there, but oh well, I probably learned something useful a long the way…).

I’ve managed to import articles with all relevant data, including the pdf, except for one crucial part: assigning each article to a specific issue. I might have found the solution though.

In the OJS 2.4.x schema, issues contain sections contain articles. I think if you wrap your articles with the relevant section tags, and wrap your sections with the relevant issue tags, you should be able to import sections and artcles overtop of a set of empty issues (where the issue identification is identical).

You wouldn’t have an example of how that looks in the xml-file, would you? I have tried this code without luck:

  <issue_identification>
    <volume>1</volume>
    <number>3</number>
    <year>2014</year>
  </issue_identification>

The issue_identification tag doesn’t appear until 3.x. See a 2.4.8 sample here:

I have a script for converting Excel-data (specific schema required of course) to OJS XML, but it only supports OJS 3 which has a bit different XML.

But with 80 articles, I would just use quicksubmit plugin and add them manually. Edit: nevermind, I see now that those were issues…

1 Like

Do you happen to have that script publicly available in GitHub?

1 Like

Not at the moment, but I can see if I have time later this week to upload it. But I have to warn you that it is not pretty :smiley: As I said above it depends on a standardized Excel sheet. We made it for journals that are importing new old articles to OJS and think that quicksubmit is too slow.

Hi,

Cab you please provide the script for converting Excel-data (specific schema required of course) to OJS 3x XML?

Thanks

Yes, I will probably upload it to github later this week.

edit: have not forgot, but simply did not have the required time last week. Will upload my script to github tomorrow (Tuesday)

@ajnyga,

that would be brilliant! We’re looking for something similar for importing our old archive of 717 articles to OJS 3.

Thanks
Tobias

Hi,

I added the script to Github: GitHub - ajnyga/tsvConverter: Excel to OJS3 XML conversion tool

As it is mentioned in the readme file now, the script was written for in-house use, so there are probably some problems with it depending on the data you are trying to import. But we have used it to import the archives of six journals during last year, so it should work at least when used with one locale.

I can not promise that I will have time to give much support for using it, but feel free to try and modify if needed.

2 Likes

@ajnyga,

thank you very much! I’d give this a try.

Tobias

This is absolutely brilliant, @ajnyga. Thank you so much!

I get this error, though, when I try to create an xml-file (I have used your example-spreadsheet for testing, only changing file names to test upload of my own pdf’s):

Fatal error: Call to undefined function mime_content_type() in .../convert.php on line 258

Do you have any idea what this might be caused by?

Hi, seems to be related to a missing php extension, see https://stackoverflow.com/questions/14809054/mime-content-type-function-not-working

edit: hmm, the function seems to be deprecated, I have to check if I could replace it with something.’

edit edit: or is it, what is your PHP version?

Thank you for helping! My php-version is 4.7.3.

edit: No, that was the phpMyAdmin version. The php-version is 5.6.30.

I am finding very mixed information on whether the function is deprecated or not.

Your could try enabling the mime_magic extension in php. If that does not work for you, you could try adding this function to the converter file.

function _mime_content_type($filename) {
    $result = new finfo();

    if (is_resource($result) === true) {
        return $result->file($filename, FILEINFO_MIME_TYPE);
    }

    return false;
}
1 Like

Hi all,

The mime_content_type function wasn’t deprecated, then it was, now it isn’t again.

The good news is that PKP software only uses it as a fallback when the “preferred” mechanism, the fileinfo suite of tools, isn’t available. See Error message on upload: "No file uploaded or invalid file type!" for more information about how OJS/OMP/etc. determine file types.

Regards,
Alec Smecher
Public Knowledge Project Team

Thanks, I’ve tried both without luck unfortunately. Maybe I could find / set up a server with an older version of php…