Solving "newline" Issues with CSV Files (Windows / Unix conversion)

The problem

Some files exported on Windows/DOS machines have a different new-line format.

On Unix and Linux, the default is the "\n" character, while on Windows/DOS the default is "\r\n".

Therefore, some files may refuse to bulk-load, because SQream DB expects the Unix newline character.

Solving the CSV Newline problem

You can specify the file format in the SQream DB COPY FROM command.

While the default for SQream is the Unix "\n" character for all bulk loads, you may wish to select the Windows sequence "\r\n".

To change the parser setting for a specific file, add the "RECORD DELIMITER" option to your copy command, after the "WITH" keyword.

See the SQream DB SQL Reference for more information about the other copy options available.

Example

COPY table_name FROM 'filename.psv' WITH DELIMITER '|'
                                         RECORD DELIMITER '\r\n'
					 OFFSET 2
                                         error_log 'error.log'
                                         error_verbosity 0;

Alternate solution

You can change the newline format on the file before importing it to SQream DB.

  1. Install the commonly-used "dos2unix" application:
    1. On CentOs (as root or with sudo):
      # yum install dos2unix
    2. On Ubuntu (as root or with sudo):
      # apt-get install tofrodos
    3. On Windows:
      Erwin Waterlander Windows port.
  2. Usage:
    dos2unix <file_name>

Example

$ dos2unix file_with_windows_terminations.csv
  • 49
  • 05-Jan-2018
  • 1345 Views