Vector 6.0 | COPY VWLOAD Examples

SQL Language Guide > SQL Language Guide > SQL Statements > COPY VWLOAD > COPY VWLOAD Examples

Was this helpful?

COPY VWLOAD Examples

1. Bulk load the data from multiple files into region table. Columns in the data file are delimited with a vertical bar, records are delimited by a new line, and null values are identified as NULL:

COPY region() VWLOAD FROM 'path/to/data/mytable_1.txt', 'path/to/data/mytable_2.txt' WITH INSERTMODE=BULK, FDELIM='|', RDELIM='\n', NULLVALUE='NULL', WORK_DIR = '/home/users/temp'

2. Load specific columns from a CSV file into the mytable table. Fields in the data file are delimited with a comma. Set the date format for columns a2_us and a4_us to US, and for all other columns to GERMAN.

COPY mytable() VWLOAD FROM '5255.csv'
WITH ATTRIBUTES='a1_multi,a2_us,a3_de,a4_us,a5_de', FDELIM=',',
DATEFORMAT='a2_us=US,a4_us=US,GERMAN';

vwload Escape Sequences

To specify control characters in the vwload command, you must use an escape sequence. An escape sequence is initiated by a \ character. Valid escape sequences are:

Escape Sequence	Description
\a	Bell (alert)
\b	Back space
\f	Form feed
\n	Newline
\r	Carriage return
\t	Tab
\v	Vertical tab
\nnn	The character with octal code value nnn
\uxxxx	The 2-byte Unicode code point with hexadecimal value xxxx
\\	\ character

Note: Certain special characters, such as \, ", ‘, and |, must be protected from interpretation by the command shell by using the appropriate quoting and escaping mechanisms provided by the shell. This does not apply to Actian Director, which automatically takes care of such formatting.

vwload Date Format Settings

The ‑‑dateformat format | attr=format option on the vwload command sets the date format for the attribute (column).

Valid settings for format are as follows:

Setting	Valid Input Formats	Output Format
US (default)	mm/dd/yy mm-dd-yy mmddyy mm/dd/yyyy mm-dd-yyyy mmddyyyy dd-mmm-yyyy dd mmm yyyy yyyy-mm-dd yyyy.mm.dd yyyy_mm_dd mm-dd mm/dd am and pm format in timestamp. Only hours between 1 and 12 are valid. am and pm must be in lowercase.	dd-mmm-yyyy
MULTINATIONAL	yyyy-mm-dd mm-dd-yy mmddyy mmddyyyy dd/mm/yy dd/mm/yyyy dd mmm yyyy All US formats except mm/dd/yyyy and mm/dd/yy	dd/mm/yy
MULTINATIONAL4	yyyy-mm-dd dd/mm/yy dd/mm/yyyy dd mmm yyyy mm-dd-yy mmddyy mmddyyyy All US formats except mm/dd/yyyy and mm/dd/yy	dd/mm/yyyy
ISO	yyyy-mm-dd yyyymmdd yymmdd ymmdd mmdd mdd dd mmm yyyy All US input formats except mmddyy	yymmdd
ISO4	yyyy-mm-dd yyyymmdd yymmdd ymmdd mmdd mdd dd mmm yyyy All US input formats except mmddyy	yyyymmdd
ISO4T	All ISO4 input formats. If the prefix "T" is used then the absolute time component can use the input format hhmmss as well as the standard hh:mm:ss. For example: Acceptable: yyyymmddThhmmss, yyyymmddThh:mm:ss, and yyyymmdd hh:mm:ss Not acceptable: yyyymmdd hhmmss Acceptable: 'Thhmmss', 'Thh:mm:ss' and 'hh:mm:ss' Not acceptable: 'hhmmss' This is the only case where an absolute time can be entered in format hhmmss instead of hh:mm:ss. To avoid ambiguity, a time field entered in hhmmss format must be 6 characters long.	yyyymmdd ISO4 output format, unless the date includes a time, in which case the format is: yyyymmddThhmmss
ISO4TC	See description under ISO4T.	ISO4 output format, unless the date includes a time, in which case the format is: yyyymmddThh:mm:ss
ISO8601	All ISO input formats	yyyy-dd-mmThh:mm:ssZ where hh is in 24-hour format and Z indicates Zulu (UTC) timezone
SWEDEN or FINLAND	yyyy-mm-dd yy-mm-dd mmddyy dd mmm yyyy All US input formats except mm-dd-yyyy	yyyy-mm-dd
GERMAN	yyyy-mm-dd dd.mm.yyyy ddmmyy dmmyy dmmyyyy ddmmyyyy dd mmm yyyy mm-dd-yy All US input formats except yyyy.mm.dd and mmddyy	dd.mm.yyyy
YMD	mm/dd mm-dd mmdd yymdd yymmdd yy-mm-dd yyyymdd yyyy-mmm-dd yyyy/mm/dd yyyy.mm.dd yyyy-mm-dd yyyy_mm_dd yyyymmdd yyyy mmm dd	yyyy-mmm-dd
DMY	yyyy-mm-dd yyyy_mm_dd dd/mm dd-mm ddmm ddmyy dd-mm-yy ddmmyy ddmyyyy ddmmyyyy dd/mm/yyyy dd-mm-yyyy dd.mm.yyyy dd-mmm-yyyy dd mmm yyyy	dd-mmm-yyyy
MDY	yyyy-mm-dd yyyy_mm_dd mm/dd mm-dd mmdd mmddyy mddyy mddyyyy mm-dd-yy mm-dd-yyyy mm/dd/yyyy mm.dd.yyyy mmddyyyy mmm-dd-yyyy	mmm-dd-yyyy

For a date that is missing the century on input, year is determined by the setting on the II_DATE_CENTURY_BOUNDARY environment variable.

In three-character month formats, for example, dd-mmm-yy, specify three-letter abbreviations for the month (for example, mar, apr, may).

To specify the current system date and time, use the constant, NOW.

vwload Supported Character Sets

Character sets supported on the vwload --charset option are as follows:

Character Set	Description	Format
ALT	Support of Cyrillic on DOS	Single byte
ARABIC	Arabic-449-Plus	Single byte
CHINESES	Simplified Chinese - PRC	Double byte
CHTBIG5	Traditional Chinese - Taiwan, BIG5	Double byte
CHTEUC	Traditional Chinese - Taiwan, EUC	Double byte
CHTHP	Traditional Chinese - Taiwan, HP ROC15	Double byte
CSGB2312	Simplified Chinese - GB2312	Double byte
CSGBK	Simplified Chinese - GBK	Double byte
CW	Cyrillic on Windows 3.1	Single byte
DECMULTI	DEC Multinational (superset of ASCII) and default for VMS	Single byte
DOSASMO	IBM DOS ASMO Arabic (cp708)	Single byte
ELOT437	Greek for PC/RS6000/SCO-UNIX	Single byte
GREEK	DEC Greek Elot	Single byte
HEBREW	DEC Hebrew	Single byte
HPROMAN8	HP Roman8 (superset of ASCII)	Single byte
IBMPC437	IBM PC Code Page 437 (US and English)	Single byte
IBMPC850	IBM PC Code Page 850 (Multilingual), includes accented characters	Single byte
IBMPC866	IBM PC 866 (Cyrillic for DOS)	Single byte
IS885915	ISO 8859/2 (Latin and some Greek). Identical to ISO 8859/1 Latin, except for eight characters, including the Euro currency symbol (€, Unicode U+20AC).	Single byte
ISO88591	ISO 8859/1 Latin and default for UNIX (superset of ASCII)	Single byte
ISO88592	8859/5 (Latin and Cyrillic)	Single byte
ISO88595	8859/9 (Latin and some Turkish) CP 920	Single byte
ISO88597	ISO 8859/7 (Greek)	Single byte
ISO88599	ISO 8859/15 (Latin and Euro sign)	Single byte
KANJIEUC	Japanese, EUC	Double byte
KOI18	KOI 8-bit (ISO 6937/8), Russia	Single byte
KOREAN	Korean	Double byte
PC737	IBM PC Code page 737 - Greek	Single byte
PC857	IBM PC Code page 857 - Turkish	Single byte
PCHEBREW	IBM PC / MSDOS Hebrew	Single byte
SHIFTJIS	Shift-JIS Japanese	Double byte
SLAV852	IBM PC Code Page 852 (Slavic)	Single byte
THAI	DEC Thai Tis	Single byte
UTF8	Unicode encoding form UTF-8	Multi-byte
WARABIC	Arabic	Single byte
WHEBREW	Microsoft Windows Hebrew	Single byte
WIN1250	Eastern Europe: Windows page 1250	Single byte
WIN1252	Windows code page 1252 - Latin 1 (Western Europe) and default for Windows	Single byte
WIN1253	Modern Greek	Single byte
WTHAI	IBM/Windows Thai (cp874)	Single byte

Last modified date: 11/09/2022