Re: [Openroad-users] Importing data - Copy statement
Kim Ginnerup
It adds a lot of unix facilities to you windows env.
Compiled native so you can use from a normal command prompt
Kim
-----Oprindelig meddelelse-----
Fra: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com] På vegne af gareth.2...@bt.com
Sendt: 2. november 2006 16:03
Til: openroa...@peerlessit.com
Emne: Re: [Openroad-users] Importing data - Copy statement
I feared as much, thanks for the response Karl.
As a complete stab in the dark, is there such a thing as VIM scripts?,
that I could possibly execute one of those against the file, to do the
substitutions?
Cheers,
Gareth Edwards
BT Global Services
tel: +44 (0)131 345 4671
email: gareth.2...@bt.com
-----Original Message-----
From: openroad-us...@peerlessit.com
[mailto:openroad-us...@peerlessit.com] On Behalf Of Karl & Betty
Schendel
Sent: Thursday, November 02, 2006 1:49 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] Importing data - Copy statement
At 10:55 AM +0000 11/2/06, <gareth.2...@bt.com> wrote:
>OR4.1, Win2000
>
>Hi All,
>
>Having not done much with the copy statement previously, I'm a litle
lost.
>
>I have to import some data, the two formats available are:
>
>1) Comma seperated, but every data value is in "quotes"
>2) Tab delimited, money values greater than 999 are in "quotes" and
>also contain a comma to break the thousand/hundred value
>
>Are there any ways to deal with these imports? In particular the quoted
values??
There's no nice way to do it with copy. The copy statement doesn't
really understand quoted values. If you were running on a real OS I
would have suggested going with the tabbed form, and using something
like sed to strip out the quotes and commas.
Unless you have an equivalent tool for windows available (e.g. sed in
cygwin32), I'd say that your idea of reading the moneys into varchars
and working with them there is probably best.
Karl
_______________________________________________
Openroad-users mailing list
Openroa...@peerlessit.com
http://peerlessit.com/mailman/listinfo/openroad-users
_______________________________________________
Openroad-users mailing list
Openroa...@peerlessit.com
http://peerlessit.com/mailman/listinfo/openroad-users
_______________________________________________
Openroad-users mailing list
Openroa...@peerlessit.com
http://peerlessit.com/mailman/listinfo/openroad-users
Durwin Wright
Ingres 2.6 and was improved in Ingres 2006. It is similar to the Import
Assistant that Microsoft provides for Access.
Durwin Wright | Sr. Architect | Durwin...@ingres.com | Ingres | 500
Arguello Street | Suite 200 | Redwood City | CA | 94063 | USA +1
650-587-5523 | fax: +1 650-587-5550
-----Original Message-----
From: openroad-us...@peerlessit.com
[mailto:openroad-us...@peerlessit.com] On Behalf Of
gareth.2...@bt.com
Sent: Thursday, November 02, 2006 7:12 AM
To: openroa...@peerlessit.com
Subject: Re: [Openroad-users] Importing data - Copy statement
I've downloaded Sed for windows, so I'll have a play with that, which
should help with formatting prior to the copy.
gareth.2...@bt.com
Rob McKenzie
Karl & Betty Schendel
gareth.2...@bt.com
should help with formatting prior to the copy.
Cheers,
Gareth Edwards
BT Global Services
tel: +44 (0)131 345 4671
email: gareth.2...@bt.com
-----Original Message-----
From: openroad-us...@peerlessit.com
[mailto:openroad-us...@peerlessit.com] On Behalf Of
gareth.2...@bt.com
Sent: Thursday, November 02, 2006 3:03 PM
To: openroa...@peerlessit.com
Subject: Re: [Openroad-users] Importing data - Copy statement
I feared as much, thanks for the response Karl.
As a complete stab in the dark, is there such a thing as VIM scripts?,
that I could possibly execute one of those against the file, to do the
substitutions?
Cheers,
Gareth Edwards
BT Global Services
tel: +44 (0)131 345 4671
email: gareth.2...@bt.com
-----Original Message-----
From: openroad-us...@peerlessit.com
[mailto:openroad-us...@peerlessit.com] On Behalf Of Karl & Betty
Schendel
Sent: Thursday, November 02, 2006 1:49 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] Importing data - Copy statement
gareth.2...@bt.com
be running on Ingres 2006.
Can the assistant be called programatically from OpenROAD?
Cheers,
Gareth Edwards
BT Global Services
tel: +44 (0)131 345 4671
email: gareth.2...@bt.com
-----Original Message-----
From: openroad-us...@peerlessit.com
[mailto:openroad-us...@peerlessit.com] On Behalf Of Durwin
Wright
Sent: Thursday, November 02, 2006 3:31 PM
To: 'International OpenROAD Users'
Subject: Re: [Openroad-users] Importing data - Copy statement
You may want to try the Ingres Import Assistant. This was introduced in
Ingres 2.6 and was improved in Ingres 2006. It is similar to the Import
Assistant that Microsoft provides for Access.
Durwin Wright | Sr. Architect | Durwin...@ingres.com | Ingres | 500
Arguello Street | Suite 200 | Redwood City | CA | 94063 | USA +1
650-587-5523 | fax: +1 650-587-5550
Gab Bonacci
Hi all
Am I missing something.
I have a column in excel with money data.
I set the column in excel to be number format with 2 decimal places, with “Use 1000 Separator (,)” not ticked
Now when I save a tab delimited file I get a number of the form 1531.25
With no commas
Regards Gab
Gabriele Bonacci
Application Manager
KDR Creative Software Pty Ltd
( (03) 9646 1788
Ê (03) 9646 9680
From:
openroad-us...@peerlessit.com
[mailto:openroad-us...@peerlessit.com] On Behalf Of gareth.2...@bt.com
Sent: Friday, 3 November 2006 2:45
AM
To: rob.mc...@rogers.com;
openroa...@peerlessit.com
Subject: Re: [Openroad-users]
Importing data - Copy statement
Hi Rob,
Yeah, i'm aware of the d for dummy files, but i'm still stuck with the commas seperating the thousand/hundred values.
I've got sed for windows working quite nicely now. An extra step i didn't want, but it works.
Thanks to everyone for their responses.
From: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com] On Behalf Of Rob McKenzie
Sent: Thursday, November 02, 2006 3:26 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] Importing data - Copy statement
Gareth,
Why can't you make liberal use of the d data type in the copy command to skip past the quotes that are
surrounding all of your data values!!
The d stands for dummy value..and allows you to read some data, but not load it into a column in a table... let me know if you need an example...
Rob McKenzie
Phone: (905) 989-1750
Cell: (905) 715-9593
Email: rob.mc...@rogers.com
----- Original Message ----
From: "gareth.2...@bt.com" <gareth.2...@bt.com>
To: openroa...@peerlessit.com
Sent: Thursday, November 2, 2006 10:02:36 AM
gareth.2...@bt.com
gareth.2...@bt.com
I've D/L sed for windows, so I'll use that approach to format the file
before importing.
Do you know Sed? Just wandered how I would substitute the quotes for
nothing and commas for nothing on the file.
gareth.2...@bt.com
Hi All,
Having not done much with the copy statement previously, I'm a litle lost.
I have to import some data, the two formats available are:
1) Comma seperated, but every data value is in "quotes"
2) Tab delimited, money values greater than 999 are in "quotes" and also contain a comma to break the thousand/hundred value
Are there any ways to deal with these imports? In particular the quoted values??
The only way I can think of is to use option 2), with
Copy table test
( col1 = c(0)tab, col2 = c(0)tab etc...
)
Copy into a varchar column for the money values, then post-process stripping out the quotes and comma, then casting it. Which seems a very dirty & consuming way to do it.
Any help appreciated...
Cheers,
Gareth Edwards
BT Global Services
tel: +44 (0)131 345 4671
email: gareth.2...@bt.com
-----Original Message-----
From: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com] On Behalf Of neil.w...@luminary.co.uk
Sent: Thursday, November 02, 2006 2:16 AM
To: International OpenROAD Users; martin...@myworkplace.com.au
Subject: Re: [Openroad-users] App Server performance
I agree completely. Hardwired initiates should be avoided at all costs unless you are prepared to write your own nameserver logic (in which case you have not hardwired the connection anyway!) The initiate method should only be used directly in exceptional circumstances for example after invoking GetConnectionDetails earlier in the session. Performance problems are usually due to configured bottlenecks(too few slaves or process affinity) and/or poorly performing sql. Rgds Neil Warnock Sent from my BlackBerry® wireless device
-----Original Message-----
From: "Durwin Wright" <durwin...@ingres.com>
Date: Wed, 1 Nov 2006 16:25:56
To:<martin...@myworkplace.com.au>, "'International OpenROAD Users'" <openroa...@peerlessit.com>
Subject: Re: [Openroad-users] App Server performance
The name server is an OpenROAD Server Application that once initialized, does not do any additional file I/O. An initiate() method request sends a request to the OpenROAD Server. This request either starts an OpenROAD Server Slave process (ASO) if one is not running already or uses an already initialized process. A connect() method request sends a request to the OpenROAD Server. This request is routed to the OpenROAD Name Server ASO and it resolves the AKAName send to the initiate method parameters. Assuming that the Name Server is already running (by default it is configured to shutdown after 60 minutes of idle), the overhead should be between 10 to 40 mSec. This results of this request to the OpenROAD Name Server are then used to internally send an initiate() method request to connect to the target application.
The advantage of the connect() method request is that you can refer to an application by a logical name instead of needing to know the image file name and the details of the command flags. I would recommend that connect() should be the way to go.
There are two types of load balancing: (1) Dynamic Load Balancing and (2) Static Load Balancing. Each type of Load Balancing is very different.
Dynamic Load Balancing is available if you are not using ASOLib. (Connect() can be used without the need to use ASOLib.) In Dynamic Load Balancing, you specify that two or more Slaves can be started for each OpenROAD Application. The OpenROAD Server will then start the additional OpenROAD Slave processes (ORASO) if needed. It will also dynamically load balance between the processes associated with each application. Each client Call4GL() method request can be routed to a different OpenROAD Server Slave process in the pool. The OpenROAD Server Dispatcher will dynamically pick the OpenROAD Server Slave process that is the least busy.
Static Load Balancing is available only if you are using ASOLib. In this scenario, you define two or more application entries with the same AKAName. The command flags need to be different however. In each application definition, you can define one and only one ASO Slave process. You also need to leave VASA running to achieve the effects of Static Load Balancing. When a client connects to an OpenROAD Server application, the static load balance manager will bind the request to a specific OpenROAD Slave instance. Each client Call4GL() method request will be routed back to the same OpenROAD Slave process. (I am looking at relaxing the need to have a copy of VASA running to achieve make the Static Load Balancing and ASO Housekeeping active. Some of our customers have written applications that implement these items already.)
If you are using the OpenROAD Server, I would encourage you to make sure that SPOLog is always turned on. In the OpenROAD 2006 reference guide, I added documentation that describes these SPO Log events. Let me know you need access to this excerpt in case you do not have the OpenROAD 2006 Beta.
Durwin Wright | Sr. Architect | Durwin...@ingres.com | Ingres | 500 Arguello Street | Suite 200 | Redwood City | CA | 94063 | USA +1 650-587-5523 | fax: +1 650-587-5550
----------------
From: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com] On Behalf Of Martin Pomej
Sent: Wednesday, November 01, 2006 3:24 PM
To: OpenROAD Users
Subject: [Openroad-users] App Server performance
Hello OR world,
Talking about windows clients communicating to a windows 2003 server, has anyone noticed any performance gains by using either a name server connection ie ASONameServer.Connect or a direct connection ie RemoteServer.Initiate?
I understand that by using a name server you get the advantage of load balancing, but currently I'm after performance.
Regards,
Martin Pomej
MyWorkplace Solutions Pty Ltd
Ph: 1300 733 731
Mob: 0414 230 845
Em: martin...@myworkplace.com.au Making Service our Priority
<http://www.myworkplace.com.au/> www.myworkplace.com.au
White, Paul
Import using Excel or Access.
Then export without quotes.
Beware of long integers turning into floating point and losing precision eg
123456789123456 turns into 123456789000000
-----Original Message-----
From: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com]On Behalf Of Kim Ginnerup
Sent: Saturday, November 04, 2006 9:06 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] Importing data - Copy statement
Try and take a look at Gnu utils for win32,
It adds a lot of unix facilities to you windows env.
Compiled native so you can use from a normal command prompt
Kim
-----Oprindelig meddelelse-----
Fra: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com] På vegne af gareth.2...@bt.com
Sendt: 2. november 2006 16:03
Til: openroa...@peerlessit.com
Emne: Re: [Openroad-users] Importing data - Copy statement
I feared as much, thanks for the response Karl.
As a complete stab in the dark, is there such a thing as VIM scripts?,
that I could possibly execute one of those against the file, to do the
substitutions?
Cheers,
Gareth Edwards
BT Global Services
tel: +44 (0)131 345 4671
email: gareth.2...@bt.com
-----Original Message-----
From: openroad-us...@peerlessit.com
[mailto:openroad-us...@peerlessit.com] On Behalf Of Karl & Betty
Schendel
Sent: Thursday, November 02, 2006 1:49 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] Importing data - Copy statement
At 10:55 AM +0000 11/2/06, <gareth.2...@bt.com> wrote:
>OR4.1, Win2000
>
>Hi All,
>
>Having not done much with the copy statement previously, I'm a litle
lost.
>
>I have to import some data, the two formats available are:
>
>1) Comma seperated, but every data value is in "quotes"
>2) Tab delimited, money values greater than 999 are in "quotes" and
>also contain a comma to break the thousand/hundred value
>
>Are there any ways to deal with these imports? In particular the quoted
values??
There's no nice way to do it with copy. The copy statement doesn't
really understand quoted values. If you were running on a real OS I
would have suggested going with the tabbed form, and using something
like sed to strip out the quotes and commas.
Unless you have an equivalent tool for windows available (e.g. sed in
cygwin32), I'd say that your idea of reading the moneys into varchars
and working with them there is probably best.
Karl
_______________________________________________
Openroad-users mailing list
Openroa...@peerlessit.com
http://peerlessit.com/mailman/listinfo/openroad-users
Sean Thrower
I take it the original file is a Excel special! I've had this situation before, and found it simple to handle using an sql script containing 2 COPY TABLEs, + UPDATE + INSERT-SELECT..
The first COPY TABLE handled all records with moneys < 999, uploading them into the target table; all the records in "error" were redirected to a second file using standard COPY TABLE "with" features.
The next statement created a session temporary table as select from target table where 1=0, with an extra varchar column for each money column.
A second COPY TABLE was identical to the first except that it loaded the session table from the "error" file, putting the moneys into the varchar columns.
The UPDATE statements simply string-manipulated the varchar moneys to proper money format, in the session table.
The INSERT-SELECT copied the statements from the session table to the target table.
It was easy to write and test and run - in general I would say, I've had many in-the-field data-conversion/ input situations, and never yet found the need to use anything but SQL in the simplest of shell harnesses.
Regards,
Sean.
-- Sean Thrower Senior Consultant, Ingres Corporation Tel: +44 (0)1753 559532 Fax: +44 (0)1753 559550 Mob: +44 (0) 7736 103089 Email: sean.t...@ingres.com
Durwin Wright
where they were going to expose a number of VDBA components as ActiveX
controls.
I will try to find out if it can be called from OpenROAD. Conceptually, it
uses COPY TABLE to import and export the data. It is essentially a wizard
written in MFC, yuck :-( that uses Ingres Embedded SQL/C to access the LIBQ
component. There is no reason that the same technique could not be
replicated using OpenROAD.
COPY TABLE is implemented by the LIBQ component of the Ingres Front Ends.
The OpenROAD runtime sits directly on top of LIBQ and has access to the same
functionality. Maybe someone has written something in OpenROAD already that
does this and they are willing to share.
This is the type of project that could become an eventually OpenROAD
community project.