next line, new line

[rasdj]

I have a lot of SQL to convert to postgres from oracle. I have most of
the problems worked out except for this last bit. Many of my tables
need the last comma replaced with a close parenthesis - they look like
this:

create table schema.table (
FLD000 NUMERIC(10,0) NOT NULL,
FLD001 CHAR(3) NOT NULL,
FLD002 DATE NOT NULL,
;

when the syntax requires:

FLD002 DATE NOT NULL)
;

I output the text in reverse thinking I could find the semicolon, go to
the next line and replace the 'comma newline' with 'closeparen newline'
and then go on to find the next semicolon.

;
FLD002 DATE NOT NULL,
FLD001 CHAR(3) NOT NULL,
FLD000 NUMERIC(10,0) NOT NULL,
create table schema.table (
;
FLD002 DATE NOT NULL,
FLD001 CHAR(3) NOT NULL,
FLD000 NUMERIC(10,0) NOT NULL,
create table schema.table2 (

I don't seem to be making any progress altho I have had some
interesting output.

Throw me a bone?

Thank you,

RasDJ

 

[Jeremy Bowers]

I have a lot of SQL to convert to postgres from oracle. I have most of the
problems worked out except for this last bit. Many of my tables need the
last comma replaced with a close parenthesis - they look like this:

create table schema.table (
FLD000 NUMERIC(10,0) NOT NULL,
FLD001 CHAR(3) NOT NULL,
FLD002 DATE NOT NULL,
;

when the syntax requires:

FLD002 DATE NOT NULL)
;

I output the text in reverse thinking I could find the semicolon, go to
the next line and replace the 'comma newline' with 'closeparen newline'
and then go on to find the next semicolon.

You don't give a heck of a lot of details here, but the first thing that
leaps to mind is,

* Suck it all into a string, let's call it "s".
* s = s.replace(",\n;", ")\n;")
* Dump out s.

Failing that, regex can be used to allow for any whitespace, but worry
about that if this isn't enough. We can also discuss trying to stream this
operation if your SQL won't fit into memory all at once, but on modern
machines that would be a breathtaking number of table definitions that
would make me think you have other, larger problems

 

[rasdj]

Thanks Jeremy, something like this would work:

try:
lines = [ line.replace(",\n;", ")\n;") for line in input ]

If I could figgure out how to:

IF ':' in line
READ next line in
lines = [ line.replace(",\n;", ")\n;") for line in input ]
output.write(str.join('', lines))

because there are lots of "comma newline" but the only ones I want are
the ones that follow the semicolon.

RasDJ

 

[Jeremy Bowers]

Thanks Jeremy, something like this would work:

try:
lines = [ line.replace(",\n;", ")\n;") for line in input ]

If I could figgure out how to:

IF ':' in line
READ next line in
lines = [ line.replace(",\n;", ")\n;") for line in input ]
output.write(str.join('', lines))

because there are lots of "comma newline" but the only ones I want are
the ones that follow the semicolon.

RasDJ

My apologies, I was unclear when I said "suck the whole file in"; a little
too colloquial.

-------------

filename = "YOUR_FILENAME_HERE.sql"
output = "YOUR_DESTINATION_HERE.sql"
f = open(filename)
contents = f.read()
f.close()

contents = contents.replace(",\n;", ")\n;")
# optionally, the \n in the second string may be dropped, it isn't
# necessary

f = open(output, "w")
f.write(contents)
f.close()