Some query help

[benkingery]

I have a delimited file that I'm importing into a table (easy enough). This delimited file contains records that need to be split into two separate tables. 1) An Order Header table, and 2) an Order Detail table. Normally this would be easy enough, however I'll need to join these tables back up with a query, so I need the Order Detail table to contain a foreign reference to the Order Header table. Here is what the data looks like in its raw form.


IH|AF374556|2006-06-05|Bob Smith|3 Berk St||Boston|MA| 02199
ID|4056|4|55.50|Floor Lamp|Ground|Test Comment
ID|BC456|1|17.75|Hall Lamp|Ground|Test Comment 2

"IH" indicates that the record is a header record and "ID" indicates that it is a Detail record. Every "ID" that DIRECTLY follows an "IH" belongs to the "IH" above. That said, I want to use the second value in "IH" record (AF374556) as the foreign reference in the detail lines.

Does someone know how to systematically add the foreign value to the related lines? Either through query, or code? Obviously if my data set was this small, I'd add it manually, but I will likely be dealing with hundreds of "IH", "ID" records at a time and doing this on a daily basis, so that's kind of out of the question.

Thanks for your attention.

[dcrake]

This is possible using vba employing the Open File For Input As #1 methodology.


It would mean opening the file and reading the contents of each line and testing for headers and detail. Once written and tested it would be fully automated. I have done this several times with plenty of sucess.

David

[dcrake]

The easiest way to do this is as follows:

Open the source file

 
Dim strData As String
Dim IH As String
 
Open "C:\Test.Text2 For Input As #1
Open "C:\Header.Txt" For Output As #2
Open "C:\Detail.Txt" For Output As #3
 
Do Until EOF(1)
Line Input #1, strData
If Left(stData,2) = "IH" Then 'Header info
   IH = Mid(strData,3,8)
   Print #2, Mid(strData,4) ' Write header details to the header txt file
Else
  Print #3, IH & "|" & Mid(strData,4) ' Write detail to the details txt file with the IH prefixed to the detail line
End If
Loop
Close #1
Close #2
Close #3

So what we have ended up with is the original text file
plus 2 new files
Header.Txt

AF374556|2006-06-05|Bob Smith|3 Berk St||Boston|MA| 02199

Detail.Txt

AF374556|4056|4|55.50|Floor Lamp|Ground|Test Comment
AF374556|BC456|1|17.75|Hall Lamp|Ground|Test Comment 2

Now we can perform two imports first into the header table, second into the detail table. The IH and ID prefixes have been removed from the import as they are not needed anymore.

David