Question Re: An error in INPUT/LOAD command SA12 in Technology Q&A

An error in INPUT/LOAD command SA12

Former Member — Mon, 10 Jun 2013 11:01:02 GMT

A disappointing error I stuck when tried load 1000ds rows text with

INPUT INTO ... FROM '...' format TEXT DELIMITED BY '^^'

after a havy inquiry I found that command INPUT (LOAD the same) tries close quotation marks don`t look at defined delimiter '^^' ! I'm afraid that SA16 have the same error.

Next test demonstrate an error.

declare local temporary table tmp_tbl
(
 R_EFERENCE   Char(16),
 TIP_DOC     Char(1),
 VID_DOC     Char(1),
 DOC_DT_KT   Char(1),
 N_DOC       Char(10),
 DATE_DOC    Date,
 sop_DOC     Char(20),
 VAL_sop     Char(3),
 ACCOUNT_A   Char(16),
 NAME_CR     Char(40),
 taxcode1_A     Char(10),
 MFO_CR      Char(9),
 BANK_A      Char(45),
 ACCOUNT_B   Char(16),
 NAME_DB     Char(40),
 taxcode2_B     Char(10),
 MFO_DB      Char(9),
 BANK_B      Char(45),
 ACCOUNT     Char(16),
 COM         Char(160),
 DAT_OD      Date,
 TIME_OP     Char(5),
 TIP         Char(1),
 DOC_N       Char(4),
 DAT         Date,
 sop         Char(17),
 VAL_CODE    Char(4),
 RS_CR       Char(16),
 KSCH_A      Char(14),
 RS_DB       Char(16),
 KSCH_B      Char(14),
 COUNT       Char(16),
 taxcode_CR     Char(10),
 taxcode_DB     Char(10),
 T_IME        Char(5)
)
 ON COMMIT PRESERVE ROWS;

INPUT INTO tmp_tbl 
FROM '\\inp.txt ' format TEXT
DELIMITED BY '^^'
(R_EFERENCE,
 TIP_DOC,
 VID_DOC,
 DOC_DT_KT,
 N_DOC,
 DATE_DOC,
 sop_DOC,
 VAL_sop,
 ACCOUNT_A,
 NAME_CR,
 taxcode1_A,
 MFO_CR,
 BANK_A,
 ACCOUNT_B,
 NAME_DB,
 taxcode2_B,
 MFO_DB,
 BANK_B,
 ACCOUNT,
 COM,
 DAT_OD,
 TIME_OP,
 TIP,
 DOC_N,
 DAT,
 sop,
 VAL_CODE,
 RS_CR,
 KSCH_A,
 RS_DB,
 KSCH_B,
 COUNT,
 taxcode_CR,
 taxcode_DB,
 T_IME );

File imp.txt:
HSKLD0521L0LD4_P^^m^^1^^1^^    912034^^21.05.2013^^              317.29^^UAH^^  29021901100047^^ПАТ "Банк Нацiональний кредит"^^  20057663^^   320702^^"БАНК НАЦIОНАЛЬНИЙ КРЕДИ^^  26004060368059^^ДНIПРОПЕТ. МIСЬКI ТЕПЛОВI МЕРЕЖI^^  32082770^^   305299^^ПАТ КБ "ПРИВАТБАНК"^^  26004060368059^^за теплопостачання  за квiтень зг. рахунку 060600 (пр. Героїв 12/245). Платник - Чорнобровкiн Є.А.^^21.05.2013^^12:47^^1^^9120^^21.05.2013^^           317.29^^UAH^^  29021901100047^^^^  26004060368059^^^^  26004060368059^^  20057663^^  32082770^^12:47^^T
error point ------------------------------------------------------------------------------------------------------------------------------------------>                        <-

Re: An error in INPUT/LOAD command SA12

johnsmirnios — Mon, 10 Jun 2013 12:17:21 GMT

Perhaps you want to add the QUOTES OFF clause?

-john.

Re: An error in INPUT/LOAD command SA12

jack_schueler — Tue, 11 Jun 2013 10:44:58 GMT

... the QUOTES OFF clause to a LOAD TABLE statement.

Re: An error in INPUT/LOAD command SA12

Former Member — Thu, 13 Jun 2013 11:49:08 GMT

Thanks for a working decision, but I still insist that it is error for INPUT command (there absent "QUOTES OFF") and lack information in help about this unlogical behavior.

Re: An error in INPUT/LOAD command SA12

Breck_Carter — Thu, 13 Jun 2013 17:04:14 GMT

You can insist all you want, but you will still be wrong 🙂

INPUT DELIMITED BY has absolutely nothing to do with quotes. INPUT DELIMITED BY specifies what appears between adjacent fields. INPUT FORMAT TEXT specifies exactly how strings are to be formatted. It is explained in the Help for the INPUT statement: http://dcx.sybase.com/index.html#1201/en/dbreference/input-statement.html*d5e53705

TEXT Input lines are assumed to be characters, one row per line, with column values separated by delimiters. Alphabetic strings may be enclosed in single quotes or double quotes. Strings containing delimiters must be enclosed in either single or double quotes. If the string itself contains single or double quotes, double the quote character to use it within the string. You can use the DELIMITED BY clause to specify a different delimiter string than the default, which is a comma.

Three other special sequences are also recognized. The two characters \\n represent a newline character, \\\\ represents a single (\\), and the sequence \\xDD represents the character with hexadecimal code DD.

Omitted values are treated as NULL. If the value in that position cannot be NULL, a zero is inserted in numeric columns and an empty string in character columns.

If you can't use INPUT, try LOAD... it is more powerful and flexible; see http://dcx.sybase.com/index.html#1201/en/dbreference/load-table-statement.html