Friday, April 15, 2016

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!


6 comments:

  1. Hi, Really great effort. Everyone must read this article. Thanks for sharing.

    ReplyDelete
  2. You are a genius. Thank you again.

    David
    davidjmp@optusnet.com.au

    ReplyDelete
  3. Brilliant! It's really helpful. Thanks~

    ReplyDelete
  4. thanks. brilliant idea.

    ReplyDelete