Add Conditional Column to CSV File
How Postgres save your day when Excel for Mac let you down
Today I had to analyze and cleanse some data to migrate to a new system we are developing. The data is coming from an existing access database and it is in CSV format. I couldn’t transform data as it is since there are differences in two data models. For example:
Take two domain models such as partner and partner site (think this relationship similar to a business owner and his business sites). In our new data model design, partner and partner site are two different entities with a foreign key relationship. However, the existing design only contains an entity called partner sites and there is no unique field to identify the partner related to that partner site. The closest information I could find is from the site name. So, in order to fit this dataset to the current model, I have to introduce a new field called partner and then I have to derive partner the value from the site name field.
Following is how my existing data looks like: (input CSV)
And this is how I want the data set for my migration script: (output CSV)
Simply it looks like something you can do from Microsoft Excel. A quick google search took me to the following link which contains step by step instructions that I could follow.
But, there is an issue because I am a Mac user. Microsoft Excel for Mac is kind of an outcast when compare with its Windows sibling. Get & Transform (Power Query) is not supported for Excel for Mac.
Then I decided to import this CSV file to my Postgress database and do my processing using SQL. However, if you use standard import csv feature in Postgres, you have to create the table schema before you upload the file. In my case, it is an overkill. I had few files to analyze and some files have large number of columns. It would be ideal if I have tool that can create the table schema and then insert the data to created table.
As usual, FOSS is the savior again. There is a Postgres plugin named pgcsv created by Friedrich Lindenberg (pudo) which does the two things I mentioned above. It is very easy to use the tool. Just run two commands, you have your CSV file uploaded to the database. Thank Friedrich !!! you saved my day.
I don’t need to mention the rest. It is bread and butter to you. Everyone knows:
- how to add a column to a table
- then insert / update column value.
- finally, export the updated table back as csv.
