Question & Answer
Question
How can you load a file that contains newlines embedded within character fields?
Answer
The nzload command assumes that the end-of-record character is a newline (\n). If a file contains one or more newline characters within its fields, the file cannot be successfully loaded. This behavior is not configurable. The file must be modified by removing the embedded newline characters or by escaping them. This can be done in various ways with a C program or a Unix shell script. This article provides one solution.
The one assumption that must be made is that character fields are enclosed in quotes. For the purposes of this solution, assume that double-quotes are used. For example:
- "this is one field"
1. Copy the following program into a file with a .c extension. For example, replace.c.
//Start of program
#include <stdio.h>
main () {
int ch;
int flag;
flag=0;
while ((ch=getchar()) != EOF)
{
// Test for "
// If found, toggle my flag on or off
if (ch==042)
{
if (flag==0) flag=1;
else flag=0;
}
// Test for NL
// If found -- and if my flag is on -- then output a carriage return instead of a newline
if (ch==012 && flag==1) printf ("\r");
else
// Put out whatever character I just read in
putchar(ch);
}
}
//End of program
2. Compile the program on NPS as follows:
- gcc replace.c -o replace.out
The replace.out file is the executable program that you will run.
3. The program reads from standard input and writes to standard output. You can run it as follows:
- replace.out <bad.dat>good.dat
Here is a simple example of a file. This is how it will look in VI on the nz host:
bad.dat
- rec 1 field 1,"rec1 field
2",
rec 2 field 1,"rec2 field 2"
good.dat
- rec 1 field 1,"rec1 field^M 2",
rec 2 field 1,"rec2 field 2"
4. Run the nzload command with the options -crInString and -quotedvalue DOUBLE.
For example:
nzload -db proddb1 -t orders -delim "," -crInString -df good.dat -quotedvalue DOUBLE
This command loads the good.dat file into the table orders in the database proddb1.
The -crInString option causes nzload to ignore any carriage returns ('\r') that are found within any of the fields.
The -quotedvalue option allows any field to be enclosed in double-quotes.
Historical Number
NZ845896
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21575158