How to convert data from one DB layout to another

Attached is a spec called ADJUST_DATA_LAYOUT.SPX This spec file reads 2 db files and a data file that matches the first DB file and converts it to the layout of the 2nd DB file. ALL the variables in the first DB file MUST exist in the 2nd DB file also. If some do not, you can still recover some of the data. See the note around the define UseExistingDCL for more info.

This can be useful if you have ADDED, MOVED, or MODIFIED questions and had not hard coded your questionnaire, so that the data layout has now changed.

If the new format data records were appended to the old format TR file, be sure to split the file up into the new and old format records first. You can use Copyfile to do this as long as there is some way to determine which records were collected in which format.

You need to change the following defines.

>Define @OrigStudy datalayout1 ”Name of Original Data and DB file

>Define @CurStudy datalayout2 ”Name of Current DB file and QFF file

>Define @OutName adjusted ”Name of the NEW output file to create

>Define @CaseIdLoc 1.4 ”Location of where Case ID is stored

The TextStart position needs to be defined for the Current Data File Layout. You can get the TextStart position from the top of the .chk file.

>Define @CurTextStart 15001

You can either use all the variables in the DBfile or use your own list. For instance, you could have the program generate all the variables, and then modify the .DCV and .DCT files created and remove some unwanted variables from it and then use this option to just read that file. The DCT file contains all the text questions (they have to be handled differently) and the DCV file contains all the other variables.

”>Define @UseExistingDCs

If your data is in Card Format you will need to set the next define two defines. The first one defines where the Card ID is supposed to be stored and the 2nd one defines the number of “real” data cards (non-text) in your new layout.

”>Define @CardIDLoc 79.2

”>Define @NumCards 2

See DATALAYOUT1.QPXas an example of an original spec file and DATALAYOUT2.QPXas an example of the changed spec file.

NOTE: You must have the original QPX, DB, or QFF file in order to use this.

  • adjust_data_layout.spx
  • datalayout2.qpx
  • datalayout1.qpx