[IQUG] Anyone got around this?

Pankaj Pathak pathak1611 at gmail.com
Thu Aug 22 05:45:00 MST 2019

HI Phil,

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 (
   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
      operation fails:

      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.

Cheers !!


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?
> Thanks
> *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.
> *Disclaimer*
> 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
> <http://www.mimecast.com/products/>.
> _______________________________________________
> IQUG mailing list
> IQUG at iqug.org
> http://iqug.org/mailman/listinfo/iqug
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20190822/813df075/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 9946 bytes
Desc: not available
URL: <http://iqug.org/pipermail/iqug/attachments/20190822/813df075/attachment-0001.png>

More information about the IQUG mailing list