Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: Need to export PDOX4.5 table with memo fields to mySQL

    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

  2. #2
    Join Date
    Oct 2004
    Posts
    12
    Jonas

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

    Brenda

  3. #3
    Join Date
    Sep 2003
    Location
    Dallas
    Posts
    182

    A kludge

    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.

  4. #4
    Join Date
    Oct 2004
    Posts
    12
    But do you know how to Export it to a .xls or .csv file? I really need to figure this out.

    Brenda

  5. #5
    Join Date
    Sep 2003
    Location
    Dallas
    Posts
    182
    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.

  6. #6
    Join Date
    Oct 2004
    Posts
    12
    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

  7. #7
    Join Date
    Sep 2003
    Location
    Dallas
    Posts
    182
    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

  8. #8
    Join Date
    Oct 2004
    Posts
    12
    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
    Last edited by brendalisalowe; 11-19-04 at 19:51.

  9. #9
    Join Date
    Sep 2003
    Location
    Dallas
    Posts
    182
    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.

  10. #10
    Join Date
    Oct 2004
    Posts
    12
    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

  11. #11
    Join Date
    Sep 2003
    Location
    Dallas
    Posts
    182

    Well, when you say it like that...

    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

  12. #12
    Join Date
    Oct 2004
    Posts
    12
    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

  13. #13
    Join Date
    Sep 2003
    Location
    Dallas
    Posts
    182

    Good find!

    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

  14. #14
    Join Date
    Sep 2003
    Location
    Dallas
    Posts
    182

    With Added Annotation

    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

  15. #15
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    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!
    The only failure is not trying to do it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •