CODEWITHSUNDEEP
sqlfirebirdfirebird2.5
Michael
Michael

Reputation: 2657

Firebird iSQL Output to csv leaving blank rows between records

I have a Firebird 2.5 database that I require to export data to csv format. I have a .SQL file that I call from a .bat file. It works and exports the data, but the formatting isn't 100%.

When its exporting to csv it is leaving 4 blank rows between each record. This is my SQL:

OUTPUT C:\\SQL_Scripts\\AddrBkExtract.csv FORMAT ASCII DELIMITED BY ',' QUOTE '"';
SELECT a.ID || ';' || a."NAME" || ';' || a.ADDRESS1 || ';' || a.ADDRESS2 || ';' || a.ADDRESS3 || ';' || a.POSTCODE FROM ADDRESSBOOK a;

When it exports it gives me data like this:

1;Testname;testaddr1;testaddr2;testaddr3;testaddr4;testpostcode




2;Testname;testaddr5;testaddr6;testaddr7;testaddr8;testpostcode

But I want it like this:

1;Testname;testaddr1;testaddr2;testaddr3;testaddr4;testpostcode
2;Testname;testaddr5;testaddr6;testaddr7;testaddr8;testpostcode

Its also exporting the word 'CONCATENATION ' between rows?

Any help would be greatly appreciated.

Upvotes: 0

Views: 1218

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109136

I cannot reproduce the empty lines in Firebird 2.5.9. It is possible this is some bug in Firebird 2.5.2, so you could try upgrading, though a quick scan of the release notes doesn't show anything like this.

Without example data to reproduce this it is hard to guess about the cause of the additional empty lines between the rows, but possibly POSTCODE contains linebreaks. A potential solution could be to TRIM each column before concatenation, or at least the POSTCODE column.

As mentioned in the comments by Arioch 'The, you can prevent the CONCATENATION column header from appearing by using the SET HEADING OFF command.

As an aside, the ISQL OUTPUT command only takes a file name, it does not have a FORMAT ASCII DELIMITED BY ',' QUOTE '"' clause. You likely picked this up from a different tool than ISQL (the comments suggest IBExpert script). This is silently ignored, because ISQL considered the filename to be complete after the first space (filenames with spaces need to be surrounded by single or double quotes), and ignores everything else until the terminator.

Upvotes: 1

Related Questions