=== Formatting the application.txt file ===

=== Formatting the application.txt file ===

Before starting this assignment, I didn't realized that I could easily remove the spaces using the regex for the tab character <code>\t</code>, and so I tried to take out the unnecessary blank spaces using the regex <code>\s*</code>. The result of using this method is the following (which didn't really work out since I got frustrated from all the outliers in this txt file and I eventually trashed this set of commands):

Before starting this assignment, I didn't realized that I could easily remove the spaces using the regex for the tab character <code>\t</code>, and so I tried to take out the unnecessary blank spaces using the regex <code>\s*</code>. I looked up online how to easily remove blank spaces and stumbled upon both <code>\t</code> and <code>\s*</code>, but decided to use <code>\s*</code>. I decided to go through each comma and attach the quotes, but this proved quite ineffective, tedious, and time-consuming. The result of using this method is the following (which didn't really work out since I got frustrated from all the outliers in this txt file and I eventually trashed this set of commands):

  cat application.txt |  

  cat application.txt |  

  sed "s/'V'$/&);/" > ~/public_html/application.sql.txt

  sed "s/'V'$/&);/" > ~/public_html/application.sql.txt

From doing this, I realized how badly formatted these data are so I decided to start over:

From doing this, I realized how badly formatted these data are so I decided to start over and so the following works. There were still outliers, but since I already knew what they were from my previous attempt, I decide to slightly hardcode the commands for the ther_potentials:

  cat application.txt |

  cat application.txt |

  sed "1d"  > ~/public_html/application.sql.txt

  sed "1d"  > ~/public_html/application.sql.txt

But then I realized that there was an even easier way of doing this assignment since PostgreSQL accepts quotes around Boolean values and numbers. (I didn't notice the tips that were before the questions so I didn't realize right away that we could use less commands):

But then I realized that there was an even easier way of doing this assignment since PostgreSQL accepts quotes around Boolean values and numbers. (I didn't notice the tips that were before the questions so I didn't realize right away that we could use less commands). This first removes the tabs, then the spaces, then attaches the closing parenthesis, then replaces the empty strings with null, then removes the first instance of a quote, then removes the header, and finally adds the necessary "insert..." commands for SQL:

  cat application.txt |

  cat application.txt |

  sed "s/\t/','/g" |

  sed "s/\t/','/g" |

  sed "s/^/insert into application (ApplNo,ApplType,SponsorApplicant,MostRecentLabelFlag,CurrentPatentFlag,ActionType,

  sed "s/^/insert into application (ApplNo,ApplType,SponsorApplicant,MostRecentLabelFlag,CurrentPatentFlag,ActionType,

                                   ChemicalType,Ther_Potential,OrphanCode) values (/g" > ~/public_html/application.sql.txt

                                   ChemicalType,Ther_Potential,OrphanCode) values (/g" > ~/public_html/application.sql.txt

=== Formatting product.txt file ===

=== Formatting product.txt file ===