Handling Newlines and Double Quotes When Exporting CSV from GBase 8a

#gbase #database #数据库 #csv

GBase 8a can export data to CSV files, but when a field contains newlines or double quotes, the resulting file often breaks when opened in Excel — rows shift, columns misalign. This article shows how to fix both problems with the right export options, ensuring clean, Excel‑compatible CSV output from your gbase database.

The Raw Data

Our example table t2 has a name column that contains embedded newlines:

SELECT id, REPLACE(name, '\n', '\\n') FROM t2;
+------+--------------------------+
| id   | replace(name,'\n','\\n') |
+------+--------------------------+
|    1 | 123\n456\n789            |
|    2 | 2\n22\n222\n2222         |
|    3 | 3\n33\n333\n3333         |
+------+--------------------------+

Default Export: Broken Rows

If you export with default settings, the newlines in the data are treated as row terminators, completely scrambling the file when opened in Excel.

SELECT * FROM t2 INTO OUTFILE '/home/gbase/t2_org.csv'
FIELDS TERMINATED BY ',' ESCAPED BY ''
WRITEMODE BY OVERWRITES;

Resulting file content (note the broken rows):

Fix with Enclosed By

Add the ENCLOSED BY '"' clause to wrap every field in double quotes. This tells Excel that the newlines inside the quotes belong to a single field value.

SELECT * FROM t2 INTO OUTFILE '/home/gbase/t2.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY ''
WRITEMODE BY OVERWRITES;

Now the file looks like this (each field quoted, rows intact):

"1","123
456
789"
"2","2
22
222
2222"
"3","3
33
333
3333"

Open this in Excel and you get the original 3 rows, perfectly aligned.

When the Data Itself Contains Double Quotes

If a field value already contains a double quote character, it will interfere with the enclosing quotes and corrupt the CSV. You can handle this in two ways:

Use REPLACE to manually double the quotes:

SELECT id, REPLACE(name, '"', '""') name
INTO OUTFILE '/home/gbase/t2_2.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY ''
WRITEMODE BY OVERWRITES
FROM t2;

Use DOUBLE_ENCLOSED BY (recommended) — the database automatically escapes embedded double quotes:

SELECT id, name
INTO OUTFILE '/home/gbase/t2_2.csv'
FIELDS TERMINATED BY ',' DOUBLE_ENCLOSED BY '"' ENCLOSED BY '"' ESCAPED BY ''
WRITEMODE BY OVERWRITES
FROM t2;