Getting comma separated number from comma separated file 
Author Message
 Getting comma separated number from comma separated file

Hi,

Apologies if this is a FAQ, but I couldn't find the information
anywhere....

I have a csv, that looks like this

a,b,c,d,e,f,$1,234,567.00,other,stuff

How can I extract the fields out correctly? A simple split won't work,
and I had real trouble thinking up a regex that will remove the ','(s)
in the number.

The number will always start with '$' and end with '.00', but may vary
between $0.00 and some large number.

If this is not in the FAQ, then i vote it gets added!

Any help much appreciated.

Thanks!

James

Sent via Deja.com
http://www.*-*-*.com/



Wed, 23 Jul 2003 04:50:19 GMT  
 Getting comma separated number from comma separated file
: I have a csv, that looks like this
:
: a,b,c,d,e,f,$1,234,567.00,other,stuff

A "proper" CSV will have quotes around fields containing commas.

: How can I extract the fields out correctly? A simple split won't work,
: and I had real trouble thinking up a regex that will remove the ','(s)
: in the number.

For arbitrary values in the other fields, there's no way to do it.  If the
only place where a comma will be followed by three digits is inside a
currency field, then this will work:

  $str = 'a,b,c,d,e,f,$1,234,567.00,other,stuff';


Output:  a:b:c:d:e:f:$1,234,567.00:other:stuff

The split pattern uses negative lookahead to ignore commas which are
followed by three digits.

Do note again that your real problem here is improper CSV format, though.

--
   |   Craig Berry - http://www.cinenet.net/~cberry/
 --*--  "When the going gets weird, the weird turn pro."
   |               - Hunter S. Thompson



Wed, 23 Jul 2003 06:03:16 GMT  
 Getting comma separated number from comma separated file

(various snippage)

Quote:
> I have a csv, that looks like this

"...looks like this"

Posting a database sample which is not a factual
representation of a database in use, is rather
a poor choice when asking for help. Does your
example precisely represent a database sample
or have you simply posted something unrelated?

Quote:
> a,b,c,d,e,f,$1,234,567.00,other,stuff
> How can I extract the fields out correctly?

This is not a question of how to extract data
correctly. This is a question of why you did
not correctly format your database. A database
should not contain meta-characters and, a comma
delimited database should not contain monetary
values delimited with commas.

This displayed database sample, which is quite
dubious not being a true sample, indicates you
have made poor choices in database management
and are not qualified to write Perl scripts
which manage a database. An inability to
create a correct database is a reflection of
an inability to write correct Perl code.

Edit your database to remove meta-characters,
such as a dollar sign, edit your database to
delimit monetary values with a character other
than a comma. This would be an intelligent
and most logical solution for these problems
you, yourself, created.

Godzilla!
--

This test script shows a method. However, it
will fail if you have a single digit data entry,
followed by another single digit data entry,
both delimited by a comma.

Learn how to establish a correct and effective
database, then learn how to write Perl code.

TEST SCRIPT:
____________

#!perl

print "Content-type: text/plain\n\n";

$string = 'a,b,c,$1,234,567.00,d,e,f';

$string =~ tr/$//;
$string =~ s/(\d),(\d)/$1?$2/g;


 {
  $element =~ tr/?/$,/;
  print "$element ";
 }

exit;

PRINTED RESULTS:
________________

a b c $1,234,567.00 d e f



Wed, 23 Jul 2003 06:35:34 GMT  
 Getting comma separated number from comma separated file

[question (which is getting ample discussion) snipped]

Quote:
>If this is not in the FAQ, then i vote it gets added!

A FAQ contains Frequently Asked Questions.  Even if you find your
question interesting, voting doesn't make it frequently asked.

Anno



Sat, 26 Jul 2003 00:04:14 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Comma separated numbers

2. Help reading comma-separated values from file

3. comparing a folder full of files to a comma,separated list

4. Comma Separated Value (CSV) file, parsing

5. Heads up: comma separated values (CSV) module

6. Heads up: comma separated values (CSV) module

7. Looking for code to parse records with comma separated values

8. Convert excel(xls) to plain comma-separated text

9. Comma to separate numeric string (thousandth separator)

10. COnverting comma separate values

11. CSV (comma separated, quoted)

 

 
Powered by phpBB® Forum Software