On Fri, 1 Jun 2007 12:58:05 +0200, Anton Berezin <[email protected]> wrote:
> Ok, so I am generating a CSV file (where "C" obviously stands for > "semicolon") and sending it out to commercial folks so that they distribute > relevant information to [friendly] customers for trials. > > The file has a number of rows with three columns each, like this: > > something;something else;1234567890123456 > > The attachments are opened in Excel - by default - and the user sees > > something something else 1.234E15 > > If that were all, I would probably get a friendly WTF mail from the > commercial guy, and look for a way to solve the "problem". But no. When > the cell with "1.234E15" is clicked, the Excel helpfully displays the > complete number in the input field at the top. That number is > 1234567890123450. > > I am glad those customers are supposed to be friendly; the future > friendliness of the commercial guy depends on the number of "oops" mails he > has to send now. > > I experimented a bit, and > > 1234567890123456 > 1234567890123456 > "1234567890123456" > > are all silently and without complaint converted to a floating point number. > > '1234567890123456' gets converted to '1234567890123456', which is better, > but only slightly. > > Graaaaah! Not to just be bluntly promoting my own modules, but the recent versions of text Text::CSV_XS perl module now comes with a utility called "csv2xls" which converts CSV files to Excel. It's easy to modify that to you needs and *force* those columns to text. Now try loading this in Excel: 20070512,20071205,05-12-2007,12-05-2007,12/05/2007,05/12/2007,"20070512",... etc etc, preferably under a locale that does not use the braindead american MMDDYYYY sequence. Try opening the csv in three ways: 1. double-click in internet-exploder, 2. open M$-Excel and open the csv from the file menu, and 3. open M$-Excel and go to Data-Import-FromFile and choose the CSV file. Compare the results. That was so much hate that I wrote csv2xls. -- H.Merijn Brand Amsterdam Perl Mongers (http://amsterdam.pm.org/) using & porting perl 5.6.2, 5.8.x, 5.9.x on HP-UX 10.20, 11.00, 11.11, & 11.23, SuSE 10.0 & 10.2, AIX 4.3 & 5.2, and Cygwin. http://qa.perl.org http://mirrors.develooper.com/hpux/ http://www.test-smoke.org http://www.goldmark.org/jeff/stupid-disclaimers/
