Read/Write Excel on Windows & Linux

On windows read/write Excel is easy. Set up ODBC for read and write connections on source and destination Excel files. For some reason Microsoft does not allow delete records in Excel through the ODBC interface (actually might be in any other interfaces), so delete can be done by replacing the current Excel with a blank Excel at the File System level.

In linux, there are modules created by people in PHP to do this, such as PHPExcel. So you download the library and use it. In Perl, the modules Spreadsheet::ParseExcel and Spreadsheet:WriteExcel are written by Takanori Kawai and John McNamara in 2000 and tincluded into CPAN. If your Perl installation does not come with this, install them (e.g., using PPM on windows or CPAN on linux). Some example code are here. This code works for Excel up to version 2000. I didn't try on more recent versions. On windows Perl also can manipulate Excel using the Win32::OLE package which is recommended.

To write Excel file, there is a shortcut to output to a *.csv file. A *.csv file uses comma as delimiter and is opened by Excel, then you can save it as a true Excel file. To escape comma in a *.csv file, quote the entry by double quotes, e.g., "a,b". To include a double quote in an quoted entry, replace it with two double quotes, e.g., "a""b".