Difference between revisions of "Week 6 E-notes Eyanosch"
(starting week 6 e-notes) |
|||
| (6 intermediate revisions by the same user not shown) | |||
| Line 3: | Line 3: | ||
Week 6 E-Notes | Week 6 E-Notes | ||
| + | After downloading the zip files and unzipping them I need to rework the files so they can be loaded into postgreSQL | ||
| + | cat application.txt | sed"s/^/insert into Applications(ApplNo, ApplType, SponsorApplicant, MostRecentLabelAvailableFlag, CurrentPatentFlag, ActionType, Chemical_Type, Ther_Potential, Orphan_Code) values (/g" | ||
| + | * | ||
| + | sed "s/ */ /g" | ||
| + | eyanosch@ab201:~$ cat application.txt | sed "s/ *//g" | sed "s/\t/ /g" | ||
| + | |||
| + | # 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 | ||
| + | |||
| + | After Copying the page of code and entering into the SQL file, the results were: | ||
| + | |||
| + | Query returned successfully: one row affected, 2329 ms execution time. | ||
| + | |||
| + | |||
| + | Now onto 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. | ||
| + | |||
| + | 2. | ||
| + | |||
| + | 3. | ||
| + | |||
| + | 4. | ||
| + | |||
| + | 5. | ||
| + | |||
| + | 6. | ||
| + | |||
| + | 7. | ||
| + | |||
| + | When first searching the drugs I used select drugname from product where activeingred like '%ATROPINE%'; but too many came up and with anything containing the word atropine, not just atropine by itself. | ||
| + | In order to narrow the search the % were removed. | ||
| + | |||
| + | |||
| + | 8. | ||
| + | select form from product where drugname like 'BENADRYL'; | ||
| + | select dosage from product where drugname like 'BENADRYL'; | ||
| + | |||
| + | Used two seperate searches to find the form and dosage of Benadryl products | ||
| + | |||
| + | |||
| + | 9. | ||
| + | select * from product where drugname like '%ESTROL%'; | ||
| + | |||
| + | that provides any drug names with Estrol in the name, not at the end. If I change the last % to a $ then it might work. It didn't but I was close, I asked Mary for help and she told me to get rid of the other % and add a ~ | ||
| + | |||
| + | |||
| + | 10. | ||
| + | took me a while to get the group part of the SQL search down but it made everything easier and produced the desired results | ||
| + | |||
| + | 11. | ||
| + | |||
| + | 12. | ||
| + | Right now I'm getting errors because I am trying to combine varchars with integers from the different tables, If I change my the table to varchar instead of integer then they will probably be able to communicate | ||
| + | 13. | ||
Latest revision as of 05:39, 15 October 2015
Week 6 E-Notes
After downloading the zip files and unzipping them I need to rework the files so they can be loaded into postgreSQL
cat application.txt | sed"s/^/insert into Applications(ApplNo, ApplType, SponsorApplicant, MostRecentLabelAvailableFlag, CurrentPatentFlag, ActionType, Chemical_Type, Ther_Potential, Orphan_Code) values (/g"
*
sed "s/ */ /g"
eyanosch@ab201:~$ cat application.txt | sed "s/ *//g" | sed "s/\t/ /g"
- 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
After Copying the page of code and entering into the SQL file, the results were:
Query returned successfully: one row affected, 2329 ms execution time.
Now onto 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.
2.
3.
4.
5.
6.
7.
When first searching the drugs I used select drugname from product where activeingred like '%ATROPINE%'; but too many came up and with anything containing the word atropine, not just atropine by itself. In order to narrow the search the % were removed.
8.
select form from product where drugname like 'BENADRYL';
select dosage from product where drugname like 'BENADRYL';
Used two seperate searches to find the form and dosage of Benadryl products
9.
select * from product where drugname like '%ESTROL%';
that provides any drug names with Estrol in the name, not at the end. If I change the last % to a $ then it might work. It didn't but I was close, I asked Mary for help and she told me to get rid of the other % and add a ~
10.
took me a while to get the group part of the SQL search down but it made everything easier and produced the desired results
11.
12. Right now I'm getting errors because I am trying to combine varchars with integers from the different tables, If I change my the table to varchar instead of integer then they will probably be able to communicate 13.
Class Journals
Weekly Assignments
Personal Journal
Electronic Notes (E-notes)
Week 6 E-notes Eyanosch
Class (personal) Notes
