on 2013 Jun 10 12:01 PM
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 ------------------------------------------------------------------------------------------------------------------------------------------> <-
Request clarification before answering.
Perhaps you want to add the QUOTES OFF clause?
-john.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
... the QUOTES OFF clause to a LOAD TABLE statement.
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
User | Count |
---|---|
45 | |
9 | |
8 | |
6 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.