Dividing fields into records with a common field
I am not sure my heading describes what I want to do but I am working with a database of historic mining licenses. The files have been moved into CSV <name.txt> files.
One file's records now look like this.
"Anderson, Thos.","67-12-02","1632","68-12-02","1392","69-11-03","1247"," "," ","-","ml"
<Full name> <License issue date> <License number for that year> <License issue date>.
What I would like to do is divide up the years so that I have records with the name, Lic year and Lic number for each year. I can lose the <ml> field as it is a source reference. the <" "> fields are sometimes filled with additional years.
I have one file of about 5,000 records.
What I want is: "Anderson, Thos."," "67-12-02","1632"
Ideally the name would be two fields, but I have already posted on that topic.
A complication is that the folks who input the information back in 1986 did not allot specific fields for specific years. So, 1871 might be in the first field rather than 1867, but only if there was no information for the earlier years.
If this is too much work I can obviously use it the way it is, but dividing it up would allow more information on population patterns to be collected.
Hope this makes sense.
Or, I could send the file to a developer.