StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL for parsing multi-line data?
text
Body
copied!<p>I have the unfortunate task of having to import data from excel into a database on a regular basis. The table looks something like this:</p> <pre><code> IssueID References 1234 DocID1<cr>DocID2<cr>DocID3 1235 DocID1 1236 DocID2 1237 DocID2<cr>DocID3 </code></pre> <p>References is a multi-line text field. What I'm trying to do is create a Docs table with one-to-many relationship to the Issue table, rather than having these multi-line references.</p> <p>I have the following tables defined:</p> <p>Issue: IssueKey, IssueID, IssueFields</p> <p>Doc: DocKey, DocID, DocRev, DocOwner, etc</p> <p>DocLink: LinkKey, DocKey, IssueKey</p> <p>Since this will be run repeatedly, the Doc table will already exist with the DocIDs defined. So, what I want to do is have a query or VBA code search for each DocID in the References column and add a link based on IssueID if one does not already exist.</p> <p>Simple, Right?</p> <p>Jeff</p> <p>Clarifications:</p> <p>1) I had a third column called "Val1" to show that there were other columns, but that seemed to confuse the issue. There are actually many (way to many, most ignored) columns in the source table, but I only care about the two above.</p> <p>2) I don't have to parse for a delimiter or anything too paranoid: References contains one or more uniquely defined document reference numbers (stored as text). So, a LIKE filter will turn up the list of IssueIDs on a case by case basis.</p> <p>3) Here is an example of acceptable output:</p> <pre><code>IssueID References 1234 DocID1 1234 DocID2 1234 DocID3 1235 DocID1 1236 DocID2 1237 DocID2 1237 DocID3 </code></pre> <p>The ideal solution would take the original excel table (top) and these two tables:</p> <pre><code>IssueKey IssueID 1 1234 2 1235 3 1236 4 1237 DocKey DocID 1 DocID1 2 DocID2 3 DocID3 </code></pre> <p>And populate/update the link table:</p> <pre><code>LinkKey IssueKey DocKey 1 1 1 2 1 2 3 1 3 4 2 1 5 3 2 6 3 3 </code></pre> <p>4) Here is an example of what I expected for a solution (creates #3 above). Unfortunately it crashes Access, so I can't tell if the syntax is correct (edited to reflect field names above).</p> <pre><code>SELECT Q1.IssueID, D1.DocID FROM Docs AS D1, Issues AS Q1 WHERE Q1.IssueID IN ((SELECT Q2.IssueID from Issues AS Q2 where (Q2.References) Like D1.DocID)); </code></pre> <p>5) Giving up on Access for the moment, I've got the following working in MySQL:</p> <pre><code>SELECT Q1.IssueID, D1.DocID FROM Docs AS D1, Issues AS Q1 WHERE Q1.IssueID IN ((SELECT Q2.IssueID from Issues AS Q2 where (Q2.References) Like '%DocID1%')); </code></pre> <p>This works as I'd expect - I get every IssueID with a Reference to DocID1, repeated for every Doc in the table. With the above data it would look like:</p> <pre><code>IssueID References 1234 DocID1 1234 DocID2 1234 DocID3 1235 DocID1 1235 DocID2 1235 DocID3 </code></pre> <p>Now I just want to replace the '%DocID1%' with '%'+D1.DocID+'%' - limiting the results to those document IDs which actually have a match. For some reason I'm getting zero records when I do this - I think I have the syntax for putting wildcards on the correlated field wrong.</p> <p>6) The following works to provide #3 above in MySQL, but the same query translated to access crashes it:</p> <pre><code>SELECT Q1.IssueID, D1.DocID FROM Docs AS D1, Issues AS Q1 WHERE Q1.IssueID IN ((SELECT Q2.IssueID from Issues AS Q2 where (Q2.References) Like CONCAT('%',D1.DocID,'%'))); </code></pre> <p>[in access it becomes ('<em>' & D1.DocID & '</em>')]</p> <p>Conclusion: Access sucks</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload