[OPEN-ILS-DEV] MARC Record Import from txt

[Elliot Voris]

Hi, Dan

Thanks again for your help! I'm still having no luck. As a test measure, I removed any single-quotes (') from the MARC records before enclosing them in the format that your script was in: ('<MARC>'), so I'm sure my issue is something else.

I'm wondering if PostgreSQL doesn't like the way I put the parenthesis and single-quotes to begin with. The only method I could figure out to put the necessary characters in place was using Notepad++. I did a find/replace with the regular expressions as follows:

To place the "('" at the beginning, I found the regular expression "^(.*)$" (no quotes), and replaced with the regex "('\1" (again, no quotes)
To place the "')," at the end, I found the regular expression "^(.*)$" (no quotes), and replaced with the regex "\1')," (again, no quotes)

Could that have messed up encoding or something? Is there a better way to add the necessary parenthesis & single-quotes at the beginning/end of each line?

Thanks!

Elliot J Voris


-----Original Message-----
From: open-ils-dev-bounces at list.georgialibraries.org [mailto:open-ils-dev-bounces at list.georgialibraries.org] On Behalf Of Dan Scott
Sent: Wednesday, December 14, 2011 11:40 AM
To: Evergreen Development Discussion List
Subject: Re: [OPEN-ILS-DEV] MARC Record Import from txt

On Wed, Dec 14, 2011 at 05:29:15PM +0000, Elliot Voris wrote:
> Update:
> 
> I noticed that when I was saving the sql file in Notepad++, it was encoded using ANSI. I switched that to UTF-8, and I received the same result. Is PostgreSQL interpreting the single-quote (') as an escape character? Or is the issue something else?

Hi Elliot:

Yes, single quotes in SQL can escape other single quotes. For example, if you need a singl quote in the middle of a string that you're inserting into the database, you would place another single quote in front of it like so:

'So that''s what the fuss is about'

I'm guessing that in your case, PostgreSQL saw something like:

'<record><datafield tag="440" ind1="0" ind2=" "><subfield code="a">So that's the problem</subfield></datafield></record>'

and got confused by the unescaped single quote in the text of the subfield.

To avoid problems with embedded single quotes in MARC records in that example SQL file, I ran a regular expression to escape all of the existing single quotes before wrapping them with the start / end single quotes. In vim, the command was :%s/'/''/g but your editor of choice will vary.

Dan