Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Perl (http://www.go4expert.com/articles/perl-tutorials/)
-   -   Parsing CSV File Using Perl (http://www.go4expert.com/articles/parsing-csv-file-using-perl-t11157/)

pradeep 6Jun2008 12:45

Parsing CSV File Using Perl
 

Introduction



CSV is a comma separated value data file is a physical ASCII file structure that contains records whose values are delimited or separated by commas. CSV is a common file type which can be imported into spreadsheet applications such as MS-Excel, database applications such as MS-Access, and contact databases such as MS-Outlook, ACT, Goldmine, etc. The format dates back to the early days of business computing. For this reason, CSV files are common on all computer platforms.

CSV is one implementation of a delimited text file, which uses a comma to separate values (where many implementations of CSV import/export tools allow an alternate separator to be used; as is shown in the MS Access screen shot, below). However CSV differs from other delimiter separated file formats in using a " (double quote) character around fields that contain reserved characters (such as commas or newlines). Most other delimiter formats either use an escape character such as a backslash, or have no support for reserved characters.

In some application you might need to allow user to upload a CSV data file, which is processed and inserted to the DB, thereby avoiding the direct interaction of the user with the DB, the user can prepare his/her report/data in MS Excel, or a similar spreadsheet program which can be later uploaded using a web interface.

Parsing The CSV File Programatically



The simplest solution would be to read the uploaded file line-by-line and splitting the lines by commas, but this is not fool proof, just in case the one of the fields contain a comma which is not a delimiter but a part of the data, then your code goes for a toss. So, we we'll use the popular CSV parsing module, Text::CSV to get the work done for us.

Code: Perl

$csv = Text::CSV->new();              # create a new object
 $status  = $csv->parse($line);        # parse a CSV string into fields
 @columns = $csv->fields();            # get the parsed fields

The above code snippet shows the basic usage of this module. You can get more info here http://search.cpan.org/~makamaka/Text-CSV-1.05/lib/Text/CSV.pm

A Complete Upload and Parsing Script



This script receives a CSV file via file upload, and parses the CSV file and display them in a HTML table. Hope this is helpful to you.

Code: Perl

#!/usr/bin/perl
 
 ## parsing a CSV file uploaded using a web interface, using Text::CSV
 
 use strict;
 use CGI;
 use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;
 use Text::CSV;
 use Data::Dumper;
 
 my $csv = Text::CSV->new(); ## pass parameter {binary=>1} if you want to handle non-ascii chars
 my $cgi = new CGI;
 
 if($ENV{'REQUEST_METHOD'} eq 'POST')
 {
     print $cgi->header();
     my $h = $cgi->upload('up');
 
     my $total_rows = 0;
 
     print "<table border=1>";
     
     while(my $line = <$h>)
     {
         if($csv->parse($line))
         {
             print q(<tr>);
             print qq(<td>$_</td>) for($csv->fields());
             print q(</tr>);
         }
     }
 
     print "</table>";
 }
 else
 {
     print $cgi->header(),qq(<html><head><title>CSV Upload Parser</title>
         </head>
         <body>
         <form method="POST" enctype="multipart/form-data">
         <input type="file" name="up"><br><br>
         <input type="submit" name="bt" value="Upload">
         </form>
         </body>
         </html>);
 }



All times are GMT +5.5. The time now is 19:09.