DBF to CDX

Hello,

We have Report Writer and need to convert a .BDF file to .CDX file (index file) so we can reference the file to. Is there such a utility or anyway around it?

Best Regards,
Crest_AdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jrbbldrCommented:
Which is it?
  DBF to CDX
     or
   .BDF file to .CDX

I guess that it really does not matter since you cannot convert one of these file types to a CDX file.

A CDX file is an Index file which is created ON a DBF file.  

The various indices which the CDX file may contain are dependent on what Indexes were created ON the DBF file.  
This is generally done in FP/VFP code somewhere.  
And the Indicies can be created in ANY manner as an Expression of any of the fields within the DBF file.

If you have a backup of an existing associated CDX file, I'd recommend restoring it.

If you need to create a new CDX file, then you can create Indicies on the DBF by using FP/VFP.
If the later, and you are not already familiar with it, you might want to look at the Indexing Records video tutorial at:   http://www.garfieldhudson.com/FreeVideos.aspx

Good Luck

0
Crest_AdminAuthor Commented:
That's what I was afraid of based on the searches I've done. So do you recommend buying FoxPro? Never used it before, will it be easy to convert the dbf file to a CDX or IDX file?

Thank you.
0
jrbbldrCommented:
will it be easy to convert the dbf file to a CDX

As I said above you CANNOT CONVERT a DBF file to a CDX file.

You can only BUILD an Index on the DBF file which is saved into the CDX file

And the Index that you BUILD is dependent on what you need
The Index has an algorithmic Expression build on the desired data table Fields that you want.

So I could build many, many Indicies, each different, but not necessarily what you need.

Indicies can be built to organize into a particular manner for reporting, etc.
And/or Indicies can be built to Relate 2 or more data tables based on having common field values.

Yes, you will need Foxpro/Visual Foxpro to create the Index.
But just having the Development Language will not, by itself, get you what you need unless you know what Expression you need to build into the Index.

USE MyDBFFile  EXCLUSIVE ALIAS MyDBF
SELECT MyDBF
INDEX ON Field1 + Field2 TAG Indx1  && An example of the INDEX command
INDEX ON Field2  TAG Indx2  && Another example of the INDEX command
INDEX ON DTOS(DateField  TAG DateIndx  && Yet another example of the INDEX command
USE

Review the video tutorial I mentioned above and determine what you need in the way of an Index Expression.

Once you know what you need, come back and we will gladly help you with the appropriate INDEX command expression.

Good Luck







0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Olaf DoschkeSoftware DeveloperCommented:
A CDX always can only acompany a DBF, it's not a standalone files, as it is an index pointing to the records of a DBF.

You may have something completely different at hand, that even has nothing to do with dbase or clipper or foxpro.

So let me ask a counter question: For what application or component do you need a CDX file? And what would a CDX file be from  your knowledge or point of view?

Bye, Olaf.  
0
Crest_AdminAuthor Commented:
Thank you jrbbldr, the information you provided are very valuable - not sure if " I " can do any of that. I will let you know.

Olaf and all -

The HR dept. uses Report Writer to open three DBF files imported from various DBs. They need to link these DB to an index file that reference one of the colums for instance (common on all 3 DB such as Emloyee ID) so they can pull the data needed.

Hope this helps.

Best Regards,
0
jrbbldrCommented:
They need to link these DB to an index file

Lets correct the terminology - they would not link these DB to an index file

Instead they will link (or in our FP/VFP terminology RELATE) these data tables by using the appropriate Indicies in each data table's separate Index file.

So at some point in time Indicies would be created on the 3 data tables - something like
* --- Build An Index on EmployeeID For Each Separate Data Table ---
* --- This Will Create or Modify CDX Files For Each Separate Data Table ---

USE MyDBFile1 EXCLUSIVE
MyDBFile1
INDEX ON EmployeeID TAG EmplID  && Create Index Named 'EmplID" For Table 1
USE

USE MyDBFile2 EXCLUSIVE
MyDBFile2
INDEX ON EmployeeID TAG EmplID  && Create Index Named 'EmplID" For Table 2
USE

USE MyDBFile3 EXCLUSIVE
SELECT MyDBFile3
INDEX ON EmployeeID TAG EmplID  && Create Index Named 'EmplID" For Table 3
USE


Then somewhere else these now-existing Indicies can be used to RELATE the data tables from a "Parent" table to the multiple "Child" tables- something like:

USE MyDBFFile3 IN 0
SELECT MyDBFFile3
SET ORDER TO EmplID    && Activate the existing Index named "EmplID"

USE MyDBFFile2 IN 0
SELECT MyDBFFile2
SET ORDER TO EmplID    && Activate the existing Index named "EmplID"

USE MyDBFFile1  IN 0   && For This Example This Is The "Parent" Table
SELECT MyDBFFile1
SET ORDER TO EmplID  && Activate the existing Index named "EmplID"
* --- Establish the Relation Between Parent and Child Tables ---
SET RELATION TO EmployeeID INTO MyDBFFile2 ADDITIVE  && Establish Relation To table 2 - Child #1
SET RELATION TO EmployeeID INTO MyDBFFile3 ADDITIVE  && Establish Relation To table 3 - Child #2
<do whatever>


Now as the record pointer is moved through the Parent table, the routine can 'see' the field values in the 2 Related Child tables.

NOTE - for some reports when you want all of the various Child record displayed for a single Parent record, it is sometimes easier to set up a Reverse Relationship between the tables.

OR - often even easier, run a separate SQL Query to accumulate all of the records meeting the report criteria from all of the associated data tables into a single table/cursor (a.k.a "record set") which would then be used instead of the 'original' tables for the Reporting purposes.

Good Luck
0
Olaf DoschkeSoftware DeveloperCommented:
Which report writer do they use? Crystal Reports? You don't need a CDX to link tables, you need SQL with a JOIN. to link data. And a CDX can accelrate that. Queries on the unindexed DBFs can indeed make the query performance quite intolerable, but they are optional.

They always are an addition to the DBFs, and they don't contain all the data, they just make referencing, relating or joining data faster.

The Report Writer and it's capabilities is the interesting part. Could you be more specific? AFAIK Crystal or List&Label are capable to execute SQL, that would perhaps also include SQL to index the tables, that is to create CDX or IDX.

Bye, Olaf.
0
Crest_AdminAuthor Commented:
The program is R&R Report Writer. When we try to link a file (an index) it looks for .cdx, .mdx, .ndx, idx. The index file we're looking for should have the same tables that one of the BDF files - and that's why I was asking if we can just convert the BDF to CDX file.

Best Regards,
0
Olaf DoschkeSoftware DeveloperCommented:
Again: A CDX is not a conversion of a DBF but an add-on. A CDX or IDX is what can be added to a foxpro dbf, MDX or NDX rather point to clipper or perhaps old foxpro or dbase.

I don't know R&R and how it works or what it supports. Typically, if you can provide a SQL Select you don't need to set up links or relations between tables. You just add the tables to a report and then specify your sql select with JOINs and that can be done with indexes, but they are not necessary.

You can use the ODBC or OLEDB Provider of Foxpr to create an index, that could help of course.

All you need to execute as the main command is INDEX ON EmployeeID TAG EmplID as jrbbldr already showed.

Bye, Olaf.
0
jrbbldrCommented:
When we try to link a file

I also do not know R&R Report Writer, but when you say that you want to "Link a file", do you mean that you are attempting to associate one or more Secondary ('Child') table(s) to a Primary ("Parent") table?

 * Which table is the Report Writer 'complaining' about?   The Parent or the Child table?
* And is the Report Writer telling you what Field(s) or complex Expression it wants to use as an Index 'link' to the other table?

Good Luck




0
jrbbldrCommented:
Two other things that I should have included with my last posting....

The HR dept. uses Report Writer to open three DBF files imported from various DBs

1. Does that mean that this process (creating new CDX files) will need to be performed on a periodic basis or its a one-time need?
If it is a periodic need, then you need to create a process which can be easily repeated when needed.

2. Also - have you contacted the R&R Report Writer developers to find out how to interpret the messages you are getting from their product?

Knowing that their product is looking for a CDX file is good - but, due to the message's generality with no specifics, it does not help you much in zeroing in on WHICH table's CDX file and WHAT Index EXPRESSION is being sought out in that CDX file.

We can guess and guess and, as a result, help you to create 20+ indicies in your various data tables, but if we are not providing you with specifically what you need within the R&R Report Writer, then that doesn't get you any closer to what you need.
Perhaps the R&R Support team can give you a more detailed means of understanding their messages we will be able to help you better.

Good Luck



0
Crest_AdminAuthor Commented:
Thank you both for all your help.

This process needs to run periodically – and for reporting purposes, needs to be run in R & R –  Is it possible to create an index file in SQL? Then save that query as .CDX, IDX, MDX or NDX? I am not a DB guy and all the stuff mentioned above "Use My...." is way over my head. This is a very simple DB with 6 tables line items so I didn't think it should be this complicated...


Thank you,
0
jrbbldrCommented:
Another question - How well does your HR personnel know the R&R Report Writer?
 * Very well?
 * Or 'Sort of"?

I just went to the R&R Report Writer support documentation page and found a User Guide for the version 10 product regarding XBase file reporting.
     http://www.livewarepub.com/doc.htm

It said the following:
5. In the 4) Relate Through box,select the Relate Through option that will be
used for record matching. There are three ways to establish a relation link:
¿  Relate through an existing index

Choose Select Related Index to specify a link based on an index file. Choose
the related table index whose key expression corresponds to the initial linking
field. Report Designer will use this index to access data in the related table.
For a compound index file (such as an MDX or CDX), you must alos select the
Tags checkbox. Note that the index key box allows you to scroll horizontally if
you have a index key whose value is longer than the display box.

¿  Record number link
For an Exact-Lookup relation based on a numeric linking field, you can specify
a record number link instead of an index file to match each record in the
controlling table with a specific record in the related table. If you specify a
record number link, Report Designer uses the integer part of the linking field’s
value to locate the matching record in the related table. To use a record
number link, check the Record # Link check box.

¿  FlexLink
It is possible that the related table that you have selected may not have an
available index for the field(s) containing that are common to the relate from
file. For this situation, you can check the FlexLink box. This will tell Report
Designer to build a FlexLink index for you. A FlexLink index is an index file
with the extension .RRX or .IDX that is created in a \windows\temp
subdirectory. After checking the FlexLink box, you will then need to click the
Edit Key button. This will bring you to the Define Index Expression dialog so
you can create the index expression whose value will be used to match the
linking field from the relate from file.

The Define Index Expression dialog is similar to the Calculated Expression box
but with some differences. The field list is limited to the fields from the Relate
To table and includes the data type and field length along with the field name.
The function list is only a subset of all available R&R functions.
Insert a field name by double-clicking on an item in the Fields list; insert a
function by double-clicking on an item in the Functions list; insert an operator
by selecting the Operator buttons.

Once you have defined an index expression, select OK to return to the
Relation dialog. Note that the index will be built when the report is actually
processed. If you save a report that uses a FlexLink index, that index will be
rebuilt each time the report is opened or printed.


It would appear that if the user's knew the above, they could get around the CDX issue with the FlexLink option.

Good Luck
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
Thanks, jrbbdr, that's service!

So it turns out as I already said, you don't need a CDX, a CDX would NOT contain the data, just pointers to data, the data stays in the DBF and there is the SQL language to join that data. Indexes are just optimisiing performance.

Crest_Admin, we already understod you're not a "DB guy" or developer, but by now you should have understood that a CDX is NOT a conversion of a DBF, you've been said so by both me and jrbbldr.

jrbbldrs code would generate CDX files, you (or your IT people!) just need foxpro to execute this, and I already also mentioned you can also create CDX with the OLEDB Provider. Get develeoper staff here and let them talk to us, there is no way to put code in more human readable format, so you could understand it. The only thing we could do is provide a program for you to index data, but that's beyond what a forum is for. And we would need specifications you not seem able to give to us.

In short: You already got several solutions, and it's understandable you don't see the wood for the trees, so forward the advices to your staff or get them to us. This would speed up the communication process.

Bye, Olaf.
0
GEOFSCommented:
I've used R&R Report Writer quite a bit in the past.  As cited in the bit of Flex Link documentation above, its later versions can build an index on the fly, but you do have to have an understanding of your data structures and relationships to use it.  On the other hand, you don't have to be a programmer to build reports with R&R.

Geof
0
Olaf DoschkeSoftware DeveloperCommented:
Well,

you don't have to be a developer to create an index, but you need foxpro obviously. Once you have created a cdx it's permanent. You can also use visual designers, in this case the table designer and go into the index section and choose a field to index, it's quite simple.

Bye, Olaf.
0
Crest_AdminAuthor Commented:
Hello All,

I thank you all for all your support.

I will go ahead and purchase Foxpro to create the index file, I just hope it will be easy to do in Foxpro.

I will be off for couple weeks and will get back to you as I think I still need some help.

Again, thank you all.

0
jrbbldrCommented:
I just hope it will be easy to do in Foxpro.

Creating the Index for the various data tables in Foxpro is easy (see example code above).

The 'unknown' (to us) will be - do you know what field(s) to build the Index on?
If that is known then no problem.
If that is uncertain, then you can successfully build index after index and not get what you need.

Good Luck


0
Olaf DoschkeSoftware DeveloperCommented:
Once again: You don't need a CDX, a CDX is not obligator as jrbbldr found out reading a bit in the R&R documentation, you can use the Flexlink option without any CDX.

But if you insist on the option relating data via a CDX and choose to buy foxpro to create the CDX, well, good luck.

You could also ask the provider of the DBFs to create indexes for you, it's really mainly just one click using the table designer or one command INDEX ON field TAG tagname and not asked much.  

Bye, Olaf.
0
jrbbldrCommented:
You could also ask the provider of the DBFs to create indexes for you

Assuming that your data table provider is not just Exporting data from some other format tables into a DBF format with no additional 'control' over the resultant file(s), what Olaf suggests would be a good option.

Good Luck

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.