A Special Kind Of Split

r.joseph has asked for the wisdom of the Perl Monks concerning the following question:

I have a CSV file (created from a client's Excel database) that may look something like this (CSV files are just Comma Seperated Values - commas as delimiters):

Brentwood,,Y,301,2+2,,1695/mo,11/22/00,Margie,"Margie 310-455-1234, Pager 310-555-9876"

As you can see, the last field in this string actually has commas in it, and therefore it is bounded by quotes ("Margie 310-455-1234, Pager 310-555-9876"). Here is my problem - I want to be able to "intelligently split" this string. In other words, I want to split on the commas UNLESS one of the fields is bounded by quotes, as I have said. But to me, it seems like the effect before the cause - in order to tell if one of the fields is bounded by quotes, we have to seperate the string into these fields, but doing this would require spliting on commas, and BAM, I am back where I started! I thought about this:

my (...) = split(/[^"],/,$string);
[download]

But haven't had the chance to test it. What do my fellow monks think about my situation?

Thanks!!
R.Joseph

Comment on A Special Kind Of Split Download Code

Replies are listed 'Best First'.
Re: A Special Kind Of Split by the_slycer (Chaplain) on Jan 27, 2001 at 11:58 UTC
Skip the regex, use one of the CSV modules. Mainly because of entries like "I said, "Hi!"". Unknown amount of quotes, or extra commas could cause many headaches.	[reply]
Re: A Special Kind Of Split by jeroenes (Priest) on Jan 27, 2001 at 13:08 UTC
Another solution: you could export into another format, excel has many options. If you use a tab-delimited database, you can just use `split('\t')`. Hope this helps, Jeroen "We are not alone"(FZ)	[reply] [d/l]
Re: A Special Kind Of Split by clemburg (Curate) on Jan 27, 2001 at 22:50 UTC
Following the advice above, I can only recommend using the CSV modules from CPAN, especially DBD::CSV - it makes input into databases so much easier just to select from one table (e.g., the CSV file) and insert into the other (e.g., the database table). And you get SQL on your CSV data for free ... That what I say three times is true: Use modules if you can. Use modules if you can. USE MODULES IF YOU CAN! Christian Lemburg Brainbench MVP for Perl http://www.brainbench.com	[reply]