Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Code Archive _ Export Paradox Memo Fields to delimited text

Posted by: JohnnyB Sep 10 2003, 04:18 PM

I got this from a database newsgroup. It works pretty well. You just have to remove a slash from the last field of each record. You get a Paradox error if you try to run it with only one slash as the record delimiter. Create a form with a button and add this code to its PushButton method.

CODE
method pushButton(var eventInfo Event)
var
   t,t1              TextStream
   counter,counter1  Number
   temp              AnyType
   m                 Memo
   lines             Array[] String
   tc                TCursor
endVar

t.open("yourfile.txt","nw")    ;Open the text file to export to.
tc.open("yourfile.DB")        ;Open the table to export from.
scan tc:                     ;Loop through all records.
   message("Exporting record " + string(tc.recno()))
   for counter from 1 to tc.nfields() ;Loop through all fields.
      if tc.fieldType(counter) <> "GRAPHIC" and ;Types to skip.
      tc.fieldType(counter) <> "OLEOBJ" and
;The next line is not required for versions 1.0 & 4.5
      tc.fieldType(counter) <> "BYTES" and
      tc.fieldType(counter) <> "BINARYBLOB" then
      tc.fieldValue(counter,temp)
      if tc.fieldType(counter) <> "MEMO" and
         tc.fieldType(counter) <> "FMTMEMO" then
         t.writeString(temp)  ;Add content to text.
      else
         m = temp
         if m.size() < 32768 then;Test memo field size.
            t.writeString(temp)  ;Add to text file.
         else
           ;Write content to temporary file.
            m.writeToFile(":priv:temp")
            t1.open(":priv:temp","r")
            t1.readLine(lines)
           ;Loop through each line of temp file.
            for counter1 from 1 to lines.size()
            t.writeString(lines[counter1])
           ;Write each line to export text file.
            if counter1 < lines.size() then
               t.writeLine("")
            endIf
            endFor
            t1.close()
         endIf
      endIf

      if counter < tc.nfields() then
         t.writeString("^")      ;Append a field separator.
      endIf
      endIf
   endFor
   t.writeLine("\\")             ;Append a record delimiter
endScan                          ;and a new line.
t.close()
tc.close()
message("Done")
endmethod

Posted by: Rogoflap Jan 22 2019, 05:58 PM

This worked as designed, but there is a problem.

When exporting memo fields that have CR and LF this causes a problem in the output file.

If you were to import this file to say MS Access or any other tool, you will then see the issue as the import is expecting one line per record and this
creates multiple lines for those memo fields that have actual CR LF in them.

It would be easy to fix had I been able to find some example of a string replace but it isn't easy to find in any documentation.

Thanks for the code as it did help get these out of my database.

Rog