Hi Donald,
The CSV file is fine unless you have alphanumeric memo fields with embedded
carriage returns, and that will create havoc... I am not a big fan of the
standard CSV format, as embedded commas which often crop up in addresses can
be a problem, (the built in short report does not use double quotes to
enclose text fields)
Alt-F3 once will show you the field formats, the 2nd Alt-F3 will show you
the field numbers, and the 3rd Alt-F3 will show you the edit order. The Edit
Order includes calculated field (ie fields with a ::C format), so if you
have those then if you are only exporting real fields you will need to
factor that in in your field counting.
I have got to say that I would not use the Built In Short Reports item to
export data except in WordPerfect merge format, when you are reimporting
back into DP. When exporting data I always create a custom report, and
then you have maximum control over the content an format.
1. To create a simple report based on the panel press Shift-F7, highlight
Built In Short Reports and press the Ins key to create a new report based on
the panel you want.
2. On the Report screen change the 7 - Print Margins to be all zeroes and
the Text lines also zero to suppress embedded page breaks in the output file
3. Set the 2 - Disk/File to 1- Create File and five it a filename (DOS 8.3
format)
4. The 6 - Disk mode should be DOS Text
5. Select 8 - Edit Report Form
6. Move your cursor down to the REPORT BODY section
7. Press F4, to select fields for the export, (not theire position as you
export)
8. Press the Left Arrow once to make that field selected in Item 7 the
current field
9. Press F6 to change the format
10. At the end of the existing format add the following print mode indicator
;;B and press Enter (This truncates leading and trailing spaces.) NB the
field might have a print format indicated by two semicolons ;;, if it does
then you should remove the existing print mode and substiture it with the
;;B
11. Choose a field delimiter... I prefer TABs to commas, (if you want to
embed a tab holds down the Alt key and on the numeric keypad type out 009,
thish will display as a small circle., and avoice the problem of embedded
commas in fields)
12. Repeat from step 7 until all the field you want to export have been
selected,
You can export the file as is, although to make it more user friendly you
could add a Header Row which will show the fieldnames. You need to do this
manually.
1. Move (arrow up) your cursor backup to the FIRST PAGE HEADER SECTION
2. Type in the name you wish to give each field in the order you chose them
in the REPORT BODY section, and put the same field delimiter between the
names
When you export the file, open it up in a text editor, and just check for
issues like extra blanks lines, perhaps caused by an Enter being entered
after the last field in the Report Body.
An alternative to using either a TAB or Comma as a field delimiter, you
could omit the ;;B Print format, and you will get a fixed record length file
which you can parse by field widths on import to you other program. If you
really wanted to use CSV and your import program can strip double quotes
around text fields, then you might want to put " before and after text
fields. Of course if your data might include double quote that will also
bite you on the ass.
If you wanted to use XML, then it is absolutely so easy. As you can see
from what I have done the report editor is essentially a text editor where
you can embed smart things like selecting field data with F4. And since XML
is just formatted text it is a snack to do.
In the FIRST PAGE HEADER type your XML Declaration. You can make it as
simple or as comlpex as you like, you could embed a DTD or a schema.
entities, comments, whatever.
Include in the FIRST PAGE HEADER section the opening of the Root element of
the document, and so you do not forget, go to FINAL FOOTER and enter the
closing of the root Element.
Add XML tags around each of the data elements, and you can put in a CrLf
after each field and indent field as you wish to make it more readable. If
you think that any of your field will have reserved XML entities such as &
or < in them, then you can either wrap it up as CDATA or alternatively and
this is a little more complex you can add a print mode indicator ;;W onto an
alphanumeric field ie AxAx type of field, which will convert the embed
entities to & < etc. If the field is not an alphanumeric field but
just something like Ax, then the ;;W does not work. The workaround is a
little complex, so I would suggest wrapping it up in a CDATA section
<![CDATA[ xxxxx ]], but if you need to say put it as an attribute rather
than as a element, let me know and I will give you details of the work
around.
I
I usre a lot of comments in my XML files and will DP you can also embed DP
data into the comments. Some of my XML files are very complex, and having
comments in the XML makes it so much easier to debug
With XML it opens up a whole new world for you as you can export child panel
data in the same field, by including a SubReport. Again if you want to do
this, first try what you have and I can step you through the rest.
By the way, after you see the XML possibilities with DP, and then realise
you can web enable DP you might not want to move your data to another
product!!
Other than the 64k limit to a report, some interesting uses can be to
dynamically create WordXML files or XML Excel files.
Good luck
Brian
----- Original Message -----
From: "Ball, Donald A Jr (Library)" <[EMAIL PROTECTED]>
To: "Dataperfect Users Discussion Group" <[email protected]>
Sent: Wednesday, January 09, 2008 3:39 AM
Subject: RE: [Dataperf] Questions from a DataPerfect novice
There are a number of ways to dump the data. CTRL-F5 is used
to import data.
Shift F7 presents the reports menu. Te top report is always
the built in short report. By default if you use this report
then it will export data from the current panel to a file on
disk, generally defaulting to the name
scratch.rep by default it exports "real fields". Fields on
a panel can
either be real or calculated fields.
You are of course correct, I had meant to say Shift-F7.
If you are editing a panels definition you must remove all
records, the easiest way to do this is to use the "Built in
Short report" to export all the data to the scratch.rep file,
remove all the data in the panel with
Alt-F5 (be careful NOT to empty the database completely),
make the changes to the data structure, and then import back
the scratch.rep using the
Ctrl-F5 import. One thing you should be aware of, is that
when you export the data it uses the EDIT order of the panel
for the ordinal positions of each data field in the export
file. The Edit Order can be readily changed from the Panel
definition screen Alt-F5, Alt-F5, and the 5 to change the
edit order. Why is this so important? Because the import
I'm afraid that's what I'm having trouble with at the moment. I've
exported all my data into csv files and am looking to import them into a
new database for analysis. Unfortunately, it's unclear in what order the
fields have been exported, and it does not appear to match the numbered
order I see if I hit Alt-F3 twice. Hitting Alt-F5 twice doesn't bring me
anywhere except the Multiple Remove dialog. Do you have any suggestions
as to what I may be doing wrong?
Personally moving data from DP to another application I would
do it as an XML file, as the file can be self describing, and
you can tranform it readily if it is not quite right. Many
other products can import the XML directly, you need to check
out the schema. and create the DP report to accurately
reflect that schema. With DP you can always export data
according to a particular schema but you are totally
responsible for it, DP just treats it as text, it itself does
not have any built in concept of XML. But this gives you a
maximum amount of freedom. For instance if you follow the
schema defitions for say an Microsoft Access Database, you
could create an XML report that would export all data from
the DP application, and using the one file, import it into
Access 2003, or 2007 even creating the table structures,
indexes for you. Similarly for SQL Server or MSDE, and most
other modern DBMS will allow you to import data.
I'm intimately familiar with working with XML, so this option is
potentially attractive. How do you create new reports in DP? Is it
possible to iterate over subpanel data within each main panel?
- donald
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
