Summary: Learn how delimiter selection affects data export and import in GBase Database(GBase 8s). This guide explains the four-character delimiter limit, compound delimiters, delimiter collisions, and how to keep unload and load operations consistent.

Today I ran into a small but costly data export issue: I exported a table using commas as delimiters, then loaded it back. One record became five rows.

The data was not corrupted. The problem was that the remarks field already contained commas.

This is a classic delimiter collision, and it is worth understanding how to avoid it in GBase Database.

1. Choose a Compound Delimiter Instead of a Single Character

In GBase Database(GBase 8s), the unload statement allows you to specify a delimiter for exported data.

For example:

UNLOAD TO '/data/order.unl' DELIMITER '|#' SELECT * FROM t_order; 

The delimiter here is |#, a combination of two characters.

GBase Database supports delimiters of up to four characters. You can combine multiple characters into one delimiter, but a delimiter longer than four characters is rejected rather than silently truncated.

Why use a compound delimiter?

A single character such as | or # may already appear in business data. A combination of two characters is generally less likely to occur naturally in a text field.

The objective is to reduce delimiter collisions, especially when exporting large datasets containing free-form text.

2. The Export and Import Delimiters Must Match

Choosing a delimiter during export is only half the job.

The same delimiter must be specified when importing the file with load.

For example:

LOAD FROM '/data/order.unl' DELIMITER '|#' INSERT INTO t_order_bak; 

The export and import operations must use the same delimiter.

Consider what happens if you export using |# but import using |.

The import process does not necessarily report a clear delimiter mismatch error. Instead, it may split fields incorrectly, resulting in column-count errors, data conversion failures, or incorrectly mapped values.

The error message often points to the symptom rather than the original cause.

This is why delimiter consistency matters in GBase Database data migration and backup workflows.

3. Inspect the Exported File Before Loading

Before importing a large export file, inspect a few records:

head -n 3 /data/order.unl 

Check whether each line contains the expected number of fields and whether the delimiter appears unexpectedly inside business data.

A useful validation sequence is:

  1. Export a small sample.
  2. Inspect the first few records.
  3. Verify the expected field boundaries.
  4. Test the import into a temporary or staging table.
  5. Run the full import only after validation succeeds.

This small checkpoint can prevent a much larger data-cleanup task.

4. Keep the Delimiter in One Shared Configuration

There is another easy mistake: changing the export delimiter without updating the import and validation scripts.

For example, the export script may use |#, while the import script still uses |.

An even more dangerous situation occurs when the validation script continues splitting records using the old delimiter and reports success.

Define the delimiter once:

DELIM='|#' 

Then reference the same configuration in the export, import, and validation workflows.

The exact variable-passing mechanism depends on how your SQL and shell scripts are organized, but the principle is simple: one configuration value should control all three stages.

A false validation success is often more dangerous than an obvious import failure because the problem may remain undetected until business users discover incorrectly mapped data.

5. What If the Business Field Contains the Entire Delimiter?

A compound delimiter reduces collision risk, but it does not eliminate it.

What if a remarks field genuinely contains |#?

Do not assume that choosing a longer delimiter will solve the problem. GBase Database's delimiter length is limited, and no delimiter is guaranteed to be absent from arbitrary free-form text.

Consider the data format itself:

  • Structured fields are easier to export and validate.
  • Free-form text may require separate handling.
  • If the export format supports an appropriate escaping or encoding strategy, define it consistently on both sides.
  • Validate records containing special characters before processing the full dataset.

Delimiter selection is ultimately a data-boundary decision, not just a SQL parameter choice.

Final Thoughts

In GBase Database(GBase 8s), unload and load make data export and import straightforward, but delimiter selection deserves attention.

Remember three rules:

  1. Choose a delimiter of no more than four characters.
  2. Use exactly the same delimiter for export and import.
  3. Validate field boundaries before loading the full dataset.

A delimiter is not just a character between columns. It defines where one piece of data ends and another begins.

Before every export, ask one question: Can any field contain the delimiter I selected?