FreeCEN Administration Tools—csv2dat

WARNING Always work on copies of your data files with these programs and keep copies of the originals safe. They have had the bare minimum of testing and we would not want to damage many hours of painstaking work.

If you come across a bug/problem/invalid output related to the program, I will need a copy of the data file that failed and the error listing to diagnose what went wrong.

I reserve the right to fix errors by changing the documentation.

See revision log for current version and bug fixes.

Overview

csv2dat has two primary functions. The first is to generate an IN-CENS data file from the SS-CENS spreadsheet data entry file. It does a lot of checking of the contents of the fields to pick up errors in data entry. Many of these are correctable later by CHECK-CENS (WINCC) but some may be better corrected in the spreadsheet form before passing on. This is the mode used by CSVCHECK where the data output file is discarded.

For Spreadsheet data entry (SS-CENS mode), a sample blank datasheet is enclosed in the ZIP file. This is provided in .XLS format for all versions of Excel from 5.0 upwards, assorted MS Works versions and in .CSV format for most spreadsheet programs. Take a copy and rename it to be the same as the piece number (e.g. RGxxnnnn.XLS) before you start. The format of the spreadsheed is implicitly documented on the message list page but the column headings should be self explanatory.

The second primary function of csv2dat is to convert a file back to the FreeCEN format after having been repaired by dat2csv and corrected using a spreadsheet program. It is designed to produce a valid IN-CENS data file regardless of Warnings in the .CSV file but Errors should be taken seriously. The message list says what action has been taken automatically. It not only checks the contents of the individual fields for valid data, but also looks for errors in the relationship from one record to another (such as schedule and page sequence).

Running csv2dat (command line)

For running csv2dat via a menu, see FC-TOOLBOX.

For running csv2dat via a CSVCHECK, see the CSVCHECK Home Page.

Create a folder on your disk (we suggest \fctools). Put the program csv2dat.exe into this folder. Export, copy or save your spreadsheet data file into this folder in .CSV (Comma Separated Values) format (e.g. rgxxnnnn.csv). An additional file, CHAP1891.TXT, should be present in the same folder as the program. This 1891 file is used for all years 1851–91. It provides data for the County Code check. If the required file is missing, this check is bypassed with only a warning.

Run the program by double clicking on it

A black console window (DOS) will open which says

Enter command :

This is a prompt for a command line which must contain the file name.

Year        ENG/WLS          SCT

1841        ho41nnnn.csv     hs4zzmmm.csv
1851        ho51nnnn.csv     hs5zzmmm.csv
1861        rg09nnnn.csv     rs6zzmmm.csv
1871        rg10nnnn.csv     rs7zzmmm.csv
1881        rg11nnnn.csv     rs8zzmmm.csv
1891        rg12nnnn.csv     rs9zzmmm.csv
1901        rg13nnnn.csv     rs0zzmmm.csv

• ENG/WLS The final 4 digits (nnnn) are the piece number with leading zeros as required.
• SCT The middle digits (zz) are “10” for a complete district and the final 3 digits (mmm) are the district number with leading zeros as required

Some pieces are rather large—to allow these to be divided to delegate the work, the following is possible.

• SCT zz = 11-99 for partial districts (89 parts possible). These (.VLD files) can be uploaded to the online database in this form.
• CON 1841 ho1xxnnn.csv where xx = pre-defined values. This format is no longer supported.
• ENG/WLS 1841 & 1851 ho4ynnnn.csv & ho5ynnnn.csv where y = a letter “a–z” meaning parts 1 to 26 respectively. These (.VLD files) MUST NOT be uploaded to the online database in this form. Only full pieces are acceptable.

Unpadded numbers (without leading zeros) will be accepted but are converted to the correct form for output files.

It works out the year and piece number from the file name (so don't change them!)

When you hit enter the program will run. There is a scrolling count so you can see how it is getting along, but it is very quick on modern machines.

The program does a lot of sanity checks to ensure that the output data file is valid.

When it finishes it will say how many records have been output, how many households and say

<hit return to exit>...

When you do so, the window will close.

Now look in the messages file (*.erc). Here it will give some information about the run and again say how many records have been output and how many households and also any other problems detected. Check this file carefully before accepting the database. Keep this file with the output file, it will be useful later for correcting the data if necessary.

The types of messages are “Fatal” which stop the program immediately and do not produce an output file; “Error” which may produce an invalid output file; and “Warning” which will attempt to correct the error and produce a valid output file. See also “Info” messages switched on with the -v VERBOSE switch.

If any “Error” messages are seen (and “Fatal” of course) I would suggest that you try to correct the data file and run the program again. “Warning”s may or may not need fixing and this can be done on the input data, or afterwards by other means such as WINCC or VALDREV.

Note that one message can sometimes generate other spurious ones. A lot of things that are warnings in dat2csv are errors in csv2dat and vice-versa because they are concentrating on different problems.

Error & Warning messages

Running csv2dat (advanced instructions)

These extra instructions are for advanced use of the program, not for novices.

The file name can be a full path specification i.e. D:\folder\rg129876.csv. The output and message files will be put in the same path.

The default mode assumes a SS-CENS brief (skeleton) style .CSV input file. A selection of messages (Error and Warning) are given relating to problems with the file, if any.

There are alternatives available by using a switch before the file name e.g.

-c rgxxnnnn.csv

The switches are

• -a = ALL —recreates a data file from the full dump of every field in the database (useful for serious correction of files and debugging). The input file should have been created with the -a parameter of dat2csv. See reference section for a detailed description of this format.
• -c = CHECK-CENS (WINCC) —re-creates a database from a .CSV file containing checked data. The file created has extension .VAL for historical reasons. This may need to be changed to .CHE (for checked) to be accepted by other software.
• -d = VALDREV —re-creates a database from a .CSV file containing validated data. The file created has extension .VLD (for validated).

Use (-a and -c) or (-a and -d) together to achieve both effects at the same time.

• -v = VERBOSE —gives you additional warning messages (marked Info: in the message list). With a damaged file this can generate huge quantities of output.
• -q = QUIET —gives you fewer messages, suppressing all the Warnings. Useful for just checking for corruption of data files.

These switches (above) are mutually exclusive and cannot be combined.

• -h or -? = HELP —gives a (very brief) summary of the options.
• -r = RELATIONSHIPS —uses an optional second auxiliary file (relcodes.txt) to check the Relationship Codes for validity. This file is NOT supplied as part of the distribution and, if required, needs to be created for users. The format is one code per line with an initial capital and only the first six characters are read. There can be no more than 150 lines.

These switches (above) are not available via the FC-TOOLBOX menu.

The command can also be run from a DOS (Command) console from the start by following the command name with the options and file name. e.g.

D:\fctools>csv2dat -v -c rg129876.csv

DOS style / switches can be used if prefered but they are always placed before the file name.

Finally, the program can also be invoked by drag-and-drop of a data file onto the program or a shortcut, e.g. on the desktop [if this feature still works on your PC; it has died on mine :( ]. No opportunity is given to enter switches but it is a very quick way to make default runs. The output and message files are sent to the same folder as the source.

The return code from csv2dat is 12 if any Fatal errors occured, 8 if any Errors occured, 4 if any Warnings occured and zero otherwise.

WARNING: This program CANNOT tell the file type from the data, the correct parameter must be used if it is not the default.

Further WARNINGS The cycle dat2csv followed by csv2dat does not always produce an identical file to the original, even if there are no errors or warnings. This can have some “interesting” side effects.

1. Removing the deleted lines either by hand or using the -z switch in dat2csv changes the relative record numbers making it hard to correlate the data with other files that use the record numbers.
2. All the Household and Household Person numbers are re-calculated from scratch in csv2dat when in BRIEF mode. This can make it harder to correlate information such as the CHECK-CENS (WINCC) .TXT file with the data. This is not generally a serious problem but, if necessary, ALL mode preserves these reference numbers.

Back to introduction

Webmaster
The Parsons family home page.