SQL for parsing multi-line data?

Question

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:

  IssueID   References  
  1234      DocID1<cr>DocID2<cr>DocID3
  1235      DocID1
  1236      DocID2
  1237      DocID2<cr>DocID3

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.

I have the following tables defined:

Issue: IssueKey, IssueID, IssueFields

Doc: DocKey, DocID, DocRev, DocOwner, etc

DocLink: LinkKey, DocKey, IssueKey

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.

Simple, Right?

Jeff

Clarifications:

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.

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.

3) Here is an example of acceptable output:

IssueID   References
1234      DocID1
1234      DocID2
1234      DocID3
1235      DocID1
1236      DocID2
1237      DocID2
1237      DocID3

The ideal solution would take the original excel table (top) and these two tables:

IssueKey   IssueID
   1        1234
   2        1235
   3        1236
   4        1237

DocKey     DocID
  1        DocID1
  2        DocID2
  3        DocID3

And populate/update the link table:

LinkKey  IssueKey  DocKey
   1        1        1
   2        1        2
   3        1        3
   4        2        1
   5        3        2
   6        3        3

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).

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));

5) Giving up on Access for the moment, I've got the following working in MySQL:

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%'));

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:

IssueID   References
1234      DocID1
1234      DocID2
1234      DocID3
1235      DocID1
1235      DocID2
1235      DocID3

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.

6) The following works to provide #3 above in MySQL, but the same query translated to access crashes it:

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,'%')));

[in access it becomes ('' & D1.DocID & '')]

Conclusion: Access sucks

Answer 1

I think using the word "parse" in the title has confused the crap out of everyone. The bug in Access was that a correlated query performed on a query (instead of a table) causes a hang. So instead, I created a temporary table that ads the References column (with the multi-line text) to the Issues table so I have access to the other fields. The final query creates the link table described above, along with the DocID and IssueID for reference:

SELECT Q1.IssueID, Q1.IssueKey, D1.DocKey, D1.DocID
FROM Issues AS Q1, Documents AS D1
WHERE Q1.IssueID in 
  (SELECT  Q2.IssueID FROM Issues AS Q2 WHERE Q2.References LIKE ("*" & D1.DocID & "*"));

The inner select pulls the list of issues which has a given document in the references column. The outer select performs this for each document, resulting in the aggregate list.

Answer 2

I suggest that you research SQL Server Integration Services (SSIS). This tool was created to do this kind of data import/export as quickly as possible with as little code.

Read about it. Do some hands on labs to see if any examples are close to what your trying to do.

http://en.wikipedia.org/wiki/SQL_Server_Integration_Services http://www.microsoft.com/downloads/details.aspx?familyid=b1145e7a-a4e3-4d14-b1e7-d1d823b6a447&displaylang=en