Resolving Single-Column Imports and Empty Separator Handling in SQLite CLI

Understanding the .import Command’s Empty Column Separator Limitation

The SQLite command-line interface (CLI) provides the .import utility to load data from text files into database tables. A common use case involves importing a text file into a single-column table, where each line of the file represents a full row in the table. Users attempting this may encounter the error:

Error: non-null column separator required for import  

This occurs when the column separator (set via .separator) is configured as an empty string. The CLI enforces that a non-null separator must be defined, even when importing into a single-column table. The expectation from users is that an empty separator would instruct the CLI to treat each input line as a single column. However, the current implementation interprets an empty separator as an invalid configuration, leading to the error.

The core challenge lies in the CLI’s design philosophy for data import: it assumes that input data is structured with delimiters separating columns and rows. When no column delimiter exists (as in a single-column table), the CLI lacks a mechanism to bypass delimiter-based parsing. This creates friction for scenarios where data rows should not be split, such as importing lines of text, log entries, or outputs from utilities like find -print0 or git status -z.

The discussion highlights two primary scenarios where this limitation is problematic:

1. Simplicity in Single-Column Imports: Users want to avoid selecting a delimiter that does not exist in the data, which requires manual inspection or preprocessing.
2. Handling Zero-Separated Inputs: Files or streams using NUL (\0) as a row separator (common in Unix utilities) cannot be directly imported due to the CLI’s handling of separator characters.

SQLite CLI’s Enforcement of Non-Empty Column Separators

The enforcement of non-empty column separators is rooted in the CLI’s parsing logic for .import. The separator configuration directly impacts how input lines are tokenized into columns. Key factors contributing to this behavior include:

1. Legacy Parsing Logic:
   The .import command uses a delimiter-based tokenizer that splits input lines into columns using the column separator. An empty separator invalidates this splitting mechanism, as the parser cannot discern column boundaries. The code explicitly checks for a non-empty separator and throws an error if absent.

2. Ambiguity in Empty Separator Semantics:
   An empty separator could theoretically represent two distinct intentions:

   • Single-Column Mode: Treat each line as a single column.
   • Character-Level Splitting: Split the input into individual characters (common in other tools).
     The CLI avoids this ambiguity by rejecting empty separators, requiring users to explicitly define a delimiter.

3. Row vs. Column Separator Interactions:
   The CLI distinguishes between row separators (typically newlines) and column separators. Even if rows are separated by newlines, the absence of a column separator complicates the parsing logic, especially for multi-line fields or escaped characters.

4. Encoding and Character Set Constraints:
   The CLI’s handling of non-ASCII characters (e.g., DEL \177 or octal \375) as separators is version-dependent. Prior to version 3.42, extended ASCII separators with bit 7 set (values ≥ 128) were mishandled due to signed char type issues. This forced users to employ workarounds for non-printable separators.

5. Security and Input Validation:
   Allowing empty separators could introduce vulnerabilities or misinterpretations when importing malformed data. Enforcing a non-empty separator ensures predictable parsing behavior.

Implementing Workarounds and Modifications for Single-Column Imports

Workaround 1: Using Non-Printable ASCII Characters as Delimiters

To bypass the empty separator restriction, use a delimiter that does not appear in the input data. The ASCII DEL character (\177) or octal \375 (valid in CLI 3.42+) are ideal candidates:

.mode ascii  
.separator "\177" "\n"  
CREATE TABLE Lines(t TEXT);  
.import /tmp/filename Lines  

Explanation:

• .mode ascii configures the CLI to handle input in ASCII mode.
• .separator "\177" "\n" sets the column separator to DEL (non-printable, rarely used) and row separator to newline.
• The DEL character acts as a "dummy" delimiter, ensuring the entire line is treated as a single column.

For zero-separated input (e.g., find -print0), translate NUL (\0) to DEL using tr:

.import "|tr '\000' '\177' < filename" Lines  

Note: The tr utility translates NUL characters to DEL, making the input compatible with the .import command.

Workaround 2: Leveraging UTF-8 Validity Constraints

In CLI 3.42+, octal \375 (character 253) can serve as a delimiter since it is invalid in UTF-8 encoded text:

.separator "\375" "\n"  
.import filename Lines  

Advantage: This avoids conflicts with valid UTF-8 data, ensuring the delimiter never appears in the input.

Workaround 3: Modifying the SQLite CLI Source Code

A proposed patch modifies the CLI to treat an empty column separator as a signal to import entire lines as single columns. The changes involve:

1. Defining a "No Separator" Sentinel Value:
   Introduce NOSEP (-2 or -1) to represent the absence of a column separator.

2. Updating Separator Handling Logic:
   Modify the ascii_read_one_field and csv_read_one_field functions to skip column splitting when NOSEP is detected.

3. Adjusting Input Validation:
   Allow empty column separators by setting sCtx.cColSep = NOSEP instead of throwing an error.

Code Modifications:

--- src/shell.c.in  
+++ src/shell.c.in  
@@ -1593,10 +1593,20 @@  
 #define SEP_Comma   ","  
 #define SEP_CrLf   "\r\n"  
 #define SEP_Unit   "\x1F"  
 #define SEP_Record  "\x1E"  
 
+/*  
+** A value different from all unsigned char values  
+** and also different from EOF.  
+*/  
+#if EOF == (-1)  
+# define NOSEP (-2)  
+#else  
+# define NOSEP (-1)  
+#endif  
+ 
 /*  
 ** Limit input nesting via .read or any other input redirect.  
 ** It's not too expensive, so a generous allowance can be made.  
 */  
 #define MAX_INPUT_NESTING 25  
@@ -5843,12 +5853,12 @@  
 **   EOF on end-of-file.  
 **  + Report syntax errors on stderr  
 */  
 static char *SQLITE_CDECL csv_read_one_field(ImportCtx *p){  
  int c;  
- int cSep = (u8)p->cColSep;  
- int rSep = (u8)p->cRowSep;  
+ int cSep = p->cColSep;  
+ int rSep = p->cRowSep;  
  p->n = 0;  
  c = fgetc(p->in);  
  if( c==EOF || seenInterrupt ){  
   p->cTerm = EOF;  
   return 0;  
@@ -5933,12 +5943,12 @@  
 **   EOF on end-of-file.  
 **  + Report syntax errors on stderr  
 */  
 static char *SQLITE_CDECL ascii_read_one_field(ImportCtx *p){  
  int c;  
- int cSep = (u8)p->cColSep;  
- int rSep = (u8)p->cRowSep;  
+ int cSep = p->cColSep;  
+ int rSep = p->cRowSep;  
  p->n = 0;  
  c = fgetc(p->in);  
  if( c==EOF || seenInterrupt ){  
   p->cTerm = EOF;  
   return 0;  
@@ -8756,12 +8766,12 @@  
    }else if( cli_strcmp(z,"-schema")==0 && i<nArg-1 ){  
     zSchema = azArg[++i];  
    }else if( cli_strcmp(z,"-skip")==0 && i<nArg-1 ){  
     nSkip = integerValue(azArg[++i]);  
    }else if( cli_strcmp(z,"-ascii")==0 ){  
-    sCtx.cColSep = SEP_Unit[0];  
-    sCtx.cRowSep = SEP_Record[0];  
+    sCtx.cColSep = (u8)SEP_Unit[0];  
+    sCtx.cRowSep = (u8)SEP_Record[0];  
     xRead = ascii_read_one_field;  
     useOutputMode = 0;  
    }else if( cli_strcmp(z,"-csv")==0 ){  
     sCtx.cColSep = ',';  
     sCtx.cRowSep = '\n';  
@@ -8784,15 +8794,14 @@  
   if( useOutputMode ){  
    /* If neither the --csv or --ascii options are specified, then set  
    ** the column and row separator characters from the output mode. */  
    nSep = strlen30(p->colSeparator);  
    if( nSep==0 ){  
-    raw_printf(stderr,  
-          "Error: non-null column separator required for import\n");  
-    goto meta_command_exit;  
-   }  
-   if( nSep>1 ){  
+     sCtx.cColSep = NOSEP;  
+   } else if( nSep==1 ){  
+    sCtx.cColSep = (u8)p->colSeparator[0];  
+   } else {  
     raw_printf(stderr,  
        "Error: multi-character column separators not allowed"  
        " for import\n");  
     goto meta_command_exit;  
    }  
@@ -8815,11 +8824,10 @@  
    if( nSep>1 ){  
     raw_printf(stderr, "Error: multi-character row separators not allowed"  
              " for import\n");  
     goto meta_command_exit;  
    }  
-   sCtx.cColSep = (u8)p->colSeparator[0];  
    sCtx.cRowSep = (u8)p->rowSeparator[0];  
   }  
   sCtx.zFile = zFile;  
   sCtx.nLine = 1;  
   if( sCtx.zFile[0]=='|' ){  

How the Patch Works:

• NOSEP Definition: A sentinel value (-2) is defined to represent "no column separator."
• Separator Assignment: If the column separator is empty (nSep == 0), sCtx.cColSep is set to NOSEP instead of triggering an error.
• Parsing Adjustments: The ascii_read_one_field and csv_read_one_field functions are modified to accept NOSEP, causing them to read entire lines as single fields.

Compiling the Modified CLI:

1. Download the SQLite source code.
2. Apply the patch to src/shell.c.in.
3. Compile using:

   ./configure  
   make  
   

4. Test the new behavior:

   .separator "" "\n"  
   .import filename tablename  
   

Workaround 4: Preprocessing Input with External Tools

For users unable to modify the CLI, preprocessing the input file ensures compatibility:

Example with tr (Translate Characters):

.import "|tr '\n' '\177' < filename" Lines  

This replaces newlines with DEL characters, allowing the CLI to parse each "line" as a single column.

Example with sed (Stream Editor):

.import "|sed 's/^/\177/;s/$/\177/' filename" Lines  

Adds a DEL character at the start and end of each line, forcing the entire line to be treated as a single column.

Choosing the Right Approach

• Temporary Fixes: Use non-printable delimiters or preprocessing for one-off imports.
• Permanent Solution: Apply the source code patch if single-column imports are frequent.
• Portability Considerations: The tr-based workaround may not be portable across Windows systems without Unix-like utilities.

By understanding the CLI’s parsing constraints and leveraging these strategies, users can efficiently import single-column data while awaiting official support for empty separators.