[IQUG] Anyone got around this?
pathak1611 at gmail.com
Thu Aug 22 05:45:00 MST 2019
Hope you are doing well !!
Above link has few good options you can try and error , like
- QUOTE for TEXT data only; identifies the enclosure character to be
placed around string values. If not specified, the default QUOTE character
is either a single (') or double (") quotation mark, depending on what is
used in the field. If QUOTES OFF is defined, QUOTE is ignored.
If the specified <enclosure_character> is multibyte, only the first byte
is used; the remaining bytes are ignored.
- QUOTE ESCAPE specifies the escape character used in the data. If not
specified, the default QUOTE ESCAPE character is the value of QUOTE. For
example, if QUOTE is defined as percent (%), but QUOTE ESCAPE is not
defined, the default value for QUOTE ESCAPE becomes %. If neither QUOTE
ESCAPE nor QUOTE are defined, QUOTE defaults to either a single (') or
double (") quotation mark, depending on what is used in the field, and
QUOTE ESCAPE defaults to match QUOTE.
If the specified ESCAPE character is multibyte, only the first byte is
used; the remaining bytes are ignored.
If QUOTES ON and QUOTE ESCAPE is not defined, single quote becomes the
ESCAPE character and must be escaped by another quote.
NoteQUOTE ESCAPE clause not supported when loading CSV file into IQ
catalog store tables. Syntax not supported message appears.
- QUOTES indicates that input strings are enclosed in quote characters.
QUOTES is an optional parameter and is ON by default. The first such
character encountered in a string is treated as the quote character for the
string. String data must be terminated with a matching quote.
With QUOTES ON, column or row delimiter characters can be included in
the column value. Leading and ending quote characters are assumed not to be
part of the value and are excluded from the loaded data value.
To include a quote character in a value with QUOTES ON, use two quotes.
For example, this line includes a value in the third column that is a
single quote character:
‘123 High Street, Anytown’, ‘(715)398-2354’,’’’’
With STRIP turned on (the default), trailing blanks are stripped from
values before they are inserted. Trailing blanks are stripped only for
non-quoted strings. Quoted strings retain their trailing blanks. Leading
blank or TAB characters are trimmed only when the setting is ON.
The data extraction facility provides options for handling quotes (
TEMP_EXTRACT_QUOTES,TEMP_EXTRACT_QUOTES_ALL, and TEMP_EXTRACT_QUOTE). If
you plan to extract data to be loaded into an IQ main store table and the
string fields contain column or row delimiter under default ASCII
extraction, use theTEMP_EXTRACT_BINARY option for the extract and the
FORMAT binary and QUOTES OFF options for LOAD TABLE.
- QUOTES ON applies only to column-delimited ASCII fields.
- With QUOTES ON, the first character of a column delimiter or row
terminator cannot be a single or double quote mark.
- QUOTES ON forces single threaded processing for a given file.
- The QUOTES option does not apply to loading binary large object
(BLOB) or character large object (CLOB) data from the secondary file,
regardless of its setting. A leading or trailing quote is loaded
as part of
CLOB data. Two consecutive quotes between enclosing quotes are loaded as
two consecutive quotes with the QUOTES ON option.
- SAP ASE BCP does not support the QUOTES option. All field data is
copied in or out equivalent to the QUOTES OFF setting. As QUOTES
ON is the
default setting for the SAP IQ LOAD TABLE statement, you must specify
QUOTES OFF when importing ASE data from BCP output to an SAP IQ table.
- If LOAD TABLE encounters any nonwhite characters after the ending
quote character for an enclosed field, this error is reported
and the load
operation is rolled back:
Non-SPACE text found after ending quote character for
an enclosed field.
SQLSTATE: QTA14 SQLCODE: -1005014L
- With QUOTES ON, if a single or double quote is specified as the
first character of the column delimiter, an error is reported
and the load
Single or double quote mark cannot be the 1st character
of column delimiter or row terminator with QUOTES option
SQLSTATE: QCA90 SQLCODE: -1013090L
- ESCAPES if you omit a <column-spec> definition for an input field
and ESCAPES is ON (the default), characters following the backslash
character are recognized and interpreted as special characters by the
database server. You can include newline characters as the combination \n,
and other characters as hexadecimal ASCII codes, such as \x09 for the tab
character. A sequence of two backslash characters ( \\ ) is interpreted as
a single backslash. For SAP IQ, you must set ESCAPES OFF.
Please go through might be helpful for you !!
And please don't forget to right about the solution here , if it gets fix.
On Thu, Aug 22, 2019 at 5:38 PM Phil Stothard <
phil.stothard at gnsworldwide.com> wrote:
> In LOAD TABLE (IQ16), QUOTES ON seems to look for both double quotes and
> single quotes. Is there any way to restrict that?
> It happens a lot, data coming in files has double quotes for the “proper”
> quoted strings generally because there is a comma in there, but then some
> of the text contains the odd single quote as that is a valid part of the
> data. Generally I have stuck the file into notepad++ and removed or changed
> the single quotes, but data I’m working with right now is too big to load
> even in notepad++. WE have another route via a memory based editor but that
> also can’t cope with 600MB files.
> So QUOTES ON fails because the single quotes in the main text are picked
> up as quoted field delimiters, and QUOTES OFF fails because then the commas
> in the “ “ fields are picked up as column delimiters. Ideally want a QUOTES
> ON but only double quotes.
> Sadly I have no control at all over the source data so can’t use a
> different column delimiter, and as I say I have no easy notepad++ type way
> of removing the single quotes.
> Anyone cracked this one?
> *Phil Stothard*
> *Chief Information Officer*
> *Global Navigation Solutions Limited*
> [image: cid:B501F5BA-5F0B-467E-A9DE-98FE351F52B2]
> 17 Elm Road, North Shields, Tyne & Wear NE29 8SE, UK
> M: +44 (0) 7848 035985
> *www.gnsworldwide.com <http://www.gnsworldwide.com/>*
> Global Navigation Solutions Limited. Registered in England at the above
> address with No. 08190145.
> The information in this e-mail and any attachment is confidential and may
> be legally privileged. They are intended solely for the named recipient and
> access to this e-mail by anyone else is unauthorised. If you are not the
> named recipient please notify the sender immediately and do not distribute,
> make copies or disclose the content to others.
> *© Global Navigation Solutions 2016. *Views expressed in this e-mail are
> not necessarily those of Global Navigation Solutions.
> The information contained in this communication from the sender is
> confidential. It is intended solely for use by the recipient and others
> authorized to receive it. If you are not the recipient, you are hereby
> notified that any disclosure, copying, distribution or taking action in
> relation of the contents of this information is strictly prohibited and may
> be unlawful.
> This email has been scanned for viruses and malware, and may have been
> automatically archived by *Mimecast Ltd*, an innovator in Software as a
> Service (SaaS) for business. Providing a *safer* and *more useful* place
> for your human generated data. Specializing in; Security, archiving and
> compliance. To find out more Click Here
> IQUG mailing list
> IQUG at iqug.org
-------------- next part --------------
An HTML attachment was scrubbed...
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 9946 bytes
Desc: not available
More information about the IQUG