[Solved] DBase Memo Fields

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

[Solved] DBase Memo Fields

Postby BarefootTom » Wed Jan 21, 2009 11:54 pm

I am successfully using Base to handle dBase databases, but am stumped in doing anything with the so-called "Memo Fields" some of the databases have.
How can I import that data, or how can I access it from a database I have already imported?
Tom
Last edited by BarefootTom on Tue Jun 09, 2009 7:57 pm, edited 1 time in total.
OOo 3.0.X on Ms Windows Other
BarefootTom
 
Posts: 3
Joined: Wed Jan 21, 2009 11:31 pm

Re: DBase Memo Fields

Postby whonea » Tue Jun 09, 2009 6:50 am

I see no responses to date but I'm beating my head on several issues with Visual FoxPro files that use the same format and this is one of them. The answer is really quite simple but the implementation gets complicated. The text from memo files is held in file by the same name as the .dbf with a .fpt extension in the same directory as the .dbf file. The file format is fairly straight forward - entries begin at a fixed offset into the file and have header bytes with a serial id number followed by the length of the entry text so parsing the file is pretty simple - if tedious. A macro will do the job but I haven't taken the time to write it. My brute force solution (for FoxPro managed tables) has been to use Excel to import the data with a FoxPro link, save it and load it into Calc. Not elegant but it gets the job done and solves a couple of the other issues with dBase files at the same time.

I realize that this is a couple of extra steps outside Base but it gets you the macro functions to do the job. Editing is another problem and will likely require a fair amount of coding (and testing). I've been looking for a FoxPro ODBC driver since that would eliminate the middleman but no luck so far.
OOo 3.1.X on openSuse 11 + XP
whonea
 
Posts: 2
Joined: Mon Jun 08, 2009 7:54 am

Re: DBase Memo Fields

Postby Villeroy » Tue Jun 09, 2009 8:33 am

whonea wrote:I see no responses to date ...

Resonse on which question? BarefootTom does not tell us anything about his problem and your problem is off topic since you obviously talk about Calc, which is a spreadsheet program and does not know any data types other than string and double, the latter formatted in various ways to represent subtypes date-time and boolean.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DBase Memo Fields

Postby BarefootTom » Tue Jun 09, 2009 4:54 pm

whonea wrote:I see no responses to date but I'm beating my head on several issues with Visual FoxPro files that use the same format and this is one of them.


Thanks for taking the time to reply. It's been long enough that I had forgotten I asked the question. I find I am able, even if a little awkwardly, to view the memo fields directly when the tble is displayed on the screen.
Thanks for the suggestions, though
OOo 3.0.X on Ms Windows Other
BarefootTom
 
Posts: 3
Joined: Wed Jan 21, 2009 11:31 pm

Re: DBase Memo Fields

Postby Villeroy » Tue Jun 09, 2009 5:46 pm

You get a better view on your data when using forms, reports or any other office document. Drag a table-object into Calc or Writer or grab the empty grey record-selector (left of the first column-header) to drag a copy out of a loaded grid view. Multiple row-selections work as well.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DBase Memo Fields

Postby BarefootTom » Tue Jun 09, 2009 7:56 pm

Thanks for your help. I'll give that a try.
OOo 3.0.X on Ms Windows Other
BarefootTom
 
Posts: 3
Joined: Wed Jan 21, 2009 11:31 pm

Re: [Solved] DBase Memo Fields

Postby melanie22 » Mon Aug 08, 2011 9:37 am

Memo fields are tough to untangle. The DBF table has 10 characters reserved for each memo field in each record with a unique identifier that dBase then uses internally to find your "memo" in the DBT. I sure wouldn't try to do that myself, as there is not much documentation for that.

Depending on where you're trying to export to, you can levitra always do it the long way. Since I have not worked with the program you're using to export nor the destination, my only suggestion is to try this alternative, though clunky it works fine with text. We will move the memo's text into character fields, but remember, dBase allows only up to 254 characters per character field. Here's a neat way to get around it.

Make sure there is a unique field for each record in the source file.

Then create a second "data" table to hold just the memo field contents:
C_TABLE && name of the source table (optional if just one table)
C_MFIELD && name of the memo field
C_RECID && record ID or whatever uniquely identifies the record
N_CNT && numeric field to hold the counter
C_DATA && data from memo field (size is 50, 100 or up to 250)

PROCEDURE memo2chr
SELECT 1
USE myfile && ORDER whatever
SELECT 2
USE memodata && this is work table described above
SELECT 1
SCAN
SELECT 2
DO copydata WITH "memofield1" && note: send memo field name
DO copydata WITH "memofield2" && since data may be huge
ENDSCAN
USE IN 1
USE IN 2
RETURN

PROCEDURE copydata
PARAMETER memoname
PRIVATE xlen, x, loopcnt, xdata
xlen=LEN(RTRIM(myfile->&memoname))
x=1
loopcnt=1
DO WHILE x<=xlen
xdata=SUBSTR(myfile->&memoname,x,50) && set chunk size here
* IF LEN(RTRIM(x_data))=0 && use the IF only to get rid of spaces?
APPEND BLANK
REPLACE c_table WITH ALIAS(1)
REPLACE c_mfield WITH memoname
REPLACE c_recid WITH myfile->uniquefld
REPLACE n_cnt WITH loopcnt
REPLACE c_data WITH x_data
* ENDIF
x=x+50
loopcnt=loopcnt+1
ENDDO
* now entire memo field has been copied in sections to char field
RETURN

Then do the reverse at the destination to put the data wherever you need it... if you have the know-how of course.

(If you knew your memo fields never held long strings of text/data, you could also do this idea within the same table by creating however many character fields were necessary to do this.)

This was the way one place I worked kept all their memo field data after their networks (MS Windows Servers) started having repeated problems with memo fields getting corrupted. Clunky, but you do what you have to do...
OpenOffice 3.1 on Windows Vista
melanie22
 
Posts: 1
Joined: Mon Aug 08, 2011 9:33 am


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 1 guest