Excel Import

Discussions related to Visual Prolog
Alfred
Posts: 5
Joined: 9 Oct 2013 13:11

Excel Import

Unread post by Alfred » 6 Feb 2018 15:44

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
VIP Member
Posts: 323
Joined: 5 Nov 2000 0:01

Re: Excel Import

Unread post by Harrison Pratt » 6 Feb 2018 17:25

(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
Posts: 5
Joined: 9 Oct 2013 13:11

Re: Excel Import

Unread post by Alfred » 7 Feb 2018 14:51

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

Harrison Pratt
VIP Member
Posts: 323
Joined: 5 Nov 2000 0:01

Re: Excel Import

Unread post by Harrison Pratt » 7 Feb 2018 17:37

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).

Post Reply