We help IT Professionals succeed at work.

Update btrieve tables

cebu1014
cebu1014 asked
on
Is there a possibility to find a program that will write/update records in a btrieve table. There is an cobol application we run that is ran with a   pervasive database engine  but how to update these btrieve records from a flat file such as txt  or csv  or anyother is all new to me. Sure need some help on this.
Comment
Watch Question

MirtheilSoftware Developer

Commented:
There is not a generic program that will do what you want.  
Depending on several factors including version of Btrieve/Pervasive and  whether you've got DDFs or not, it may be easier to build a program.  
If you have DDFs and have a recent Pervasive engine (PSQL 2000 or later) with an ODBC driver, you might be able to create a program that reads the CSV/TXT file and writes or updates the records.  You might even be able to use the Pervasive Data Integration tools.  
If you don't have DDFs and can't build them, you would be limited to the Btrieve API and would need to read the records from the TXT/CSV file and insert/update the records using the Btrieve API calls.  You'd still need to know the exact record structure so the Btrieve API calls don't corrupt the data.

Author

Commented:
I have the ddf files there is a english and complete(programmer) set. I have Pervasive 8 which I belive is later than 2000. I am not sure since I dont have access to server now.  Would there be tools in this version or later version of Pervasive that allow for me to accomplish this? I have only thus far used ODBC drivers for pervasive to query into another program such as crystal

Thanks.
MirtheilSoftware Developer

Commented:
PSQL v8 is a later version than PSQL 2000.  It should have an ODBC driver.  You'd want an engine DSN on the server pointing to the data and DDFs.  On the client, you'd want a Client DSN pointing to the Engine DSN.  

As far as tools, I think PSQL v8 had an Import Wizard.  It might do what you need.  
Again, you might look at the Pervasive Data Integration tools.  It'll help, especially if this is an ongoing project.  If it's a one time thing, the import wizard might work best.

Author

Commented:
I have imported a table from a file generated from the export wizard. It exports as a sdf file are there any other file formats you can use to import into btrieve besides the sdf extension? Can you do updates  to existing records as well as import new ones? I have tried but I get errors. I have been sucessful only importing new records into a empty table.

Thanks
President and Btrieve Guru
Commented:
Pervasive's standard tools work with SDF files, which are essentially comma-delimited files.  You can import data with any filename extension, but the tools will look for SDF by default.  Just override that by entering the complete file name yourself.

Note that the standard, included, import tool is INSERT ONLY.  The tool assumes that you want to import the data, and it does SQL INSERT calls to do it.  If the record already exists (as defined by a unique key), then you would need to do an UPDATE statement, and decide how much data is important, and which data should be the key field.  This is NOT handled by the native tools because of the ambiguity/complexity of such a task.

What you REALLY want is an ETL tool, such as Pervasive Data Integrator, or the open source Jaspersoft or Pentaho solutions.  These tools Extract data from a data set (like your CSV file), Transform and normalize the data to match the target data set, and the Load the data into the target data set.  Many handle an "UPSERT" command, which does the correct update/insert operation for you.  These tools are also designed to be extremely flexible and can probably take care of a large portion of your data integration needs.  Get a trial of Pervasive's Data Integrator to try it out from http://www.pervasiveintegration.com/products/Pages/data_integration_tools.aspx.  You can also Google the term "ETL" to find some more ideas.