Fixed problem with Endnote Import script

adrianbj
2005-04-07
2013-05-28
  • adrianbj
    adrianbj
    2005-04-07

    I'd like to share something I discovered using the Endnote import script. The enl style worked great, but the conversion using the endnote2mysql.php script caused me some problems.

    The main thing is that it seemed not to be producing a 'field terminator character' that I could use during the import into mysql database phase. I am using the built-in import from txt file function in PHPMyAdmin, so maybe this is the problem.

    To fix it for me, I made this change:

        $fin = str_replace("</REFBASE>; ","*",$fin);

    Note that the end of line character is now a '*'

    Also, it was correctly inserting the \N to specify NULL when there were multiple tabs, but not producing a 'lines terminated by' character between these. To fix this, I added the following line to the script:

       $fin = str_replace("\t","~",$fin);

    So now when I use the PHPMyAdmin import feature I use the following settings and it works perfectly!

    Fields terminated by:  *
    Fields enclosed by: leave this field blank
    Fields escaped by: \ Lines terminated by: ~
    LOAD method: Data Local

    Obviously you can change these two characters (* ~) to anything of your choice - these are probably not great options, but they did work.

    Thanks again to Richard Karnesky for this file - made my life so much easier!

     
    • I'm glad these eventually worked for you (they have had the least amount of testing of any new feature of refbase, I think).  Thanks for documenting your solution.

      The default field termination character for the import functionality which ships with MySQL is a tab (hence my use of '\t').  These can be changed to a user-specified character, so if PHPMyAdmin can't also be made to use '\t', perhaps I will update the script and instructions to use alternative characters, just as you have done.

      Thanks again,
      Rick

       
      • adrianbj
        adrianbj
        2005-04-07

        Rick,

        PHPMyAdmin can use \t for sure, however, the default is ';' however which if obviously a problem with the author field.

        For me I wasn't getting a \t character in the import.txt file. It was placing all the \N characters correctly for the NULL, but not giving any \t for the tabs. This is why I added the extra line:

                $fin = str_replace("\t","~",$fin);

        I still kept the following line to take care of the \N for multiple tabs.

            $fin = preg_replace("/(?<=\t)(?=\t)/","\\N",$fin);

        Users should certainly be able to play with this to suit their needs.

        Thanks again - you saved me weeks of work for sure!

         
    • Hi Adrian,

      glad you could help yourself!

      I don't have Endnote so I can't test the Endnote filter & script myself but as far as I understand Rick's original script it expects you to load the resulting text file using:

      Fields terminated by: \t
      Fields enclosed by: (leave blank)
      Fields escaped by: (leave blank)
      Lines terminated by: \n
      LOAD method: Data Local

      These are also the default settings used by the 'LOAD DATA LOCAL INFILE' command. I'm sure PHPMyAdmin employs the same command (internally) so I would think that PHPMyAdmin would do fine using \t as field separator and \n as line separator.

      Anyway, I'm happy it worked for you!

      Best regards, Matthias

       
    • adrianbj
      adrianbj
      2005-04-07

      Hi Matthias,

      Thanks for your comments. I think you are right for sure that you should be able to use \t and \n in the import settings. As I just mentioned to Rick, for some reason when I ran the script without the extra line I added, it would leave a tab space behind to define the line break, but when I went to import, it would not detect the tab as a \t I presume, because it wouldn't end the line and start a new record - I really don't know why. Specifying an actual character did the trick for me. Hopefully I will be the only person with this problem anyways.

      Adrian