spreadsheet format

Al C dont_send_me_anything at serverisdownforever.com
Mon Mar 17 17:45:35 CET 2003


user at domain.invalid wrote in news:b544jt$lur$1 at ctb-nnrp2.saix.net:

> This not really a python question, but here it goes:
> 
> I have data with the format
> 
>      Name\Header     Header1     Header2     Header3...     HeaderN
>      name1          4     5     6          6
>      name2          5     6     4          3
>          .
>      .
>      .
>      nameN          6     56     6          1
> 
> My problem is if I email the data as plain text the formatting
> is usually lost, especially when printing. So, I looked at
> Comma Separated Values (CSV) and Data Interchange Format (DIF).
> Finding The CSV format specifaction has been a mission, but
> from what I gatherd the spec was to general. For instance,
> the is no predifined value seperator, and there is no distinction
> between text and number values. 

The best way to get around the problem about "no distinction
between text and number values" is to have the recipient 
specify a format for each column where this might be a problem
when they import the file into their spreadsheet.  The main trouble
is that strings that might be numbers are treated as numbers, and
thus the leading zeroes are lost, even if the string is enclosed in
tick marks or whatever, so you have to specify Text, not General
(the default), as column format on the import screen.  If you have
a reasonably astute recipient and a reasonably small number of
problematic columns, this should work OK.  

The format for CSV (as used by Excel) is approximately as follows:
1. Commas separate values.

2. If a value contains commas, wrap it in quote marks  eg " a, b, or c".

3. If a value contains quotes, wrap it in quotes and double the embedded
quotes, eg """It sometimes works,"" he said."

4. Rows end with CRLF.

5. Linebreaks within a field are indicated by a CR (Ascii 13) only, IIRC.  
(It might be a LF (Ascii 10), I'm not 100% sure.)
 
Some versions of Excel seem to have trouble with fields longer than 255
characters, so be careful if you have long fields.
    	

Al 




More information about the Python-list mailing list