PERL CSV and commas in fields 3

[Arion23]

Hi all,

I'm still fairly new to PERL, but I'm posed with a problem I'm hoping someone can assist with.

I've been asked to write a script that performs a search on a (rather large) CSV datasource, outputing the results in HTML.

An example row from the CSV file (generated by saving an MS Excel spreadsheet as CSV) is:
0011423,Bathurst,12,"Southern Bell, Northern Bell", Blue

This row has 5 fields, the fourth containing a ',' - in this format will the PERL CSV DBI/DBD modules be able to handle this as a 5 field row, or will the embedded comma confuse everything? If it is going to cause a problem, are there any suggestions on how to parse this file in PERL (without modifying the CSV file)?

Many thanks in advance....

 

[sophisticate]

I'm not familiar with the CSV driver for DBD, but parsing the line shouldn't be a problem, even with that extra comma. All you need to do is run a regular expression on the line that converts commas inside two containing characters (in this case, quotation marks) to some otherwise-never-used character, say, tildes.. then do your split and replace the tildes with commmas.

Are your clients aware of how resource consuming a search on a CSV file is? If they aren't constantly changing the file, you might consider writing a script that parses the file into some other DB format (MySQL, Postgres, Berkeley, etc.) which can be executed each time the spreadsheet is changed, and another script to search the database.

Hope this helps,

brendanc@icehouse.net

 

[tsdragon]

If the perl dbi csv module is written correctly it should be able to handle the embedded comma with no problem, as long as the field containing it is surrounded by quotes. That's part of the CSV standard. Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[Arion23]

Thanks both of you, pretty much answered my query.

Sophisticate: Yup, I've raised performance issues with them regarding the use of a CSV file as a datasource, particularly with large numbers of records (currently its sitting at around 7000 rows, and is expected to reach 20000) - but they are adamant of using CSV over other DB technologies (even though they have SQL server installed, go figure...)

Again, many thanks...

 

[tsdragon]

I'm sure glad my boss is technically literate! AND... he actually listens to me!

Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[kempis555]

Wow, that's the first I've heard of the CSV stuff inside of the DBI module. I looked at the manpages, and I was wondering...

Say you need a CGI that requires a database, but the server it's on doesn't have a database program running on it right now, but there's plans to get one on there eventually.

Would a script using the DBI:CSV be easily to modify to use a regular database? Seems like it.


-k

 

[Arion23]

From what I've seen of CSV and the Perl DBI/DBD modules, they allow you to SQL queries on the file, as if it were a regular DB. You can then take that code (and with a few modifications to defining the datasource) and use it as an interface to another database type...