Scientific notation with Perl and Microsoft Excel

Home > Search
  by

Let's take an example where you are working with a script that reads and/or writes integers that are 16 characters or longer to a CSV or XLSX file. Take for example the following script, which creates a CSV file with a 28 character integer.

my $file = "/path/to/example.csv";
open(FH, '>', "$file") or die "cannot open $file $! \n";
print FH "111122223333444455556666777788889999";
close(FH);

 

In this example, when example.csv is opened in Microsoft Excel, the integer will be converted into scientific notation.

 

To remedy this, all you need to do is to place a single apostrophe before the integer.

my $file = "/path/to/example.csv";
open(FH, '>', "$file") or die "cannot open $file $! \n";
print FH "'111122223333444455556666777788889999";
close(FH);

 

The single apostrophe will prevent Microsoft Excel from converting the interger into scientific notation.



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments