[IQUG] Anyone got around this?

Mumy, Mark mark.mumy at sap.com
Fri Aug 23 08:44:52 MST 2019


Phil,

Can you send a sample of the data and the issues?  A simple create table and data file would be most welcome.

Mark

Mark Mumy
SAP Platform and Technologies Global Center of Excellence
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

From: "iqug-bounces at iqug.org" <iqug-bounces at iqug.org> on behalf of Phil Stothard <phil.stothard at GNSWorldwide.com>
Date: Friday, August 23, 2019 at 4:45 AM
To: Pankaj Pathak <pathak1611 at gmail.com>
Cc: "iqug at iqug.org" <iqug at iqug.org>
Subject: Re: [IQUG] Anyone got around this?

Yes. If this becomes a production thing rather than a one off I can hopefully get the source data changed and better delimited – this was just a dump of 48GB of existing text data to cope with! And hoping not to have to do a transformation but to deal with it in the LOAD utility if there was a simple way to do that for this exploratory phase.
Anyway thanks to those who contributed, have now loaded the data and can push on.

Phil Stothard
Chief Information Officer
Global Navigation Solutions Limited
[cid:image001.png at 01D5599F.CB481A70]
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.

From: Pankaj Pathak <pathak1611 at gmail.com>
Sent: 23 August 2019 10:13
To: Phil Stothard <phil.stothard at GNSWorldwide.com>
Cc: Mike.Garner at glencore.co.uk; rwatkins at dssolutions.com; RNaugle at DSSolutions.com; iqug at iqug.org
Subject: Re: [IQUG] Anyone got around this?

Just thought if this data is coming from sources you can filter out there then and you could have saved yourself.

Like filtering those special characters during the extraction and transformation phase. It could make life easier. :)

Thank you!!

With warm regards,
Pankaj

On Fri 23 Aug, 2019, 12:57 Phil Stothard, <phil.stothard at gnsworldwide.com<mailto:phil.stothard at gnsworldwide.com>> wrote:
OK thanks. First attempt took around 4 minutes (this is windows not unix)  to do one of the 4.5m row 600MB data files…………….. But that was just a first up quick look and may not be as efficient as it could be. Will revisit.

Phil Stothard
Chief Information Officer
Global Navigation Solutions Limited
[cid:image001.png at 01D5598C.6C0EF230]
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.

From: Mike.Garner at glencore.co.uk<mailto:Mike.Garner at glencore.co.uk> <Mike.Garner at glencore.co.uk<mailto:Mike.Garner at glencore.co.uk>>
Sent: 23 August 2019 07:53
To: Phil Stothard <phil.stothard at GNSWorldwide.com<mailto:phil.stothard at GNSWorldwide.com>>; rwatkins at dssolutions.com<mailto:rwatkins at dssolutions.com>; RNaugle at DSSolutions.com<mailto:RNaugle at DSSolutions.com>; iqug at iqug.org<mailto:iqug at iqug.org>
Subject: RE: [IQUG] Anyone got around this?

Slow ?

This one is not noticeable, it removes an occasional strange char from a massive MS SQL transfer prior to IQ load table.

                # remove a strange nvarchar(max) issue looks like ^@ ?? #
                cat -v ${DATA_DIR}/${MSSQL_DB}.${OWNER}.${TABLE_NAME}.csv | sed -e 's/\^^@/^/g' > /tmp/$$_${TABLE_NAME}.csv

I’m sure it can be done better. But it works.



From: Phil Stothard <phil.stothard at GNSWorldwide.com<mailto:phil.stothard at GNSWorldwide.com>>
Sent: 22 August 2019 17:18
To: Ron Watkins <rwatkins at dssolutions.com<mailto:rwatkins at dssolutions.com>>; Garner, Mike (London - GB) <Mike.Garner at glencore.co.uk<mailto:Mike.Garner at glencore.co.uk>>; RNaugle at DSSolutions.com<mailto:RNaugle at DSSolutions.com>; iqug at iqug.org<mailto:iqug at iqug.org>
Subject: RE: [IQUG] Anyone got around this?


External sender
Yeah if that turns out to be the only way – did try it through sed or at least through cat in powershell but is relatively slow……. This  is a pilot phase at the moment so temporarily I’ve found a fie editor that can cope with 600MB files, but will look for a better solution going forwards, hopefully within the IQ LOAD QUOTE ESCAPE options etc. And once beyond the pilot I can hopefully get this resolved at source with a better delimiter than comma.
Thanks.

Phil Stothard
Chief Information Officer
Global Navigation Solutions Limited
[cid:image001.png at 01D5598C.6C0EF230]
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.

From: Ron Watkins <rwatkins at dssolutions.com<mailto:rwatkins at dssolutions.com>>
Sent: 22 August 2019 17:14
To: Mike.Garner at glencore.co.uk<mailto:Mike.Garner at glencore.co.uk>; RNaugle at DSSolutions.com<mailto:RNaugle at DSSolutions.com>; Phil Stothard <phil.stothard at GNSWorldwide.com<mailto:phil.stothard at GNSWorldwide.com>>; iqug at iqug.org<mailto:iqug at iqug.org>
Subject: RE: [IQUG] Anyone got around this?

Why not run the file through a filter to escape the special characters you want to keep in the feed?
In unix, we use awk, sed and other tools such as python/perl.
Ron

From: iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> [mailto:iqug-bounces at iqug.org] On Behalf Of Mike.Garner at glencore.co.uk<mailto:Mike.Garner at glencore.co.uk>
Sent: Thursday, August 22, 2019 7:57 AM
To: RNaugle at DSSolutions.com<mailto:RNaugle at DSSolutions.com>; phil.stothard at GNSWorldwide.com<mailto:phil.stothard at GNSWorldwide.com>; iqug at iqug.org<mailto:iqug at iqug.org>
Subject: Re: [IQUG] Anyone got around this?

And then you find an operator in the real world who types in a free text field something like:

“Duration 09:30 || 17:30”

I’m a fan of sed and awk as well.

From: iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> <iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org>> On Behalf Of Ray Naugle
Sent: 22 August 2019 15:53
To: 'Phil Stothard' <phil.stothard at GNSWorldwide.com<mailto:phil.stothard at GNSWorldwide.com>>; iqug at iqug.org<mailto:iqug at iqug.org>
Subject: Re: [IQUG] Anyone got around this?


External sender
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.

Ray

From: iqug-bounces at iqug.org<mailto: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<mailto: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?

Thanks

Phil Stothard
Chief Information Officer
Global Navigation Solutions Limited
[cid:image001.png at 01D5598C.6C0EF230]
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/>.

LEGAL DISCLAIMER. The contents of this electronic communication
and any attached documents are strictly confidential and they may not
be used or disclosed by someone who is not a named recipient.
If you have received this electronic communication in error please notify
the sender by replying to this electronic communication inserting the
word "misdirected" as the subject and delete this communication from
your system.
_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20190823/68a5dd55/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 9947 bytes
Desc: image001.png
URL: <http://iqug.org/pipermail/iqug/attachments/20190823/68a5dd55/attachment-0001.png>


More information about the IQUG mailing list