REBOL [
    Title:  ".csv to EPOC32 Sheet converter"
    Date:   3-Mar-2000
    Author: "Bohdan Lechnowsky"
    Email:  amicom@sonic.net
    File:   %csv2sheet.r
    Purpose: {
        To convert .csv files to EPOC32's "Sheet" (spreadsheet)
        application format.

        I was frustrated by the lack of anything other than
        PsiWin to convert .csv files, especially since I don't
        own a Windows machine, so I decided to try to crack
        the file format.
    }
    Notes: {
        Currently can only successfully convert .csv files 
        with 8 or less total cells...not very useful.

        Download REBOL/core from http://www.rebol.com.  It's
        free and available for just about any 32- or 64-bit
        operating system.  An EPOC32 port of REBOL is also
        underway.  Very small and no installation needed.

        After putting .csv data into the REBOL word DATA below
        and creating a document using EPOC32 Sheet which
        mirrors what the .csv file should look like in Sheet,
        run this script in REBOL with the following command
        from the REBOL command prompt:

            do %csv2sheet.r

        Enter:

            print diffs-out

        at the REBOL command line after executing the above
        to see which locations are different between the
        EPOC32's Sheet result and the script's result.

        You can also open the resulting file on your EPOC32
        device.  It may cause the application to bomb, so be
        careful (shouldn't cause any loss of data, but don't
        hold me responsible if it does).

        You can also enter:

            print psion-out
            print body-out

        to see the actual hexadecimal contents of the real
        EPOC32 Sheet file and the script-created Sheet file.

        If you don't want to learn REBOL but just want to
        catalog what changes occur with each minor variant
        of the .csv data compared to the parallel Sheet data,
        feel free to send those findings to the above
        address.

        As a test, the sample DATA below and the parallel 
        Sheet file should only vary in one location.

        If anyone wants to convert this to OPL32, please
        feel free (and then send me a copy) :-)
    }
]

;note: ^/ below means END-OF-LINE

data: {alpha,beta,c^/delta,e^/f,gamma}

int2hex: func [int digits][
    form skip tail to-hex int negate digits
]

lastpos: func [data /local row col][
    row: 0 col: 0
    foreach char data [
        if char = #"," [col: col + 1]
        if char = #"^/" [col: 0 row: row + 1]
    ]
    reduce [col row]
]

parsed-data: parse data ",^/"

length1: (length? rejoin parsed-data) + (5 * length? parsed-data)

last-pos: lastpos data

header: rejoin [
{370000106D00001088000010A8150855} int2hex 96 + length1 2 {01000002} int2hex last-pos/2 2 {000000} int2hex last-pos/1 2 {000000000F0A
00E8030000E80300000100000000000000010000000000000001000000D00200
00D0020000A0050000A0050000A0050000A00500000100000000000000000000
00005C0000106300001029000000650000100000000066000010000000006400
00100206010000000000000000000000005C0000106300001031000000650000
10000000006600001000000000640000100206F7000010D02F0000E03D000000
0200030200020002000200}]

footer: rejoin [
{3F8090030000000000000000FF3F0000FF000000
0801000000F00300000000000000000000000000000000000000000000000000
00000000FFFFFF020102070200000006020D0000001CEC000000220641726961
6C01020004C5000000C7000000C9000000} int2hex 204 + length1 2 {000000020200} int2hex 7 + length1 2 {010000020002C0
000000C3000000} int2hex 53 + length1 2 {010000} int2hex 60 + length1 2 {010000020000880000102653686565742E617070
0A1F0100101400000005010010390000001D010010} int2hex 62 + length1 2 {01000021010010} int2hex 79 + length1 2 {0100
0089000010} int2hex 82 + length1 2 {010000}]

next-cell:  "00A"
body: join copy header int2hex ((length? parse data "^/,") * 2) 2
line: parse data "^/"
row: 0

forall line [
    col: 0
    element: parse first line ","
    forall element [
        append body int2hex 4 * col 2
        append body int2hex 4 * row 2
        append body next-cell
        append body int2hex 2 + (4 * length? first element) 3
        foreach char first element [
            append body int2hex to-integer char 2
        ]
        col: col + 1
    ]
    row: row + 1
]
append body footer

body: replace/all form body "^/" ""

if error? try [b: read/binary %/ncp/c/documents/personal/stocks/test][
    b: read/binary to-file ask "Location of Sheet document which is what your .csv file should look like: %"
]

psion: replace/all skip form b 2 "^/" ""

diffs: func [a b /local text-out][
    text-out: copy ""
    for i 1 length? b 1 [either a/:i = b/:i [append text-out "."][append text-out b/:i] if zero? i // 64 [append text-out "^/"]]
    text-out
]

format-out: func [text /local text-out][
    text-out: copy ""
    forskip text 64 [text-out: append text-out copy/part text 64 append text-out newline]
    text-out
]

body-out: format-out body
psion-out: format-out psion
diffs-out: diffs body psion

if error? try [write/binary %/ncp/c/documents/personal/stocks/testin load rejoin ["#{" body "}"]][
    write/binary to-file ask "Location where to write .csv converted document to: %" load rejoin ["#{" body "}"]
]