[Openroad-users] Importing data - Copy statement

[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

*  gab.b at kdr.com.au

 

  _____  

From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of
gareth.2.edwards at bt.com
Sent: Friday, 3 November 2006 2:45 AM
To: rob.mckenzie at rogers.com; openroad-users at 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.

 

Cheers, 
Gareth Edwards 

BT Global Services 
tel: +44 (0)131 345 4671 
email:  <mailto:daryn.piper at bt.com> gareth.2.edwards 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/20061103/98cd123a/attachment-0001.html