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!

*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.

Jobs

Databases and tables

How to export DBFs with Memo or Blob fields to csv by Olaf Doschke
Posted: 2 Mar 15

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.

CODE

Close All
Clear All

lcFieldString = ''
lcMemo = ''

Use Getfile('dbf', 'Select DBF') && Prompts for table to be used.

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(Getfile('txt', 'Select Text'))

For lnCount = 1 To lnFieldCount
   lcFieldString = lcFieldString + Lower(laGetFields(lnCount, 1))
   If lnCount < lnFieldCount && Determines if the last field was
      && processed and sets the closing quote.
      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 numeric 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 and sets the closing quote.
         lcFieldString = lcFieldString + ','
      Endif
   Endfor
   Fputs(lnFH, lcFieldString) && Writes string to the text file.
   lcFieldString = ''
Endscan

Fclose(lnFH)

Close All
Clear All 

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

My Archive

Resources

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