SQL Language Guide : 8. SQL Statements : COPY VWLOAD : COPY VWLOAD Examples
 
Share this page                  
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