Re: upper with dbload

>From: CDware user <cdware@sun.com> >Date: Tue, 02 Sep 1997 17:16:23 -0700 >X-Informix-List-Id: <news.42454> > > I've a dump of sybase database. (bcped one). I'm trying to load >it on informix using dbload. I've created the necessary command files. >I need tp upcase the char data in some of the columns. (I've the UPPER >procedure which works). > > My question is : Can I use UPPER in the insert statement of >the command file to upcase the data while loading??. Any experiences?? > >thanks >shashank No. The values will be inserted exactly as they're found in the data file. However, there's a program below which can be used to convert the data to upper case -- it is an update of DBLDFMT which has previously been sent to c.d.i. This version can also convert date fields from almost any format to almost any other format; read the manual page. It requires an ANSI C compiler and strptime(3). Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: Warning I do not reply to messages with anti-spam in the return path. : "@(#): shar.sh,v 1.10 1997/06/02 21:45:00 johnl Exp $" #! /bin/sh # # This is a shell archive. # Remove everything above this line and run sh on the resulting file. # If this archive is complete, you will see this message at the end: # "All files extracted" # # Created: Wed Sep 3 13:51:45 PDT 1997 by johnl at Informix Software Ltd. # Files archived in this archive: # dbldfmt.1 # dbldfmt.c # dbldfmt.mk # dbldfmt.tst # debug.h # emalloc.c # emalloc.h # filter.c # filter.h # getopt.h # range.c # range.h # stderr.c # stderr.h # #-------------------- if [ -f dbldfmt.1 -a "$1" != "-c" ] then echo shar: dbldfmt.1 already exists else echo 'x - dbldfmt.1 (8624 characters)' sed -e 's/^X//' >dbldfmt.1 <<'SHAR-EOF' X.\\" @(#)$Id: dbldfmt.1,v 3.3 1997/09/03 20:43:28 johnl Exp $ X'\\" @(#)Manual page: Application -- Part of application X.ds fC "Version: $Revision: 3.3 $ ($Date: 1997/09/03 20:43:28 $) X.TH DBLDFMT 1S "JLSS UNIX Tools" X.SH NAME Xdbldfmt \\(em convert fixed format LOAD file to delimited format X.SH SYNOPSIS X\\fBdbldfmt\\fP [-P p][-D d] -l len \\e X.br X [ -c literal | -d lo-hi.dp | -r lo-hi[,...] | \\e X.br X -f [c=const,r=lo-hi,d=lo-hi.dp,l=lo-hi,u=lo-hi,...] \\e X.br X -t [r=lo-hi,i='in format',o='out format',...]] \\e X.br X ... [file ...] X.SH DESCRIPTION X\\fBdbldfmt\\fP converts a file with fixed length records separated by Xnewlines into the delimited format used by the Informix LOAD statement Xand the \\fBdbload\\fP and dbimport commands. X.P XThe `\\*c-l\\*d' option is used to specify the line length (excluding the newline Xcharacter) and is mandatory. XIf any of the input lines turns out to have a different length from this, Xthe program stops immediately. X.P XThe `\\*c-f\\*d', `\\*c-r\\*d', `\\*c-c\\*d', `\\*c-d\\*d' and `\\*c-t\\*d' can occur Xmultiple times on the command line, and each occurrence generates a field Xin the output. X.P XThe `\\*c-c\\*d' option specifies the text for a constant field, which can be Xuseful to specify missing information. X.P XA range of columns is a value such as 13, or a column range such as 13-16, Xor a combination of values and column ranges separated by commas, or a Xcombination of values separated by literals. XRanges such as -16 are interpreted as 1-16, and ranges such as 17- are Xinterpreted as 16-len. X.P XThe `\\*c-d\\*d' option allows you to specify a range of columns which form Xthe number, and it implicitly inserts a decimal point at the position Xspecified by `\\*c.dp\\*d'. XThere is no default value for `\\*c.dp\\*d'; if you do not specify X`\\*c.dp\\*d' then no decimal point will be placed in the output. X.P XThe `\\*c-r\\*d' option is used to specify one or more ranges of columns to Xbe treated as a single field. XIt allows more or less arbitrary literal strings to be used as separators Xfor the ranges, but you cannot embed literal digits or dashes between Xranges, and you must have a valid range as the first part of the option Xargument. XIf you want something more complex, you have to use the more general X`\\*c-f\\*d' option. XThus `\\*c-r\\ 10-11/8-9/12-15\\*d' specifies that columns 10 and 11 are to be Xoutput, followed by a slash, columns 8 and 9, another slash, and finally Xcolumn 12 to 15. XThis effectively converts a date written in American format with no Xpunctuation into British format with slashes separating the day, month and Xyear. X.P XThe `\\*c-f\\*d' option is the most general and allows you to concatenate Xranges, decimals and constants into a single, composite output field. XNote that with the `\\*c-f\\*d' option, the suboptions are interpreted by Xgetsubopt(3). XBecause \\fBdbldfmt\\fP uses sub-options, if you need a compound range Xequivalent to `\\*c-r\\ 10-15,18-20\\*d', you have to use two range Xsub-options: `\\*c-f\\ r=10-15,r=18-20\\*d'. XSince a comma always marks the end of a suboption, you cannot embed them Xdirectly in the option string for a `\\*c-f\\*d' field; use octal escape Xsequences instead (the ASCII octal value for a comma is \\e054). XAn octal escape consists of a backslash followed by an arbitrary length Xoctal number (though values too large for an unsigned character generate an Xerror). XOctal escapes can be used for any character, though they are not strictly Xnecessary except for comma. XYou must escape a singleton backslash too; any character which is not an Xoctal digit that occurs immediately after a backslash is treated verbatim, X(but note that the interpretation of commas occurs before examining the Xstring for backslashes, so you still cannot escape a comma). XEscape sequences are also interpreted by the `\\*c-c\\*d' option. X.P XThe suboptions `\\*cr=\\*d', `\\*cl=\\*d' and `\\*cu=\\*d' are similar. XThe range on the right-hand side specifes the input range. XThe difference is that `\\*cr=\\*d' copies the data unchanged, but X`\\*cl=\\*d' converts the data to lower case and `\\*du=\\*d' converts the Xdata to upper case. XIf you need to put an initial capital on a field in columns 1-10, Xyou can do so using the notation `\\*c-f\\ u=1,r=2-10\\*d'. XThe `\\*cd=\\*d' suboption is treated like the value for a `\\*c-d\\*d' option. XThe `\\*cc=\\*d' suboption is treated like the value for a `\\*c-c\\*d' option. X.P XThe `\\*c-t\\*d' option allows you to apply date conversions to the data. XIt takes 3 suboptions, of which only the `\\*cr=\\*d' option is mandatory. XThe `\\*cr=\\*d' suboption specifies the range of columns in which the input Xdata appears, just as in the `\\*c-f\\*d' option. XThe `\\*ci=\\*d' suboption specifies the format of the input in terms which Xwill be understood by the X/Open function, strptime(3). XThe `\\*co=\\*d' suboption specifies the format of the output in terms which Xwill be understood by the ANSI C function, strftime(3). XIf the input format is not specified, the input is assumed to be in the Xformat dictated by ISO 8601:1988, which is familiar to you as the format Xfor a DATETIME YEAR TO SECOND value: `\\*c%Y-%m-%d\\ %H:%M:%S\\*d'. XSimilarly, if the output format is not specified, the output is printed in