How to Convert XLS to CSV on Perl

Written by roy huggins
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Convert XLS to CSV on Perl
Perl is useful for converting data quickly. (calculation image by lefty from Fotolia.com)

Companies commonly use Excel to store tables of data. Often, those companies will find a use for that data that requires converting it into a different format for other software to use. But Excel files aren't designed to be read by other programs. The data they contain needs to be converted to a common format, such as Comma Separated Value (CSV) files. For people with a lot of Excel files to convert, the usual process is cumbersome. So Perl's ability to handle data-processing tasks quickly and easily comes in handy in this case.

Skill level:
Moderate

Other People Are Reading

Things you need

  • Perl or Activestate Perl
  • CPAN if you have Perl
  • PPM if you have Activestate Perl

Show MoreHide

Instructions

  1. 1

    Install the Perl module "Spreadsheet::ParseExcel" on your system. This is the standard Perl module for reading data from Excel files. The process of installation depends on your system type.

    If you have Activestate Perl (i.e.,. Perl for Windows):

    Open a Command Prompt window. Go to the "bin" subfolder of the folder where your Activestate Perl program is located. If you don't know where it is, use the Windows search feature to find "Activestate" and make a note of the "directory path" to the Activestate Perl folder. Then go to your Command Prompt window and type "DIR" followed by that directory path and then press Return. Then type "DIR bin" and press Return.

    Type "ppm" then press Return. Some text will appear and then you'll be able to type commands again. Type "install Spreadsheet::ParseExcel" and press Return. Some more text will appear and then the installation will be completed.

    If you have standard Perl:

    Open a command prompt or terminal window. In Mac OS X, the program is called Terminal. Type the following:

    perl -MCPAN -e install Spreadsheet::ParseExcel

    Press Return. A lot of text will appear and then installation will be complete.

  2. 2

    Make your Excel file accessible to your Perl script. Make a copy of the Excel file with the name "xls_test.xls" for this tutorial and move it into the folder where your Perl script is located.

  3. 3

    Load your Excel file into your Perl script with this code:

    use Spreadsheet::ParseExcel;

    my $xlsparser = Spreadsheet::ParseExcel->new();

    my $xlsbook = $parser->parse('xsl_test.xls');

    my $xls = $xls->worksheet(0);

    Now we have an object ("$xls") that holds the data from the first worksheet of our Excel file. If you want to get data from a different worksheet, replace the zero ("0") in the fourth line with the number of your worksheet minus one. For example, the first worksheet's number is zero ("0"), the third worksheet's number is two ("2") and so on. If your Excel file only has one worksheet, use the code as-is.

  4. 4

    Find out what columns and rows your spreadsheet has. Use this code:

    my ( $row_first, $row_last ) = $xls->row_range();

    my ( $col_first, $col_last ) = $xls->col_range();

  5. 5

    Create a variable to store the CSV data. Use this code:

    my $csv = '';

  6. 6

    Create the CSV data. Use this code to do it:

    for my $row ( $row_first .. $row_last ) { #Step through each row

    for my $col ( $col_first .. $col_last ) { #Step through each column

    my $cell = $xls->get_cell( $row, $col ); #Get the current cell

    next unless $cell;

    $csv .= $cell->unformatted(); #Get the cell's raw data -- no border colours or anything like that

    if ($col == $col_last) {

    $csv .= "\n"; #Make a new line at the end of the row

    } else {

    $csv .= ","; "Add a comma between each item

    }

    }

    }

  7. 7

    Save the data in $csv to a file using Perl's file-writing functions. Make sure your file name ends in ".csv" for clarity.

Tips and warnings

  • Excel files are much more complicated than CSV files. This will work for Excel files with a header row or no header row followed by rows of data. Anything else requires more complicated programming.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.