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 ------------------------------------------------------------------------------------------------------------------------------------------>                        <-

asked 10 Jun '13, 11:01

Serge's gravatar image

Serge
1266715
accept rate: 0%

edited 10 Jun '13, 11:15


Perhaps you want to add the QUOTES OFF clause?

-john.

permanent link

answered 10 Jun '13, 12:17

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

1

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

(11 Jun '13, 10:44) JBSchueler

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.

(13 Jun '13, 11:49) Serge
Replies hidden
Comment Text Removed
3

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

(13 Jun '13, 17:04) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×85
×5

question asked: 10 Jun '13, 11:01

question was seen: 1,893 times

last updated: 14 Jun '13, 03:49