importing multiple lines

  • Thread starter Thread starter Mr.E
  • Start date Start date
M

Mr.E

I'm importing data from a proprietary db. To do so I have
to kick the data out as text then import into Access 97 or
00 (we have both). The data look like this:

123456$PName, Patient$Name, MD$09-30-2002
3031 - 93508 - Coronary Angiography, w/o LHC
1861 - 93556 - Imaging Supervision, Coronary / Aortic
1969 - 93539 - LIMA/RIMA Angiography
1471 - 93545 - Coronary Angiogram
1900 - 93544 - Aortogram

The dollar signs being delimiters. I am limited by the
vendor software, in that I am unable to attach the unique
id to each procedure for export. Any ideas on how I can
attach the unique ID(first number of the record) to each
procedure when I import into Access? I had no luck with
the vendor. The number of Procedures vary from patient to
patient.

Thanks

Mr.E
 
I am assuming here that each line below the first line is a procedure for
the patient on the first line.

You are going to have to use VBA code. The LineInput command will read an
entire line of a text file into a variable. Then you will have to parse the
this string into separate fields. Your problem is complicated by the fact
that the first line should go into one table (Patient) and the rest into
another related table (Procedures). The logic would go something like this:

Open the text file
Do (until the end of the text file)
LineInput the first line into string variable
Parse string into Patient Fields
Store the patient number in a variable
Do (until you come to another patient line)
LineInput the procedure line
write the patient variable into the foreign key of the procedure record
parse the string into the rest of the fields in the record
Loop
Loop
Close the text file

Now, I don't have anything directly on point, but on my website (see sig
below) is a small sample database called "ImportLineInput.mdb", which
illustrates how to use the LineInput to read an oddly delimited text file.
That should get you started.
 
Hi E,

I'd do this by writing a little script to create a new text file that
omits the "ID" lines but prepends the ID number to each of the other
lines.

Here's a VBScript that does the job for files where the "ID" line
*begins* with a constant value; you'll need to modify it to identify
your ID lines and extract the numbers (if they're all 6-digits followed
by a $ it'll be easy).


'Prepend.vbs
'VBScript
'Call from commandline as
' cscript Prepend.vbs GroupTag InFile OutFile
'where GroupTag is the string that begins a group.
'E.g. if the data is
'
'Group # 1
'First Line
'Second Line
'Group # 2
'First Line
'Second Line
'Third Line
'
'use a command line like
' cscript Prepend.vbs "Group # " Infile.txt Outfile.txt
'to get the output
'1,First Line
'1,Second Line
'2,First Line
'2,Second Line
'2,Third Line


Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strTest 'As String
Dim strTag 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the "group" data
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
strTest = WScript.Arguments(0)
Set fIn = fso.OpenTextFile(WScript.Arguments(1))
Set fOut = fso.CreateTextFile(WScript.Arguments(2))

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine

'CHANGE THIS NEXT BIT TO SUIT YOUR FILE
If Left(strLine, Len(strTest)) = strTest Then
'this line starts a group so store its value
strTag = Mid(strLine, Len(strTest)+1)
Else
fOut.Write strTag & DELIM & StrLine & Chr(13) & Chr(10)
End If
Loop

fIn.Close
fOut.Close
 
Thanks for the help. I'll give this a try.
-----Original Message-----
Hi E,

I'd do this by writing a little script to create a new text file that
omits the "ID" lines but prepends the ID number to each of the other
lines.

Here's a VBScript that does the job for files where the "ID" line
*begins* with a constant value; you'll need to modify it to identify
your ID lines and extract the numbers (if they're all 6- digits followed
by a $ it'll be easy).


'Prepend.vbs
'VBScript
'Call from commandline as
' cscript Prepend.vbs GroupTag InFile OutFile
'where GroupTag is the string that begins a group.
'E.g. if the data is
'
'Group # 1
'First Line
'Second Line
'Group # 2
'First Line
'Second Line
'Third Line
'
'use a command line like
' cscript Prepend.vbs "Group # " Infile.txt Outfile.txt
'to get the output
'1,First Line
'1,Second Line
'2,First Line
'2,Second Line
'2,Third Line


Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strTest 'As String
Dim strTag 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the "group" data
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
strTest = WScript.Arguments(0)
Set fIn = fso.OpenTextFile(WScript.Arguments(1))
Set fOut = fso.CreateTextFile(WScript.Arguments(2))

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine

'CHANGE THIS NEXT BIT TO SUIT YOUR FILE
If Left(strLine, Len(strTest)) = strTest Then
'this line starts a group so store its value
strTag = Mid(strLine, Len(strTest)+1)
Else
fOut.Write strTag & DELIM & StrLine & Chr(13) & Chr(10)
End If
Loop

fIn.Close
fOut.Close





I'm importing data from a proprietary db. To do so I have
to kick the data out as text then import into Access 97 or
00 (we have both). The data look like this:

123456$PName, Patient$Name, MD$09-30-2002
3031 - 93508 - Coronary Angiography, w/o LHC
1861 - 93556 - Imaging Supervision, Coronary / Aortic
1969 - 93539 - LIMA/RIMA Angiography
1471 - 93545 - Coronary Angiogram
1900 - 93544 - Aortogram

The dollar signs being delimiters. I am limited by the
vendor software, in that I am unable to attach the unique
id to each procedure for export. Any ideas on how I can
attach the unique ID(first number of the record) to each
procedure when I import into Access? I had no luck with
the vendor. The number of Procedures vary from patient to
patient.

Thanks

Mr.E
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top