Handling variable fields in a CSV file with JSON?

Question

Im trying to figure out the engineering to get a CSV file into PostgreSQL. Requirements:

1. The CSV has X number of predetermined required fields
2. The CSV file has Y number of dynamic fields

Example:

• TenantCode,DomainCode,PersonCode,ExtraField1,ExtraField2,ExtraFieldN
• "CUST1","STUD","C0001","Donald","Duck","M"
• "CUST1","STUD","C0002","Diana","Duck","F"

into this format (in PostgreSQL 10)

TenantCode---DomainCode---PersonCode---ExtendedFields(JSONB)

• CUST1---STUD---C0001--- {"ExtraField1":"Donald","ExtraField2":"Duck","ExtraFieldN":"M"}
• CUST1---STUD---C0002--- {"ExtraField1":"Diana","ExtraField2":"Duck","ExtraFieldN":"F"}

My original thought would be to (in Python) use Pandas to convert the entire file to json, then use PostgreSQL to COPY into a staging table, then use PostgreSQL SQL to parse out the required fields and insert into the destination table.

Answer 1

I wouldn't do all that. I would just use python to transform the CSV file into another CSV where all the variable fields are a string of JSON.

Create the right table with the jsonb column and load direct into it. To do that use psql and \COPY. You can even create a program that reads from STDIN does the reformatting and outputs to STDOUT. And sits in the pipeline. Or you can write a quick perl script to get this done,

perl reformat.pl stupid1.csv stupid2.csv |
  psql -d test -e '\COPY mymastertable FROM stdin WITH ( format = csv )'

Or whatever.