The Import Data command allows you to bring data records from an Excel file, delimited text file (*.csv), or fixed width text file into a new or existing ProVal database.

If data is imported via the database menu, the data will be imported into the currently open ProVal database. If this is not the database that you want to use, then open database or create a new database before importing the data. Alternatively, select the database you wish to import into and import from the ribbon within the database library.

When you select the external data file to import, the data import wizard will show you a preview of the records in the external file and walk you through the import process.

Data File

The wizard will allow you to create Data Dictionary entries for new fields as well as modify existing entries. ProVal will offer educated guesses about the data and you can choose to either accept or modify them. 

When coded fields exist in both the external data file and the Data Dictionary, ProVal will ask you to select how to handle ​Matches between File Values and existing Labels. You may choose whether to Require exact matches or to Allow similar matches. Based on your selection, ProVal will guess whether codes in the external data file are mapped to an Existing Label or a New Label. You may accept or modify ProVal's guess.

If your external file is an excel file, date fields will default to the <Excel> format type, which means the data is formatted as a date in excel. If your date fields are formatted as a number in excel (for example, YYYYMMDD), you can modify the format by double clicking on the field and changing the drop-down option.

If there are any new fields being created or codes being added, Alerts will display on the top of the screen. The Alerts column will indicate which new fields or codes have been introduced.

Double click on a field, or click the Edit button to modify Field Attributes before importing the data. If a field should not be imported, choose to Skip this field. Otherwise choose whether it is an Existing field in the Data Dictionary or a New field. For coded fields, you may edit the Mapping of File Values and Labels. For more information about the parameters for defining a new field, see Data Dictionary.

Import Into an Empty Database 

Apply a Selection Expression if you wish to restrict the import to records that meet particular criteria. If you leave the Selection expression box blank, then all records will be imported. Press the F1 key when your cursor is in this box to get a list of the operators you can use to create the expression.

Import Into a Database with Existing Records 

If you choose to merge records, you must also specify the parameters to use when merging. Select one or more Key fields. If you choose a character field, check Match case in keys if you wish to treat case differences as significant (so “Joe Smith” and “JOE SMITH” will be treated as different key values).

Alt Key is an optional parameter that can be used to specify a secondary key field (or fields). When a matching record cannot be found based on the primary key field(s), a second check is performed using the alternate key. This can be helpful in situations where employee ID numbers are corrected from one year to the next. As an example, consider a current year database with primary key field EEID and alternate key field OLDEEID. When matching EEID in the current year database against the imported data, a second step will be taken for any for any unmatched records. Those unmatched records in the current year will also have the alternate key field OLDEEID checked for possible matches against EEID in the imported data.

There are two options that dictate how the data update should be performed. The first option specifies what should be done for records that were found in both files. For records found in both files, choose to overwrite:

• All values. Data from the import file will REPLACE existing data in the current database. Check Allow values to be overwritten with missing values in order to update the target database with missing values from the source database. If unchecked, missing values in the source database will not replace non-missing data in the database. That is, existing (i.e., non-missing) source data will always overwrite target database data. 

• Only missing values. Existing non-missing values in the database will not be altered, but any "holes" in the database (i.e., missing values) will be filled in with data from the import file.

The second option specifies what should be done with records from the import file that are not matched with a record in the current database. Select, For records only found in the import data, whether you want to:

• Append to database. Records found ONLY in the import file with be appended to the current database.

• Ignore. Records found ONLY in the source database will not be added to the current database.

Check Create MergeStatus field if you want a "merge status" field in the current database. This coded field, which will be named MergeStatus, classifies each record as one of:

• Matched: found in both the current database and import file
• Not Matched: found only in the current database
• Appended: found only in the import file and added to the current database

Apply a Selection Expression if you wish to restrict the import to records that meet particular criteria. If you leave the Selection expression box blank, then all records will be imported. Press the F1 key when your cursor is in this box to get a list of the operators you can use to create the expression.

Complete the ​Import

During the actual data import process, if there are conflicts between the file contents and the field types, codes, date formats, etc., ProVal will pause and ask you how to resolve the errors. A summary of the errors shows the name of the affected field, the number of erroneous records and the type of error. If you wish to see this information for each record, rather than in summary form, click the Details button. If you wish to have only a final error report, check Report errors only at end. Otherwise, the program will pause periodically with interim error reports. The Continue button resumes the import procedure. Click Cancel to quit without importing the data.

When the import is complete, ProVal displays a final count and description of the errors. Check the box entitled Save errors in the error log if you wish ProVal to record them for you.

Import Schema Files

When importing a data file, you can provide an optional “schema file” that contains record layout and Data Dictionary information. If you have your own census management system, you might consider enhancing it to produce schema files. This will free users from having to key in information such as field names, types, etc. that was known in the census management system but isn’t contained in the data file.

By convention, import schema files end in the “.schema.csv” suffix. For example, if you are importing “<filename.ext>”, the corresponding schema file would be named “<filename.ext>.schema.csv” in the same folder. Note that since the schema file’s extension is .csv, it can be opened easily and edited in Excel. If “<filename.ext>.schema.csv” doesn't exist, ProVal will look for a "standard" schema file named “schema.csv” in the same folder. Otherwise, ProVal will assume that no schema file exists.

ProVal indicates the name of the data file you’re importing from and any corresponding schema file on the title bar of the Import Data wizard dialog box.

Schema example:

FIELD,SSN,Social Security Number,SSN,1,9,999-99-9999

FIELD,NAME,Name,CHARACTER,10,39,20

FIELD,SEX,Sex,CODED,40,40

CODE,SEX,,,,,,,M,1,MALE

CODE,SEX,,,,,,,F,2,FEMALE

FIELD,BIRTHDT,Birth Date,DATE,41,48,YYYYMMDD

FIELD,PAY,Pay,NUMERIC,49,58,"9,999,999.99",100

Schema format:

Note that the file contains two different types of records, FIELD and CODE, as indicated in position 1 (pos1).

pos1 = Record type, FIELD or CODE

pos2 = Field name

If pos1=FIELD, then pos3 - pos8 contain:

pos3 = Field description (optional)

pos4 = Field type, NUMERIC, CHARACTER, CODED, DATE, or SSN

pos5 = Field start position (optional; only relevant for fixed-width data)

pos6 = Field end position (optional; only relevant for fixed-width data)

pos7 = Field format (optional)

For NUMERIC fields, use a picture format such as "999,999.99"

For CHARACTER fields, display width (contained in the Field Attributes dialog box) in characters, e.g., 20

For DATE fields, use a picture format using YYYY, MM, DD, and /, e.g., YYYYMMDD

For SSN fields, use a picture format such as 999-99-9999

pos8 = Scaling factor for NUMERIC fields (optional)

If pos1=CODE, then pos9 - pos11 contain:

pos9 = Field value in import file

pos10 = Field code in ProVal database

pos11 = Field label in ProVal database (optional; default is same as pos9)

Notes:

• Although the field format (pos7) is optional, its use is strongly encouraged for date fields.

• Date formats with 2-digit years YY are tolerated but discouraged, because century breakpoints cannot be specified in the schema file.

• Case-sensitive codes are discouraged because there is no way to specify that codes are case-sensitive in the schema file.

• Items with embedded commas should be enclosed in quotes (e.g., the numeric format "9,999,999.99" in the example above).

• Trailing commas can be omitted from each line.

• Rows that don't start with FIELD or CODE will be ignored. This behavior allows comments to be added to the file.