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: [email protected]
[mailto:[email protected]] 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
