0

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 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

  • Arrays and internal one-to-many relations on the record
  • at least types BigDecimal, Long, Date, String
  • the export is labeled "Flat File Architecture Record Listing"

Arrays

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

guarantorPersentTypeCd1,guarantorNameOrganization1,guarantorNameLast1,guarantorPersentTypeCd2,guarantorNameOrganization2,guarantorNameLast2,guarantorPersentTypeCd3,guarantorNameOrganization3,guarantorNameLast3...

You can see an example of this data here,

0

I believe this is Political Disclosure Standard Electronic Reporting Format (PDSERF) a format used by [SDR Technologies] which later went off and was acquired by National Information Consortium Technologies, Inc. in 2000

As for the file format, I found this about it

This format utilizes EDI, the Electronic Data Interchange, standards developed and maintained by the Accredited Standards Committee X.12 of the American National Standards Institute."

You can tie this into Texas in this proposal by the Texas Ethics Commission which was later subsequently rewarded

On January 11, 2000 I attended a public briefing in the Capitol Extension held by the Texas Ethics Commission and SDR Technologies, Inc. to discuss specifications for a standard file format for filing reports electronically with the Ethics Commission. This file format will be available to commercial vendors so they may generate campaign finance reports for filing electronically from their software. Additionally, the briefing demonstrated how filers will be able to convert data from spreadsheets such as Excel or Access for filing campaign finance reports electronically. On February 16, 2000 I attended another similar public briefing, which included a filer software demo.

You can also see them talk about it here,

The document produced by SDR Technologies describing the filing format used for electronically submitting disclosure and registration documents to the Texas Ethics Commission (i.e., containing the technical specifications) is available upon request. It is located in the appraisal files of the Archives and Information Services Division of the Texas State Library and Archives Commission, and is titled "Texas PDSERF/Plus (.PDP) File Layouts."

I think the PDSERF/Plus is an SDR Technologies thing. They also seem to service the Federal Election Commission. You can see them pitch their product to the State of California here, and Michagan, and Louisiana also seem to use it (or have used it).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.