[Alfred]

I am using CE 7.4. In order to import content of excel spreadsheet cells into client application I am looking for simple routine that works.

[Harrison Pratt]

(1) A simple and reliable way is to save the Excel data as comma- or tab-separated file and parse that file.

(2) Alternatively, you can highlight an area of the spreadsheet and copy it to the clipboard. It will have the columns separated by tabs ("\t") with a new line ("\n") separating each row.

Less simple approaches are to use ODBC to read the file or save the Excel data in XML or other formats. If you cannot have the Excel user do (1) or (2) then you may need a more complex approach.

The code below will read a tab-separated file into a string_list_list variable. Quoted strings in the Excel export file are returned as quoted strings. Numeric values are also returned as strings, so you will have a little more data manipulation to do after reading the data strings.

Code: Select all

class predicates
    readFileToSSS : ( string QFN ) -> string_list_list determ.  /* QFN = fully Qualified File Name */
clauses
    readFileToSSS(QFN) = _ :-
        not( file::existExactFile(QFN) ),
        !,
        stdio::write("\nNo such file: ", QFN ),
        fail.
    readFileToSSS( QFN ) = DataSSS :-
        FileString = file::readString(QFN),
        DataRows = string::split( FileString, "\n" ),
        DataSSS = [ SS || S in DataRows,
                            SS = string::split(S,"\t"),
                            SS <> [""] /* skip empty rows */ ].

[Alfred]

Thanks for your reply. Up to now I have used dde way. Now I will try your suggested idea.

[Harrison Pratt]

Alfred,

NOTE: Edited from the original post 2018-02-07 06:18 PM CST.

Here's an approach I use for parsing some character-separated (CSV) text files with some (but not all) delimited fields. Excel will enquote string fields but not numeric fields when you export the worksheet.

Open a re-usable string buffer and write to it as you scan through the lines of the file, cleaning the text and inserting my own splitChar to use to convert the buffer string to a string list:

Code: Select all

%  Create some custom predicate to access the file strings; 
%  you could also read the file a line at a time and process it.
class predicates
    readSepDelimFileToSSS : ( string QFN ) -> string_list_list determ.
clauses
    readSepDelimFileToSSS(QFN) = _ :-
        not( file::existExactFile(QFN) ),
        !,
        stdio::write("\nNo such file: ", QFN ),
        fail.
    readSepDelimFileToSSS(QFN) = DataSSS :-
        FileString = file::readString(QFN),
        DataRows = string::split( FileString, "\n" ),
        Buff = outputStream_string::new(),
        DataSSS = [ DataSS || RowStr in DataRows, DataSS = splitStringSepDelim( Buff, RowStr, '\t', '"' ) ],
        Buff:close().
 
 
% This predicate does the parsing using specified separator and delimiter characters.
% Note that it uses a string buffer created and closed by the calling predicate.
constants
    splitChar : char = '|'.  % This may be any character that is NOT in the string to be parsed
class predicates
    splitStringSepDelim : ( outputStream_string Buffer, string InStr, char Separator, char Delimiter ) -> string_list.
        % NOTE: Does NOT handle embedded escaped characters such as '/t'
clauses
    splitStringSepDelim( Buff, S, Separ, Delim ) = SS :-
        Buff:resetString(),
        InDelimitedSubstr = varM::new(false),
        foreach X = std::fromTo(0,string::length(S)-1) do
            C = string::subChar(S,X),
            CurrState = InDelimitedSubstr:value,
            NowState = writeToBuffer( Buff, CurrState, C, Separ, Delim ),
            if NowState <> CurrState then InDelimitedSubstr:value := NowState end if
        end foreach,
        SS = string::split( Buff:getString(), toString(splitChar) ).
 
        class predicates
            writeToBuffer : ( outputStream_string Buffer, boolean InAQuoteTF, char CurrChar, char Separ, char Delim ) -> boolean InQuoteNow.
        clauses
            %-- NOT IN a delimited part of the string
            writeToBuffer( Buff,        false, SEPAR,  SEPAR, _Delim ) = false :- Buff:write(splitChar), !.
            writeToBuffer( _Buff,       false, DELIM, _Separ, DELIM ) = true :- !.
            writeToBuffer( Buff,        false, Char, _Separ, _Delim ) = false :- Buff:write(Char), !.
            %-- IN a delimited part of the string
            writeToBuffer( Buff,        true, SEPAR, SEPAR, _Delim ) = true :- Buff:write(SEPAR), !.
            writeToBuffer( _Buff,       true, DELIM, _Separ, DELIM ) = false :- !.
            writeToBuffer( Buff,        true, Char, _Separ, _Delim ) = true :- Buff:write(Char).