Difference between revisions of "Eyanosch Week 6"

Revision as of 04:31, 15 October 2015

Erich Yanoschik

Week 6

Application

After downloading the zip files and unzipping them I need to rework the files so they can be loaded into postgreSQL

eyanosch@ab201:~$ cat application.txt | sed "s/ *//g" |  sed "s/\t/ /g"

1. pulls up information, removes the spaces, and removes the tabs. Adds a space inbetween each column

cat application.txt | sed "s/. /&,/g"| sed "s/ *//g" |  sed "s/\t/ /g" 

eyanosch@ab201:~$ cat application.txt | sed "s/ *//g" |  sed "s/\t/ /g" | sed "s/ /' /g" | sed "s/. /&'/g" | sed "s/ /,&/g" | sed "s/\r$/'/g" | sed "s/^/&'/g" | sed "s//null/g"

I had to add ' behind and infront of every character and then the empty spaces that were I replaced with the word null

cat application.txt | sed "s/ *//g" |  sed "s/\t/ /g" | sed "s/ /' /g" | sed "s/. /&'/g" | sed "s/ /,&/g" | sed "s/\r$/');/g" | sed "s/^/&'/g" | sed "s//null/g" |sed "s/^/insert
into Application(ApplNo, ApplType, SponsorApplicant, MostRecentLabelAvailableFlag, CurrentPatentFlag, ActionType, Chemical_Type, Ther_Potential, Orphan_Code) 
values (/g" | sed "1D" > ~/public_html/application.sql.txt

This should give the proper syntax for adding from the database the SQL

Product

cat Product.txt |  sed "s/'/\"/g" | sed "s/ *//g" |  sed "s/\t/ /g" | sed "s/ /' /g" | sed "s/. /&'/g" | sed "s/ /,&/g" | sed "s/\r$/');/g" | sed "s/^/&'/g" | sed "s//null/g" | 
sed "s/^/insert into product(ApplNo, ProductNo, Form, Dosage, productMktStatus, TECode, ReferenceDrug, drugname, activeingred) values (/g" | sed "1D" > ~/public_html/Product.sql.txt

Apply the same principle but there were 's within the text so I changed them into " and replaced them with null

Ran the program in SQL:

Query returned successfully: one row affected, 4916 ms execution time.

#1

create table Application(ApplNo varchar, ApplType varchar, SponsorApplicant varchar, MostRecentLabelAvailableFlag boolean, CurrentPatentFlag boolean, ActionType varchar, Chemical_Type varchar, Ther_Potential varchar, Orphan_Code varchar)

create table product(ApplNo int, ProductNo int, Form varchar, Dosage varchar, productMktStatus varchar, TECode varchar, ReferenceDrug varchar, drugname varchar, activeingred varchar)

#2

cat application.txt | sed "s/ *//g" |  sed "s/\t/ /g" | sed "s/ /' /g" | sed "s/. /&'/g" | sed "s/ /,&/g" | sed "s/\r$/');/g" | sed "s/^/&'/g" | sed "s//null/g" |sed "s/^/insert
into Application(ApplNo, ApplType, SponsorApplicant, MostRecentLabelAvailableFlag, CurrentPatentFlag, ActionType, Chemical_Type, Ther_Potential, Orphan_Code) 
values (/g" | sed "1D" > ~/public_html/application.sql.txt

cat Product.txt |  sed "s/'/\"/g" | sed "s/ *//g" |  sed "s/\t/ /g" | sed "s/ /' /g" | sed "s/. /&'/g" | sed "s/ /,&/g" | sed "s/\r$/');/g" | sed "s/^/&'/g" | sed "s//null/g" | 
sed "s/^/insert into product(ApplNo, ProductNo, Form, Dosage, productMktStatus, TECode, ReferenceDrug, drugname, activeingred) values (/g" | sed "1D" > ~/public_html/Product.sql.txt

#3

wc application.txt

Results: 19757 147418 1616281 application.txt

wc Product.txt

Results: 32796 369649 2858064 Product.txt

19757 and 32796 records in each respective file

#4

select count(*) from application

select count(*) from product

both give the number of records in each table

#5

The numbers should be the same, since each column corresponds to a specific record in the database

#6

• a.

The Drug Names are:

"LOVENOX" "VELCADE" "VIDAZA" "ENOXAPARINSODIUM" "ACTEMRA" "AZACITIDINE"

• b.

select drugname from product where form like '%INJECTABLE;INTRAVENOUS,SUBCUTANEOUS%';

#7

• a.

"ATROPEN" "ATROPEN" "ATROPEN" "ATROPEN" "ATROPINE"

Atropen and Atropine are the drug products whose active ingredient is Atropine

• b.

select drugname from product where activeingred like 'ATROPINE';

#8

#9

Class Journals

Class Journal Week 1

Class Journal Week 2

Class Journal Week 3

Class Journal Week 4

Class Journal Week 5

Class Journal Week 6

Class Journal Week 7

Class Journal Week 8

Class Journal Week 9

Class Journal Week 10

Class Journal Week 11

Weekly Assignments

Week 1

Week 2

Week 3

Week 4

Week 5

Week 6

Week 7

Week 8

Week 9

Week 10

Week 11

Week 12

Week 13

Personal Journal

Eyanosch Week 2

Eyanosch Week 3

Eyanosch Week 4

Eyanosch Week 5

Eyanosch Week 6

Eyanosch Week 7

Eyanosch Week 8

Eyanosch Week 9

Eyanosch Week 10

Eyanosch Week 11

Eyanosch Week 12

Eyanosch Week 13

Eyanosch Week 14

Eyanosch Week 15

Electronic Notes (E-notes)

Week 3 E-notes Eyanosch

Week 4 E-notes Eyanosch

Week 5 E-notes Eyanosch

Week 6 E-notes Eyanosch

Week 7 E-notes Eyanosch

Week 8 E-notes Eyanosch

Week 9 E-notes Eyanosch

Week 10 E-notes Eyanosch

Week 11 E-notes Eyanosch

Week 12 E-notes Eyanosch

Week 13 E-notes Eyanosch

Week 14 E-notes Eyanosch

Week 15 E-notes Eyanosch

Class (personal) Notes

Week 3 Notes

Week 4 Notes

Week 5 Notes

Week 6 Notes

Week 7 Notes

Week 8 Notes

Week 9 Notes

Week 10 Notes

Week 11 Notes

Week 12 Notes

Week 13 Notes