https://xmlpipedb.lmucs.io/biodb/fall2015/index.php?action=history&feed=atom&title=Blitvak_Week_6Blitvak Week 6 - Revision history2026-06-07T20:11:53ZRevision history for this page on the wikiMediaWiki 1.25.1https://xmlpipedb.lmucs.io/biodb/fall2015/index.php?title=Blitvak_Week_6&diff=2609&oldid=prevBlitvak: forgot to add template!2015-10-15T18:06:43Z<p>forgot to add template!</p>
<table class='diff diff-contentalign-left'>
<col class='diff-marker' />
<col class='diff-content' />
<col class='diff-marker' />
<col class='diff-content' />
<tr style='vertical-align: top;'>
<td colspan='2' style="background-color: white; color:black; text-align: center;">← Older revision</td>
<td colspan='2' style="background-color: white; color:black; text-align: center;">Revision as of 18:06, 15 October 2015</td>
</tr><tr><td colspan="2" class="diff-lineno" id="L134" >Line 134:</td>
<td colspan="2" class="diff-lineno">Line 134:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>#*Command: <code>select SponsorApplicant from applications inner join products on (applications.ApplNo = products.ApplNo) where SponsorApplicant like '%LABS%' and activeingred like '%ASPIRIN%' and activeingred like '%CAFFEINE%' group by SponsorApplicant</code></div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>#*Command: <code>select SponsorApplicant from applications inner join products on (applications.ApplNo = products.ApplNo) where SponsorApplicant like '%LABS%' and activeingred like '%ASPIRIN%' and activeingred like '%CAFFEINE%' group by SponsorApplicant</code></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>#*ACTAVIS LABS UT INC and WATSON LABS</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>#*ACTAVIS LABS UT INC and WATSON LABS</div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">----</ins></div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">{{Template:blitvak}}</ins></div></td></tr>
</table>Blitvak
*I removed the first row (column labels) and replaced all of the ''tabs'' with commas; I also turned the extra commas into null values: <code>cat Product.txt | sed "1D" | sed "s/\t/,/g" | sed "s/,,/,null,/g"</code>
*I removed the first row (column labels) and replaced all of the ''tabs'' with commas; I also turned the extra commas into null values: <code>cat Product.txt | sed "1D" | sed "s/\t/,/g" | sed "s/,,/,null,/g"</code>
*I began placing the apostrophes around the ''varchar'' data with the ''Form'', ''Dosage'', and ''TECode'' data. I placed apostrophes around this data by adding several <code>sed</code> replacements to the pipeline made earlier: <code>sed "s/,/&'/2" | sed "s/,/'&/3" | sed "s/,/&'/3" | sed "s/,/'&/4" | sed "s/,/&'/5" | sed "s/,/'&/6"</code>
*I began placing the apostrophes around the ''varchar'' data with the ''Form'', ''Dosage'', and ''TECode'' data. I placed apostrophes around this data by adding several <code>sed</code> replacements to the pipeline made earlier: <code>sed "s/,/&'/2" | sed "s/,/'&/3" | sed "s/,/&'/3" | sed "s/,/'&/4" | sed "s/,/&'/5" | sed "s/,/'&/6"</code>
−
*I noticed that there are a few commas and apostrophes in the raw data, I decided to initially replace the commas with characters that are not present elsewhere in the raw data (such as''~''); <code>sed "s/,/~/g"</code> was added to the beginning of the command chain to initially replace them, and <code>sed "s/~/,/g"</code> was added to the end of chain in order to restore them. The presence of apostrophes in the data was fixed by through adding <code>sed "s/'/''/g"</code> to the beginning of the pipe (this addition allows pgSQL to understand that these apostrophes are a part of the actual data.
+
*I noticed that there are a few commas and apostrophes in the raw data, I decided to initially replace the commas with characters that are not present elsewhere in the raw data (such as''~''); <code>sed "s/,/~/g"</code> was added to the beginning of the command chain to initially replace them, and <code>sed "s/~/,/g"</code> was added to the end of chain in order to restore them. The presence of apostrophes in the data was fixed by through adding <code><nowiki>sed "s/'/''/g"</nowiki></code> to the beginning of the pipe (this addition allows pgSQL to understand that these apostrophes are a part of the actual data.
*I placed apostrophes around all remaining ''varchar'' data by adding <code>sed "s/,/&'/7" | sed "s/,/'&/8" | sed "s/,/&'/8" | sed "s/\r$/'/g"</code> to the pipeline
*I placed apostrophes around all remaining ''varchar'' data by adding <code>sed "s/,/&'/7" | sed "s/,/'&/8" | sed "s/,/&'/8" | sed "s/\r$/'/g"</code> to the pipeline
*To the end of the current pipeline, I added <code>sed "s/^/insert into products(ApplNo,ProductNo,Form,Dosage,ProductMktStatus,TECode,ReferenceDrug,drugname,activeingred) values(/g" | sed "s/$/);/g"</code> to wrap up the formatting of the raw data (should now be usable as sequences of SQL <code>insert</code> statements)
*To the end of the current pipeline, I added <code>sed "s/^/insert into products(ApplNo,ProductNo,Form,Dosage,ProductMktStatus,TECode,ReferenceDrug,drugname,activeingred) values(/g" | sed "s/$/);/g"</code> to wrap up the formatting of the raw data (should now be usable as sequences of SQL <code>insert</code> statements)