×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Microsoft: FoxPro FAQ

Databases and tables

How to export DBFs with Memo or Blob fields to csv by Olaf Doschke
Posted: 2 Mar 15 (Edited 2 Feb 19)

Creating CSV or tab delimited files is easy enough with COPY TO or EXPORT, but there is one limitation: Memo fields (and Blob and all fpt file related fields) are not exported to file types other than DBF, also towards Excel.

Microsofts KB article suggests using low level file operations (FPUTS) to write out Memos in http://support.microsoft.com/kb/241424.

I modernized this routine slightly, so it also processes Blobs and Varbinary and exports them to 0h... (like CreateBinary) format. It also processes NULLs correctly and I mostly only use Transform() to create the text output of values. I also write out a header line with field names.

Some true CSV standards are: Memos with line breaks stay as they are, one record then does not end at CRLF. the double quote delimiters are only put around char, varchar and memo values and a double quote inside them is converted to a double double quote.

Besides I also set some date settings, assuming american should be standard in a CSV file. But that and other things are easy to adapt to your situation.

CODE

Close All
Clear All

lcFieldString = ''
lcMemo = ''

Use Getfile('dbf', 'Select DBF') && Prompts for table to be used.
lcTargetFile = Putfile('txt', 'Specify Target txt File') && Prompts for target file name.

CSVExport(lcTargetFile)

Function CSVExport(tcFilename as String, tcAlias As String)
   tcAlias = Evl(tcAlias,Alias())
   tcFilename = Evl(tcFilename, ForceExt(Dbf(tcAlias),"txt"))
   
   If ADir(laDummy,tcFilename)>0 
      Error 'File already exists'
   EndIf
   
   If !Used(tcAlias)
      Error 'No workarea selected or specified'
   EndIf
   
   Select (tcAlias)
   lnFieldCount = Afields(laGetFields) && Builds array of fields from the
   && selected table.

   *!* Prompt for Output file and use Low-Level functions
   *!* to create it.
   lnFH = Fcreate(tcFilename)

   For lnCount = 1 To lnFieldCount
      lcFieldString = lcFieldString + Lower(laGetFields(lnCount, 1))
      If lnCount < lnFieldCount && Determines if the last field wasprocessed.
         lcFieldString = lcFieldString + ','
      Endif
   Endfor
   Fputs(lnFH, lcFieldString) && Writes string to the text file.
   lcFieldString = ''

   *!* Starts scanning the table and converts the fields
   *!* values according to their types
   Scan
      * Whatever suits you here, standard for CSV should be american formats.
      Set Century On
      Set Mark To "/"
      Set Date AMERICAN
      Set Hours To 24
      Set Point To "."
      Set Separator To ""


      For lnCount = 1 To lnFieldCount
         lcType = laGetFields(lnCount, 2)

         If Not lcType $ 'G' && Don't try to turn a general field into a string
            lcString = Evaluate(laGetFields(lnCount, 1))
         Endif

         Do Case
            Case Isnull(lcString)
               lcString = '.null.'
               lcType = 'U' && no delimiter for char,varchar or memo field, .NULL. is null without quotes.
            Case lcType $ 'CMV'
               * nothing to do
            Case lcType $ 'IDTFBY' && process most field types via TRANSFORM
               lcString = Transform(lcString)
            Case lcType = 'L'   && process logic fields
               lcString = Iif(lcString,'.t.','.f.')
            Case lcType = 'N'   && process numeric fields
               lcString = Str(lcString, laGetFields(lnCount, 3), laGetFields(lnCount, 4))
            Case lcType = 'G'   && process general fields
               lcString = 'Gen' && that means don't process them, export the word 'Gen' instead
            Case lcType $ 'QW'  && process blob and varbinary fields
               lcString = '0h'+Transform(lcString) && empty fields have 0h prefix only
            Otherwise
               lcString = ["UNKNOWN FIELD TYPE!"] && should never happen
         Endcase

         If lcType $ "MCV"
            * csv convention: double double quotes
            lcFieldString = lcFieldString + ["] + Strtran(lcString,["],[""]) + ["]
         Else
            lcFieldString = lcFieldString + lcString
         Endif

         If lnCount < lnFieldCount && Determines if the last field was processed.
            lcFieldString = lcFieldString + ','
         Endif
      Endfor
      Fputs(lnFH, lcFieldString) && Writes string to the text file.
      lcFieldString = ''
   Endscan

   Fclose(lnFH)
EndFunc 

Back to Microsoft: FoxPro FAQ Index
Back to Microsoft: FoxPro Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close