[IQUG] Anyone got around this?

Ray Naugle rnaugle at dssolutions.com
Thu Aug 22 07:53:13 MST 2019

I get data from a number of sources in varying degrees of “quality”.  I operate in a UNIX environment and typically do a lot of pre-processing to make data in a more standard format that I typically load.  When I get data with double quotes, I have found that every column has that around the data is double quoted and a comma separator.  In cases like that I run the data through some edit filters (sed).  When dealing with double quotes I change all [ “,” ] (quote comma quote) pieces of the string to a single pipe character  [ | ], along with the beginning and end of line quotes.  I load with QUOTES OFF with no issue – the single and double quotes in the text are included in the column load fine.


Hope this helps.




From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of Phil Stothard
Sent: Thursday, August 22, 2019 5:07 AM
To: iqug at iqug.org
Subject: [IQUG] Anyone got around this?


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


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 <http://www.mimecast.com/products/> .

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20190822/efda8b68/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/efda8b68/attachment-0001.png>

More information about the IQUG mailing list