[Openroad-users] Importing data - Copy statement

[gareth.2.edwards at bt.com]

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.
 

Cheers, 
Gareth Edwards 

BT Global Services 
tel: +44 (0)131 345 4671 
email: gareth.2.edwards at bt.com <mailto:daryn.piper at bt.com>  

 


  _____  

	From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at 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.mckenzie at rogers.com


	----- Original Message ----
	From: "gareth.2.edwards at bt.com" <gareth.2.edwards at bt.com>
	To: openroad-users at peerlessit.com
	Sent: Thursday, November 2, 2006 10:02:36 AM
	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.edwards at bt.com
	
	-----Original Message-----
	From: openroad-users-bounces at peerlessit.com
	[mailto:openroad-users-bounces at 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.edwards at 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
	Openroad-users at peerlessit.com
	http://peerlessit.com/mailman/listinfo/openroad-users
	
	
	
	_______________________________________________
	Openroad-users mailing list
	Openroad-users at peerlessit.com
	http://peerlessit.com/mailman/listinfo/openroad-users


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://peerlessit.com/pipermail/openroad-users/attachments/20061102/82ce395a/attachment.html