Exporting to Excel from PastPerfect

One of our collections uses Museum Software’s PastPerfect database, and as part of the barcoding project, they’re making lists of objects to have barcode labels printed for. You can export these lists to Excel, and BarTender can use Excel as a data source, so you’d think this would be a pretty easy workflow, and for the most part it is.

But it turns out that there are some objects in the collection with catalog numbers like “3.2E12”. And Excel really wants to be helpful, so when it sees something that’s almost in scientific notation, it nudges it into the right format. So that catalog number becomes “3.2E+12”, or “3.20E+12”, and it doesn’t keep the original string around anywhere. Even if you convert those fields back to text, they stay reformatted. So it has to be fixed before Excel imports it.

As it happens, PastPerfect isn’t writing an Excel file directly. It’s writing an HTML file with a table, which Excel apparently knows how to import. And there’s a magic CSS declaration you can add to the HTML file to make Excel treat all the cells as text, rather than trying to convert any of them to a number or date format. This is the line:

td { mso-number-format:"\@"; }

If you add that after the <style> tag in the exported HTML file, Excel will import everything as text, and you don’t end up with catalog numbers changing from “4.6E7” to “46000000”.

You can do this with a simple perl script, if you install Strawberry Perl or another Perl-For-Windows package. I did it like so:

$file = $ARGV[0];
open(FILE,"<$file");
open(TMP, ">tmpfile");

while (<FILE>) {
	s/<style>.*$/<style> td { mso-number-format:"\\@"; }/i;
	print TMP;
}
close FILE;
close TMP;
unlink $file;
rename "tmpfile", $file;

That file goes into c:\PP5Reports\excelfix.pl. Then there’s a tiny .cmd file which goes in the same directory, named excelfix.cmd:

perl c:\pp5reports\excelfix.pl %1

When you export a report from PastPerfect, it will save it as PPSResults.htm in that folder, and open it in Excel. Close it in Excel and drag the .htm file onto the excelfix.cmd file in a file explorer window. The .htm file will be modified to have the css rule which tells Excel to import as text. Now you can open it in Excel again, and Excel won’t have munged any of your cells.