Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to export DBFs with Memo or Blob fields to csv

Databases and tables

How to export DBFs with Memo or Blob fields to csv

by  Olaf Doschke  Posted    (Edited  )
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 [link http://support.microsoft.com/kb/241424]http://support.microsoft.com/kb/241424[/link].

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
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top