CODEWITHSUNDEEP
visual-foxpro
ChrisCode
ChrisCode

Reputation: 29

Unprintable Characters in Visual FoxPro Memo Fields

I am trying to export a Visual FoxPro table with a memo field to a .CSV file. There appear to be unprintable characters embedded in the memo field which cause the exported value to be truncated (i.e., only the portion of the memo field BEFORE the unprintable character exports).

Has anyone seen something similar or know how/why/what the unprintable characters are which are embedded in Visual FoxPro memo fields?

Upvotes: 0

Views: 874

Answers (2)

Dhugalmac
Dhugalmac

Reputation: 574

Since I haven't done it before for Testing purposes I just now created a simple VFP table. 

CREATE TABLE C:\Temp\temp (Fld1 C(1), Note M)
APPEND BLANK
REPLACE Fld1 WITH '!',;
   Note WITH "Now is the time to come to the aid of your country"
COPY TO C:\Temp\Temp.csv CSV

That command did indeed create the CSV file, but the NOTE field (Memo field) and its contents were not included.

You might try to do a SQL Query of your desired table first and 'convert' the Memo field contents into a Character string and then removing any unwanted characters BEFORE attempting your Export to the CSV file.

For example, something similar to: 

CREATE TABLE C:\Temp\temp (Fld1 C(1), Note M)
APPEND BLANK
REPLACE Fld1 WITH '!', Note WITH "Now is the time to come to the aid of your country"
* --- Use SQL Query to Gather Table Data & 'Convert' Memo Data To String ---
SELECT Fld1,;
   PADR(ALLTRIM(LEFT(Note,120)),120) AS NoteStr;
   FROM Temp;
   INTO CURSOR ForExport READWRITE

* --- Remove Unwanted Characters From NoteStr ---
* --- Start By Making List of Everything To KEEP ---
cAlphaNumeric = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ,-'"  && and whatever else you want to KEEP

SELECT ForExport
SCAN
   cNoteStr = ForExport.NoteStr
   * --- Get List of Un-Wanted Characters ---
   cNotWanted = CHRTRAN(cNoteStr, cAlphaNumeric, "")  && Get Everything NOT In cAlphNumeric List
   * --- Remove Those Un-Wanted Characters ---
   cToKeep = CHRTRAN(cNoteStr, cNotWanted, "")  
   * --- 'Write' Back Resultant String Into NoteStr Field ---
   REPLACE NoteStr WITH cToKeep
ENDSCAN
* --- Now Export To CSV File ---
COPY TO C:\Temp\Temp.csv CSV

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

Memo and text fields in VFP can store any character, including character 0. IOW a user might have stored binary information in a memo field.

If you use, VFP's copy to command, then it creates a CSV, delimited and a few other types for you. However, copy to doesn't export fields like memo, general or blob. A text file, is not the best medium to export data, and a CSV file, when used including memo fields, may not be imported by all databases.

If you tell us more details, how you do it and the purpose, we might have better suggestions. ie: an XML might be a much better text based export. Or it might be much better to do a connection to or from the target database and do the transfer without any text based file in between.

Upvotes: 2

Related Questions