I have a series of files I've downloaded from the Texas Ethics Commission (a misnamed . You can see an example of their layout in the
ReadMe.txt. I've made an etl library which processes the
ReadMe.txt to generate the SQL DDL to create this schema, and load it from the CSVs. The thing is, I think this is a standardized format. I imagine it's backed by something like a PICK (which is a BASIC database) or something COBOL-esque -- having worked with them before, and that this is something like a MARC, or ANSI, or ISO standard.
I'd like to potentially abstract out my ETL script to benefit others who use this format.
Some identifying features of the format are that it supports
Arraysand internal one-to-many relations on the record
- at least types
- the export is labeled "Flat File Architecture Record Listing"
For example here you see this,
Array 4050 loanGuarantorLoanPersent[5/ROW_MAJOR] CsvPublicExportLoanGuarantorLoanPersent 810 Guarantors for the loan (maximum 5) 46 guarantorPersentTypeCd String 30 Type of guarantor name data - INDIVIDUAL or ENTITY 47 guarantorNameOrganization String 100 For ENTITY, the guarantor organization name 48 guarantorNameLast String 100 For INDIVIDUAL, the guarantor last name
That defines a structure called a
loanGuarantorLoanPersent and essentially declares that there are five of them. So the export CSV will have something like
You can see an example of this data here,