I LOVE pdox4.5 with PAL and I've been using its report generator to make all of my static web pages.

I now want to move to a mySQL/PHP system. I can export via comma delimited files to mySQL for regular tables, but how to do this with the memo field table has me stymied.

Any suggestions? I would like a back and forth solution.

JonasD

Jonas

Have you ever figured this out? I am having the same problem. Let me know. Thanks!

Brenda

You could try setting your printer to print to file and then creating a paradox report that prints out every record in a single line. You will have to adjust the page settings so that it allows for a very wide line of course. Then import the results as a text file.

But do you know how to Export it to a .xls or .csv file? I really need to figure this out.

Brenda

Exporting within paradox is pretty straight forward, Its just you can't export memo fields. The nature of these fields as being of infinite size makes you understand why they would not be exportable. People misuse them when they create them for other than extremely specific purposes. You have only two choices capture them via a print file and import them. (As I said it is straight forward to create a report that prints the records and inserts commas between the fields) or recreate them in the new database from scratch.

If you have odbc drivers you might be able to use scan and an array to pull them out and transfer them to another data table using. But to tell you how would involve much more intimate details about your tables and data.

I am so glad you are willing to help me. How would I capture them via a print file and import them? I think that will probably be easier than doing the array thing you were talking about. Thanks!

Brenda

You have to have access to the full paradox program so that you can create a report. In this report you set the page to be something like 11 X32 inches you know a big line printer. On the report you place two fields the records key field and the Memo field. Make sure the memo field is set to display everything and to auto extend in size. Use the text tool to insert a comma as a field separator and delimit the field by placing a quote before and after it. You can also achieve this by using a calculated field. After this is done you select a text file as printer. It will prompt you for a name when printing begins. When your report "prints" it will actually go to a text file with the name you typed gave it. Import this text file into your new data table.

Lonnie

One problem I am having is with the hard return. If there is a hard return in the memo field, it won't go to the print file. Can I replace the hard return with something that will print out? How? Any ideas?

Brenda

the hard return is read as an eof during the import.

The question becomes now how important is it to move the data to another type of database.

Actually, it is VERY important to be able to get this data into SQL Server. I have been working on a brand new application for my company and it I can't get all this data in, I'll have to do it all by hand. That would take FOREVER ya know?

So where do I go from here? I just wish you were here with me to help

Brenda

I wish I were there too. I understand your distress and I am not ignoring you I just need to think about it a solution some more. I may have some ideas using arrays. About how much text is in each of the memo record anyway? This will give me a hint about the tools I can use.

Lonnie

Hi Lonnie

It depends on the record. Some records have only 25 characters and others can have thousands and thousands. We have been using the memo field as a "Notes" field. It is where we keep all the notes about our clients. I really wish this wasn't so hard.

Look at this link:

http://www.techtricks.com/paradox/exportmemo.php

I don't know how to run script like that, or would it even work?

Thanks!

Brenda

That's a great find. It creates a tab delimited ("\"|) text file and uses an array to capture the memo field which is sort of what I was thinking. It also manually writes the file. Stupidly I hadn't thought of that even though I have written other information from paradox databases. OK here's the deal. There are really two scripts in there. One creates a procedure which is the memo field capturing procedure. The other writes the text file and calls the procedure "fixup (tc.(4)" in the process which processes the memofield on the fly.

If you send me your database structure, I can send you back an annotated modification of the scripts so you will no exactly where it is hooking into your database and how it is working. The annotation on this one is good if you are an experienced pal programmer, but hopefully I can modify it enough so that you will feel comfortable using it and modifying it and creating another like it if need be in the future. It that sounds like what you want to do let me know. Otherwise you can email me or send me your email and I will get you mine and perhpaps send you my number if you want to call with questions which can sometimes be helpful.

Lonnie

Brenda et all I took the script that you pointed me to and added what I think is sufficient annotation so that you can use it. The script is based upon a self-generated table with 4 fields the fourth is the memofield. I have put lines in brackets that you don't need. The field identifiers relative ot your datbase tc.(1), tc.(2) etc... will need to modified by by you as well as the table names. You can create a simple form if you copy and paste everything from procfixup to endmethod into a script (.ssl) and make the necessary changes to apply to your datatable you should have no problems. Brenda if you need more help, you know where you can reach me.

Lonnie

;breakApart splits a string into an array of substrings
;each substring is written to an element of an array.
;You can specify one or more delimiting characters in separators.
;If you omit separators, substrings are delimited by a space.
;Delimiting characters are not included in tokenArray.

proc fixup( strInput String ) String ;This syntax sets the procedure up to take a passed value "strinput" from another method.
; --------------------------------------------------------------
; If strInput contains CRLF's, this replaces them with "\n"
; and returns the result; otherwise, returns the original value.
; --------------------------------------------------------------
var
astrLines Array[] String
strRetval String
siCounter smallInt
endVar

strRetval = strInput
if strRetval.search( "\n" ) > 0 then ; separate CRLF's

strRetval.breakApart( astrLines, chr( 13 ) + chr( 10 ) ) ; This one step creates an array of all the "lines" in the memo field.
strRetval = "" ;This line resets the string after the array is created.

; reassemble the string using "\n" instead of CRLF's
for siCounter from 1 to astrLines.size(); This For loop puts the string back together without the CRLF's.
if ( astrLines[ siCounter ] <> "" ) then
strRetval = strRetval + astrLines[ siCounter ]
if siCounter < astrLines.size() then ; add "\n"
strRetval = strRetval + "\\n"
endIf
endIf
endFor
endIf
return strRetval
endProc

method run(var eventInfo Event)

var
tc TCursor
ts TextStream
endVar

const
DATAFILE = "riv:rtlerrors" ;Put your table and path name here
TEXTFILE = "c:\\errors.txt" ;Put the output file name you want here
STDERROR = "If [>>] is enabled, choose it for more details." ;Just a helpful error message
endConst

{enumRTLErrors( DATAFILE ) ; create the data table not needed as your table already exists}
if not tc.open( DATAFILE ) then
errorShow( "Can't Open Errors Table", STDERROR )
else

if not ts.open( TEXTFILE, "nw" ) then
errorShow( "Can't Open Output File", STDERROR )
else

scan tc :
message( "Writing ", tc.recNo(), " of ",
tc.nRecords(), "..." )
ts.writeLine( "\"", tc.(1), "\"|",;I believe the pipe translates to a tab otherwise it is the delimiter and not tab in your file.
"\"", tc.(3), "\"|",
"\"", fixup( tc.(4) ), "\"" ) ;tc.(4) is the memo field which is the fourth field in this table. You can reference it by name.
endScan

; Add additional error-checking for full sanity.
ts.commit()
ts.close()
tc.close()
beep()
message( "Done!" )
endIf
endIf

endMethod

I'd proceed in a totally different manner: i would link an alias to the mySql Server (it does indeed function well by means of MySQLOdbc) and then use a pdox script to move data to the mysql server: this way i could transform memo fields to normal strings in pdox, and then to text fields in mysql...

Bye!