APPEND FROM fails on Memo (VFP)

It appears that APPEND FROM command has an undocumented behaviour which is it cannot transfer its values into a Memo Field.  So in cases where you want to append data from say a csv, text or other types and your destination table has a memo field, then forget about this command.

Inside foxite forum, this exact problem causes a member to search for a way to achieve getting the values of a column of a CSV file that has more than 256 characters (definitely a candidate for a memo field type).  And APPEND FROM cannot simply just do that.

Having said, I showed him an alternative way to transfer data from a CSV file to our table with memo fields via excel automation.  It is quite simple really to do that.  Here is a small snippet for that:

Create Cursor junk (familyid i, Name c(60), Description M)

Local lcFile, loExcel As Excel.Application, lnLastRow

lcFile = Getfile("csv")

If !Empty(m.lcFile)

      loExcel = Createobject("excel.application")

      With loExcel

            .Workbooks.Open(m.lcFile)

            For lnloop = 2 To .Cells(1, 1).CurrentRegion.Rows.Count

                  Insert Into junk (familyid, Name, Description) Values ;

                        (.Cells(m.lnloop,1).Value,.Cells(m.lnloop,2).Value,;

                        .Cells(m.lnloop,3).Value)

            Next

            .ActiveWorkBook.Close

            .Quit

      Endwith

      Go Top

      Browse Normal

Endif

Using his sample data, here is the result of said automation:

And that is it, an easy way to import data from a CSV file (or any other files that excel supports) into our tables including ones for Memo fields.  Cheers!

ExcelExport

This is the counterpart of ssToExcel2 of ssClasses.  

Unlike ExcelPivot, this one simply transfers your cursor/table into excel with added formats, titles, etc.  An example is a ledger as shown at the bottom hereof

New properties


_PaperSize (1 = letter (Default), 3 = tabloid, 4 = ledger, 5= legal, 7 = executive, 8 = A3, 9 = A4, 11 = A5, 66 = A2, 119 = 11x17 size)
_MarginLeft, _MarginRight, _MarginTop, _MarginBottom (all Defaults to 1 inch)
_WithFooter (Default is .F.)
_Orientation (1 = Portrait (Default), 2 = Landscape)
_scaling (For zooming of sheet, default is 100)
_isProper = to turn the field names into PROPER() (Default or .T.) or UPPER() (.F.)




Miscellanous:


Added Progressbar (ProgBarX class)
Replaced Default body font from Tahoma to Calibri
Cleaned codes, speed up automation a bit





Shown below is what I meant by exporting cursor result to excel using this class on a spreadsheet with added features.  Please note that with this class, it us entirely possible to do that even when you have zero automation knowledge.












Available only to ssUltimate subscribers.

ProgBarX

A simple progress bar which I created to replace the WAIT WINDOWs I put in ExcelPivot and ExcelExport classes.

Late last week, I decided to add more features onto it so its usage can go beyond those two classes such as for my other looping needs.

Progressbar on SQL SELECT

After that, I decided to see how I can incorporate this to an SQL SELECT as well, a desire which most of us share.  Here is how it looks like when incorporated on an SQL SELECT

SQL SELECT..  INTO CURSOR TEST RESULTS

* Update, April 14, 2016

Thought of another way to slash the overhead of progressbar on SQL SELECT.

Test is performed on our requested items for 9 years joining 5 tables in the process.   The SQL SELECT produces a cursor with  51,743 records:

- standard SQL SELECT (not using this class), average elapsed time on multiple runs is 1.83 seconds

- with ProgBarX showing records being processed, the bar and  percentage of completion (via SQLBar() method of class),
 average elapsed time is 4.50 seconds.

- with ProgBarX showing only the progress bar and percentage of completion via its SQLBarNoRec() method, average elapsed time of 3.72 seconds.

Again, the test is done on a 9-year dataset.  If it will be just within months or a year, the overhead will be less than a second.

The beauty of this is now we can see the number of records and its percentage of completion as those are created by an SQL SELECT.

Not only that, visual is the key.  Without a progressbar showing records being processed by an SQL SELECT with huge number of records, it appears to be slower than the one with ProgBarX.  That, of course, is an illusion as users do not like staring at the screen wondering what the hell is going on.  Users never like to unknowingly wait.

With this in place,  it provides users a real-time live feedback of what's going on behind the sql select processes as it shows the number of records being processed so far and can give them estimates on how much more to wait via the progressbar and percentage of process completion.  It also shows them that the app is currently doing something and has not frozen, as some tend to wonder or think on SQL SELECTs resulting to huge number of records.

This class though is only available for ssUltimate users.  The one added onto ssClasses does not have the latest features including that capability to show progress of an SQL SELECT.

A demo form for this is now available for my subscribers:

Interested in being a subscriber?  Email me at ss.classes.jun at gmail.com

Cheers!