Fastest way to Export data into Excel File.

[EJ-Boy Perez]

Good day to everyone!

What will be the fastest way to export data to excel file?

I have my application wherein i used this format:

arr_temp.clear();
i = 1;
repeated select  :arr_temp[i].col1=col1,:arr_temp[i].col2=col2,... from source_table
begin
   i = i + 1;
end;
commit;

i=0;
for i = 1 to arr_temp.lastrow do
   insert to excel file..
endfor;

Hope you can help me with this.

Thank you..

[Nazih Ugail]

Hi Perez,

Easiest way is to, run below to get the data directly into a CSV file and you can open via excel.

copy table1(              

  name = char(0)comma,

  address1 = char(0)comma,

  address2 = char(0)nl)

into 'table1.csv';

But the little issue in this is where you have ',' in the middle of data!!! might as well to try use a different delimiter in that case.

Kind regards,

Nasih

--
You received this message because you are subscribed to the Google Groups "OpenROAD Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openroad-users+unsubscribe@googlegroups.com.
To post to this group, send email to openroad-users@googlegroups.com.
Visit this group at https://groups.google.com/group/openroad-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/50be238b-e89a-4561-b4d7-09fff9fd49d7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[adr...@octavo2.demon.co.uk]

The later versions of Ingres have native CSV capability so you do not need to worry about embedded commas etc:

 

--

To unsubscribe from this group and stop receiving emails from it, send an email to openroad-user...@googlegroups.com.
To post to this group, send email to openroa...@googlegroups.com.

Visit this group at https://groups.google.com/group/openroad-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/50be238b-e89a-4561-b4d7-09fff9fd49d7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

 

--

You received this message because you are subscribed to the Google Groups "OpenROAD Users Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to openroad-user...@googlegroups.com.
To post to this group, send email to openroa...@googlegroups.com.

Visit this group at https://groups.google.com/group/openroad-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/CAADHqjyBj4syt3sMq20uPin38K%2Bq9dgVSevSE%2Bo40uQWEysEqg%40mail.gmail.com.

[EJ-Boy Perez]

Thank you for for quick response..

To unsubscribe from this group and stop receiving emails from it, send an email to openroad-user...@googlegroups.com.
To post to this group, send email to openroa...@googlegroups.com.

[Allan Biggs]

If you're using OpenROAD get the rows into a tablefield set HasControlButton to TRUE
when running the frame if the control button is pressed the contents can be copied to the clipboard and pasted into a table field.


If you want to do this regularly - have a look at http://xlsxwriter.readthedocs.io/index.html we are using Ingres report writer to produce sophisticated spreadsheets via this tool (via a python script)
these spreadsheets are fully formatted.

Allan

.
To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/01b201d21ae4%24a9a34180%24fce9c480%24%40demon.co.uk.
For more options, visit https://groups.google.com/d/optout.

This communication is for use by the intended recipient and contains
information that may be Privileged, confidential or copyrighted under
applicable law. If you are not the intended recipient, you are hereby
formally notified that any use, copying or distribution of this e-mail,
in whole or in part, is strictly prohibited. Please notify the sender by
return e-mail and delete this e-mail from your system. Unless explicitly
and conspicuously designated as "E-Contract Intended", this e-mail does
not constitute a contract offer, a contract amendment, or an acceptance
of a contract offer. This e-mail does not constitute a consent to the
use of sender's contact information for direct marketing purposes or for
transfers of data to third parties.

Francais Deutsch Italiano  Espanol  Portugues  Japanese  Chinese  Korean

          http://www.DuPont.com/corp/email_disclaimer.html

[Engr. Rogelio Jr. Burgos]

Good day!  As per experience, copying data to Excel cell by cell takes a lot of time to finish.  And Sir Allan is correct in saying that placing your data in a tablefield and copying it the clipboard is the way to go.  In my latest project I have created a procedure for copying any tablefield to an Excel file, and I had to rewrite the code (to make use of the clipboard) because of the time it took copying data per cell to Excel.

The code takes care of NEWLINES/TABS in the data (which you can comment out but I would not recommend it because these will ruin your Excel file if they exist).  The code also takes care of some simple formatting to make the finished product nifty. The parameters for the procedure as as follows:

xTF = TableField (tablefield where the data is)

xFilename = varchar(256) not null default '' (Optional filename) 

WithInv = i1 not null default FALSE (Include/Exclude invisible columns)

cFrame = FrameExec (frame calling the procedure)

I am assuming here that the External Class for Excel is already included. Here is the finished working code:

procedure TF2Excel(xTF = TableField, xFilename = varchar(256) not null default '', 

WithInv= i1 not null default FALSE, cFrame = FrameExec)= 

declare 

arr = array of Object;

dup_arr = array of Object;

i = i4 not null;

k = i4 not null;

v_val = varchar(1000) with null default '';

datatype= varchar(65) not null;

tString = stringobject;

tStr1 = stringobject;

rLen = integer not null;

    filex = stringobject;

targetWB        = Workbook; 

xlapp         = Application; 

wrksheet      = Worksheet; 

targetFile    = varchar(256); 

sheets_coll   = Sheets collection of Worksheet; 

enddeclare 

begin

if xTF.getFieldValue(value = byref(arr)) != ER_OK then

message 'There was an error fetching the array data!';

return -1;

endif;

if arr.lastrow = 0 then

message 'There is nothing to save!';

return -1;

endif;

        /**** Get filex.Value here via fileopenpopup, I use my own 3GL dll here ***** /

filex.Value = ....

        /**** Get filex.Value here via fileopenpopup, I use my own 3GL dll here ***** /

if trim(ifnull(filex.value,'')) = '' then

message 'The save operation was cancelled!';

resume;

endif;

/**** Check for file existence here and delete if necessary ***** /

        1. Check for existence of filex.value

        2. If it does, prompt user for overwrite

            a. If yes delete the existing file and proceed

            b. If no exit the procedure

/**** Check for file existence here and delete if necessary ***** /

        targetfile = filex.value;

:cFrame.statustext = 'Cloning array data...'; 

dup_arr = arr.duplicate(); /*** to undo changes to the original ***/

        /**** String value check start (comment out the block if no check is required) ****/

:cFrame.statustext = 'Cleaning data of newline and tab prior to copy...';

for i = 1 to arr.lastrow do

:cFrame.statustext = 'Status: Cleaning Entries of NEWLINE and TAB :: '

+ifnull(trim(char(i)),'0')+'/'+ifnull(trim(char(arr.lastrow)),'0')

+' >> '+ifnull(squeeze(char(100*i/arr.lastrow)),'0')+'%';

  :cFrame.flush();

for k = 1 to xTF.TableBody.ChildFields.LastRow DO

  :cFrame.flush();

  if  WithInv= FALSE and xTF.TableBody.ChildFields[k].curBias = FB_INVISIBLE then

continue;

      endif;

    datatype = lowercase(ScalarField(ColumnField(xTF.TableBody.ChildFields[k]).ProtoField).DataType);

      if left(datatype,7) = 'varchar' or left(datatype,4) = 'char' or 

(left(datatype,1) = 'c' and left(datatype,2) <> 'ch') then

        arr[i].getAttribute(:xTF.TableBody.ChildFields[k].Name = byref(v_val));

tString.value = ifnull(v_val,'');

while locate(tString.value,HC_NEWLINE) <= length(tString.Value) do

rLen = locate(tString.value,HC_NEWLINE) - 1;

tStr1.value = left(tString.Value,rLen);

tString.value = right(tString.value,length(tString.value)-rLen-1);

tString.value = tStr1.value + tString.value;

endwhile;

while locate(tString.value,HC_TAB) <= length(tString.Value) do

rLen = locate(tString.value,HC_TAB) - 1;

tStr1.value = left(tString.Value,rLen);

tString.value = right(tString.value,length(tString.value)-rLen-1);

tString.value = tStr1.value + tString.value;

endwhile;

v_val = squeeze(tString.value);

        arr[i].SetAttribute(:xTF.TableBody.ChildFields[k].Name = v_val);

v_val = '';

endif;

    endfor;

  endfor;

:cFrame.statustext = '';

 /**** String value check end (comment out the block if no check is required) ****/

:cFrame.statustext = 'Copying data to clipboard...';

if xTF.CopyToClipboard( headers = true) <> ER_OK then

:cFrame.statustext = 'Failed to copy to clipboard...';

message 'There was a error during the copy operation! The save operation will 

be aborted...';

                return -1;

endif;

xlapp.visible = false;

targetWB = xlapp.WorkBooks().add();

targetWB = xlApp.ActiveWorkbook;

sheets_coll = targetWB.WorkSheets;

sheets_coll[1].Name = ifnull(:xFilename,'');

wrksheet = sheets_coll[1];

wrksheet.Activate();

/**** Column formatting start (comment out the block if no formatting is required) ****/

:cFrame.statustext = 'Status: Formatting headers...';

:cFrame.flush();

for i = 1 to xTF.TableBody.ChildFields.LastRow DO

    if  WithInv= FALSE and xTF.TableBody.ChildFields[i].curBias = FB_INVISIBLE then

continue;

      endif;

      datatype = lowercase(ScalarField(ColumnField(xTF.TableBody.ChildFields[i]).ProtoField).DataType);

xlapp.Range('A1').Offset(0,:i-1).Formula = :xTF.TableBody.ChildFields[i].Name;

xlapp.Range('A1').Offset(0,:i-1).font.name = 'ArialNarrow';

xlapp.Range('A1').Offset(0,:i-1).font.size = 10;

xlapp.Range('A1').Offset(0,:i-1).font.bold = TRUE; 

      if datatype = 'i4' or datatype = 'integer' or datatype = 'smallint' then

xlapp.Range('A:A').Offset(0,:i-1).NumberFormat = '#,##0_);[Red](#,##0)';

elseif datatype = 'float' or datatype = 'f8' or datatype = 'decimal' then

xlapp.Range('A:A').Offset(0,:i-1).NumberFormat = '#,##0.000000_);[Red](#,##0.000000)';

elseif datatype = 'money' then

xlapp.Range('A:A').Offset(0,:i-1).NumberFormat = '#,##0.00_);[Red](#,##0.00)';

elseif datatype = 'date' or datatype = 'ingresdate' then

xlapp.Range('A:A').Offset(0,:i-1).NumberFormat = 'mm/dd/yyyy';

elseif left(datatype,7) = 'varchar' or left(datatype,4) = 'char' or 

(left(datatype,1) = 'c' and left(datatype,2) <> 'ch') then

xlapp.Range('A:A').Offset(0,:i-1).NumberFormat = '@';

endif;

endfor;

/**** Column formatting end (comment out the block if no formatting is required) ****/

:cFrame.statustext = 'Transferring data from clipboard to worksheet...';

xlapp.Range('A1').select();

wrksheet.Paste();

xlapp.Range('A1').select();

/**** Column Autofitting based on widest cell content in a column ****/

for i = 1 to xTF.TableBody.ChildFields.LastRow DO

xlapp.Range('A:A').Offset(0,:i-1).Columns.AutoFit();

endfor;

/**** Column Autofitting based on widest cell content in a column ****/

xlapp.DisplayAlerts = False;

xlapp.ActiveWorkbook.Close(TRUE,:targetfile); 

xlapp.quit();

xlapp = null;

sheets_coll = null;

targetwb = null;

/**** Resetting the tablefield data to the one prior to string cleanup ****/

:cFrame.statustext = 'Resetting array data back to original values...';

if xTF.setFieldValue(value = dup_arr) != ER_OK then

message 'There was an error resetting the array data!';

endif;

xTF.UpdField();

/**** Resetting the tablefield data to the one prior to string cleanup ****/

if :cFrame.confirmpopup('Finished saving TableField to "'+targetfile+'".'+HC_NEWLINE+HC_NEWLINE+

'Do you want to open the file?') = PU_OK then

/**** Opening the file here (by MS Scripting Object, etc.. I use my own 3GL here) ****/

                 wsh.run(:targetfile,,1);

endif;

:cFrame.statustext = '';

return 1;

end

This procedure works on any tablefield.  Parts of the code were lifted from an article in NAUIA way way back (for copying a tablefield to CSV/text) with the following info:

Datum Autor Beschreibung

----- ----- ------------

06.12.2000 dr neu

I don't really remember the actual name of the author but just the same thank you for sharing it as I am sharing the code above. 

Hope this helps 

Engr. Rogelio C. Burgos Jr.
SA/DBA - IT Officer II

 

Pag-IBIG Fund - Bacolod Branch Office
Gaisano Grand City Mall, Araneta St., Singcang
Bacolod City, NIR Philippines 6100

Tel. # (+63) 34 435 1154
Mobile # (+63) 925 863 3836

 

To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/OF0B19FE9C.0B14461D-ON8025803E.003CA6AE-8025803E.003DAB17%40CDCLN05.LVS.DUPONT.COM.

[Engr. Rogelio Jr. Burgos]

A minor correction:

if trim(ifnull(filex.value,'')) = '' then

message 'The save operation was cancelled!';

return -1;

endif;

Sent from Yahoo Mail on Android

[Paul White]

Hi Rogelio,

 

That is great work. Thank you for your effort.

 

You might consider reducing the frequency of status updates and curframe.flush() to help speed up the routine and reduce network traffic if user is accessing the UI from remote connection.  Also I suggest remove frame flush out of the inner loop.

 

for i = 1 to arr.lastrow do

PercentageCounter = PercentageCounter - 1;

if PercentageCounter <= 0 then

PercentageCounter=arr.lastrow / 10;

cFrame.statustext = 'Status: Cleaning Entries of NEWLINE and TAB :: ' + ifnull(trim(char(i)),'0')+'/'+ifnull(trim(char(arr.lastrow)),'0') +

' >> '+ifnull(squeeze(char(100*i/arr.lastrow)),'0')+'%';

:cFrame.flush();

                endif;

…

 

Suggest also you might have better performance if you use _StringTokenSub

 

https://groups.google.com/forum/#!topic/openroad-users/NVfgcfA5b_E

 

 

 

 

 

From: 'Engr. Rogelio Jr. Burgos' via OpenROAD Users Mailing List [mailto:openroa...@googlegroups.com]
Sent: Tuesday, 11 October 2016 2:33 PM
To: openroa...@googlegroups.com
Subject: Re: [openroad-users] Fastest way to Export data into Excel File.

 

Good day!  As per experience, copying data to Excel cell by cell takes a lot of time to finish.  And Sir Allan is correct in saying that placing your data in a tablefield and copying it the clipboard is the way to go.  In my latest project I have created a procedure for copying any tablefield to an Excel file, and I had to rewrite the code (to make use of the clipboard) because of the time it took copying data per cell to Excel.

[Engr. Rogelio Jr. Burgos]

Thanks, we have this problem where applications seem to freeze but are actually still executing. I thought maybe issuing a flush can take care of that but it doesn't really help.  5 or 10% counter increments should be good.  There was also a progressbar component of the proc but I took it out as I implemented it as a userclass so it won't make any sense in the code..  

Thanks for the _StringTokenSub suggestion. I can definitely use it in my existing codes where I do Search and Replace.

 

Engr. Rogelio C. Burgos Jr.
SA/DBA - IT Officer II

 

Pag-IBIG Fund - Bacolod Branch Office
Gaisano Grand City Mall, Araneta St., Singcang
Bacolod City, NIR Philippines 6100

Tel. # (+63) 34 435 1154
Mobile # (+63) 925 863 3836

 

--
You received this message because you are subscribed to the Google Groups "OpenROAD Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openroad-user...@googlegroups.com.
To post to this group, send email to openroa...@googlegroups.com.
Visit this group at https://groups.google.com/group/openroad-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/57fc7721.65fe420a.a53f1.8b6c%40mx.google.com.