PDA

View Full Version : Generic file export utility



Bill Clifford
16-Feb-2006, 07:55 PM
Hello,
I would like to write a utility to export any Dataflex file to a text
file. Since many of my Dataflex files have large text fields, I don't
think exporting to CSV format will work since the text may contain
quoted text. I'm wondering how others have dealt with this. Exporting
to an XML file would be nice (perhaps with text fields as CDATA)...
Bill

Paul Cooling
16-Feb-2006, 10:21 PM
You could do the line delimited method DataFlex uses for dumping data
and then you don't have to worry about quotes, apostrophes etc.

Bill Clifford wrote:
> Hello,
> I would like to write a utility to export any Dataflex file to a
> text file. Since many of my Dataflex files have large text fields, I
> don't think exporting to CSV format will work since the text may contain
> quoted text. I'm wondering how others have dealt with this. Exporting
> to an XML file would be nice (perhaps with text fields as CDATA)...
> Bill

Bill Clifford
16-Feb-2006, 11:40 PM
What about carriage return/line feeds in the text data?
Bill

Paul Cooling wrote:
> You could do the line delimited method DataFlex uses for dumping data
> and then you don't have to worry about quotes, apostrophes etc.
>
> Bill Clifford wrote:
>
>> Hello,
>> I would like to write a utility to export any Dataflex file to a
>> text file. Since many of my Dataflex files have large text fields, I
>> don't think exporting to CSV format will work since the text may
>> contain quoted text. I'm wondering how others have dealt with this.
>> Exporting to an XML file would be nice (perhaps with text fields as
>> CDATA)...
>> Bill

Paul Cooling
17-Feb-2006, 08:02 AM
You're right. I was thinking of just ascii fields.
Somebody else will be able to help, I'm sure.

Bill Clifford wrote:
> What about carriage return/line feeds in the text data?
> Bill
>
> Paul Cooling wrote:
>> You could do the line delimited method DataFlex uses for dumping data
>> and then you don't have to worry about quotes, apostrophes etc.
>>
>> Bill Clifford wrote:
>>
>>> Hello,
>>> I would like to write a utility to export any Dataflex file to a
>>> text file. Since many of my Dataflex files have large text fields, I
>>> don't think exporting to CSV format will work since the text may
>>> contain quoted text. I'm wondering how others have dealt with this.
>>> Exporting to an XML file would be nice (perhaps with text fields as
>>> CDATA)...
>>> Bill

Bob Worsley
17-Feb-2006, 09:25 AM
You could be reinventing the wheel... if you have VDF, there's already
dbExport built into the product. It's had it's problems over time, but does
output to text, csv & xml. If you're doing this specifically for CM
DataFlex, knock yourself out! <g>
Bob

"Bill Clifford" <bill.clifford@northernhealth.ca> wrote in message
news:CUJjKz1MGHA.2932@dacmail.dataaccess.com...
> Hello,
> I would like to write a utility to export any Dataflex file to a text
> file. Since many of my Dataflex files have large text fields, I don't
> think exporting to CSV format will work since the text may contain
> quoted text. I'm wondering how others have dealt with this. Exporting
> to an XML file would be nice (perhaps with text fields as CDATA)...
> Bill

Larry Heiges
17-Feb-2006, 01:02 PM
I did something like this a few years ago. Converted the text to
"string hex" and wrote that to the text file. Downside is the 2 bytes
per character of unreadable, but it worked.

Larry Heiges
App-2-Win Systems, Inc.

Bill Clifford
18-Feb-2006, 12:59 AM
Bob, Larry, Paul,
Just regaining conciousness after reinventing wheel specifically for
CM! Being able to extract complete records from a file and import them
into a file is very useful in repairing "seek to unwritten extent"
errors which I recently encountered after an old peer to peer Win98
"server" crashed. I was able to export all the records up to the bad
record and afterwards by specifying recnum range to export. Dfmaint to
erase the file then read the data back in. I can now also export all
records for a particular patient including those with text fields and
take them to another practice for import. I've tested the 2 programs
below and they seem to work although haven't tested whether or not the
string variable declared in the procedure can hold up to 16K of data.
It should, shouldn't it?
Now I'll have to dust off the SAX like Dataflex XMLparser I posted some
time ago and see if I can do the same entirely in XML format.
Bill

//***** Program to export all fields in CSV format for records
//***** in a specified recnum range.
//***** Can replace specification of file number and recnum range with a
//***** a UI

Use UI

integer number_fields begin_recnum end_recnum field_type field_number
string tempstr 255
move 1 to begin_recnum
move 10 to end_recnum
direct_output "expfile.csv"
move 51 to filenumber
open filenumber mode DF_exclusive
get_attribute DF_FILE_NUMBER_FIELDS of filenumber to number_fields

Procedure writeTextField
local string textString
move indirect_file.recnum to textString
write "<![CDATA[" textString "]]>"
End_Procedure

clear indirect_file
move 0 to fieldindex
move begin_recnum to indirect_file.recnum
find ge indirect_file.recnum
[not found] abort
if (indirect_file.recnum >= begin_recnum) begin
while (indirect_file.recnum <= end_recnum)
move 1 to field_number
repeat
if (field_number > 1) write "," //don't put a comma in first pos
get_attribute DF_FIELD_TYPE of filenumber field_number to field_type
move field_number to fieldindex
if ((field_type = DF_BCD) or (field_type = DF_BINARY) or
(field_type = DF_DATE)) write indirect_file.recnum
else if (field_type = DF_ASCII) begin
trim indirect_file.recnum to tempstr
write '"' tempstr '"'
end
else if (field_type = DF_TEXT) send writeTextField //use
procedure to increase size of string variable
increment field_number
until (field_number > number_fields) //through the field numbers
writeln
move 0 to fieldindex
find gt indirect_file.recnum
loop
end //if first recnum greater than or equal lower limit
abort
//************************************************** ****************************************
//***Program to import records exported with program above
// Uses CDATA tags for text fields to invoke different method of
// reading from the csv file
Use UI

integer number_fields begin_recnum end_recnum field_type field_number
string tmpstr
direct_input "expfile.csv"
move 51 to filenumber
open filenumber mode DF_exclusive
get_attribute DF_FILE_NUMBER_FIELDS of filenumber to number_fields

Procedure readTextField
local string textField c sb
read_block tmpstr 9 //discard the CDATA start tag
move "" to sb
move "" to textField
repeat
read_block c 1
if ((c="]")or(c=">")) append sb c //temporarily save to later

// determine if part of ]]> closing tag
//don't write anything yet in case is end tag
else begin
if (sb > " ") begin
append sb c
if not sb in "]]>" begin //write and clear sb if breaks from
//end tag pattern
append textField sb //add held characters plus recent one
move "" to sb
end
end
else append textField c
end //if character <> ] or >
until (sb="]]>")
read_block c //to move past the comma
move textField to Indirect_file.recnum
End_Procedure

repeat
clear indirect_file
move 1 to fieldindex
read indirect_file.recnum
[not seqeof] begin
move 2 to fieldindex
repeat
get_attribute DF_FIELD_TYPE of filenumber fieldindex to field_type
if (field_type = DF_TEXT) send readTextField
else read indirect_file.recnum
increment fieldindex
until (fieldindex > number_fields) //through the field numbers
readln tmpstr //to move to next line
saverecord Indirect_file
end
until [seqeof]
abort


Bob Worsley wrote:
> You could be reinventing the wheel... if you have VDF, there's already
> dbExport built into the product. It's had it's problems over time, but does
> output to text, csv & xml. If you're doing this specifically for CM
> DataFlex, knock yourself out! <g>
> Bob
>
> "Bill Clifford" <bill.clifford@northernhealth.ca> wrote in message
> news:CUJjKz1MGHA.2932@dacmail.dataaccess.com...
>
>>Hello,
>>I would like to write a utility to export any Dataflex file to a text
>>file. Since many of my Dataflex files have large text fields, I don't
>>think exporting to CSV format will work since the text may contain
>>quoted text. I'm wondering how others have dealt with this. Exporting
>>to an XML file would be nice (perhaps with text fields as CDATA)...
>>Bill
>
>
>

Sture Andersen
19-Feb-2006, 05:36 AM
Hello Bill,

I have have always done it like this: Basically the output format is
line-delimited. However, for text and binary fields, instead of writing the
actual value, I writeln the length of the value (in bytes). Following the
length value I write (not writeln) the actual field value.

When reading such files back in, for text and binary fields I use the
read_block command passing it the number of bytes that I am conveniently
able to readln because of the strategy mentioned above.

-Sture