open, convert, then import list of files

Hi All,


I have a server directory that recives patholology reports (i .pit format)

1. I need to automate the opening of each of these into FileMaker and
then save each converted FileMaker file, in to a different directory.
(on a winxp system). Can this be done ?


2. I then need to generate a list of the filenames of the converted
FileMaker files.

If I can generate a list of filenames, I should then be able to open
each file individually, into a Converter file that parses the PIT format
date into a relational FM format (have this working)

The conversion and initial port is required to be as automated as
possible (i.e. and ideally no user input)

I would like to work up this up on a Mac initially (OSX.4.20, FMA 8.5).


any suggestions how to approach this?

regards

Chris

Re: open, convert, then import list of files

> I have a server directory that recives patholology reports (i .pit format)

Without knowing how pit is formatted your questions can't really be
answered.

If it is anything like plain text, tab- or comma delimited.
you could import a list of all files in a certain directory into a textfield
from there it wouldn't be to difficult to set-up an import into an empty
table, do some massaging en redirect the contents to the main file.

But without further details from your incoming pit and your target fmp file
it is difficult to go into all this deeper.

Ursus

Re: open, convert, then import list of files

In article <4736ef25$0$3898$dbd43001@news.wanadoo.nl>, "Ursus"
wrote:

> > I have a server directory that recives patholology reports (i .pit format)
>
> Without knowing how pit is formatted your questions can't really be
> answered.
>
> If it is anything like plain text, tab- or comma delimited.
> you could import a list of all files in a certain directory into a textfield
> from there it wouldn't be to difficult to set-up an import into an empty
> table, do some massaging en redirect the contents to the main file.
>
> But without further details from your incoming pit and your target fmp file
> it is difficult to go into all this deeper.

I'd also question the creation of separate FileMaker files - that's
going to quickly bloat the system. It would probably be better to have
separate RECORDS in one database.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: open, convert, then import list of files

Ursus wrote:
>> I have a server directory that recives patholology reports (i .pit format)
>
> Without knowing how pit is formatted your questions can't really be
> answered.
>
> If it is anything like plain text, tab- or comma delimited.
> you could import a list of all files in a certain directory into a textfield
> from there it wouldn't be to difficult to set-up an import into an empty
> table, do some massaging en redirect the contents to the main file.
>
> But without further details from your incoming pit and your target fmp file
> it is difficult to go into all this deeper.
>
> Ursus
>
>



Hi Ursus,

the structure of the PIT files is not at issue at this point. My
approach is:

1. open each individual pit file in FileMaker, and save as a FM file
2. Import each converted PIT FM file into a single PitImporter FM file
3. do the parsing
(4. push the parsed data from the intermediate (2/3) importer/parser
file, into a main patient dtaabase


This all works, one by one.
What I need to do now, is to be able to automate the opening and
conversion of Pit into FM files, and also to generate list of FM files
that can be used to feed step 2


I have looked at Troi File plugin, which will do step 2, although the
client is not keen on commercial plug-ins. I am hoping for a
vba/applescript approach to this.



PIT files are used as an Australian (so I beleive) alternative to HL7
formats/
They have 3 digit line codes.
The each pit report may contain tests results for one or more patients
each pit report may contain one or more test results for each patient
reported
there is one row code (301) for the actual main chemistry/blood results,
and from one source (path lab). So there may be 30 row 301 lines in a
pit report. I currently have 55 different result types for a 301 row e.g.

301 Albumin 42 g/L (35 - 50)
301 * Albumin 34 g/L (35 - 50)
301 Bicarb. 25 mmol/L (20 - 33) T.Protein 71 g/L (60 - 82)
301 Chloride 98 mmol/L (98 - 109) Phosphate 0.89 mmol/L (0.75 - 1.45)


etc.
The 301 syntax for any given chem result will vary depending on the lab
company issuing the report. So Albumin form one path company, Alb from
another. And the combinations on a row vary from path company to path
company! Non-standard standards!


The object of the excercise is to parse out the numeric value for each
chemistry result (Bicarb 25; TProtein, 71...) and push that into a main
database that records all patient info (operation, visits, operation
data, tests...).



What I really am looking for, is vba/applescript to importconvert all
files in a folder

regards

Chris

Re: open, convert, then import list of files

Helpful Harry wrote:
> In article <4736ef25$0$3898$dbd43001@news.wanadoo.nl>, "Ursus"
> wrote:
>
>>> I have a server directory that recives patholology reports (i .pit format)
>> Without knowing how pit is formatted your questions can't really be
>> answered.
>>
>> If it is anything like plain text, tab- or comma delimited.
>> you could import a list of all files in a certain directory into a textfield
>> from there it wouldn't be to difficult to set-up an import into an empty
>> table, do some massaging en redirect the contents to the main file.
>>
>> But without further details from your incoming pit and your target fmp file
>> it is difficult to go into all this deeper.
>
> I'd also question the creation of separate FileMaker files - that's
> going to quickly bloat the system. It would probably be better to have
> separate RECORDS in one database.
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)


Hi Harry,

Pit files are just text files, they have line numbers 0001-999, many of
the line numbers can be repeated

the separate FM files are a temporary construct. Each pit file can open
into FM, creating an FM record for each text row in the pit, then the FM
replicate is saved, and used as a data source into a single
Importer/Parser FM file. The converted PIT.fm7 is then redundant

There are no consistent delimiters.
These reports can originate from different Patology lab companies, and
The syntax for any given line, will vary depending on which path lab.




an extract:

001 XXXXXXXX PATHOLOGY - ELECTRONIC REPORT TRANSMISSION 07 01/07/1996
002
003 Report Run No : XXXXXXXX Created: 02/11/2007 at 14:00:00
004 Surgery: Reports: DD/MM/YYYY 10:00:00 to DD/MM/YYYY 10:00:00
009
------------------------------------------------------------ ----------------
010 DR AAA BBBBBBB XXXXXX
019
------------------------------------------------------------ ----------------
020 Your Ref. Patient Name Lab Ref. Test Code
021 CCCCC
029--------------------------------------------------------- -------------------
100 Start Patient : CCCCC
101 9 Somewhere ST
104 Birthdate: DD/MM/YYYY Age: NN Sex: F
109
110 Your Reference :
111 Lab Reference : 07-NNNNNNNN Business :P
112 Medicare Number: XXXXXXXXXXX
115 Phone Enquiries:
119
121 Referred By : DR AAA BBBBBBB
122 Copy To : DR AAA BBBBBBB
122 Copy To : DR AAA BBBBBBB
123 Addressee : DR AAA BBBBBBB
129
200 Start of Result:
201 Specimen :
203 Requested : DD/MM/2007
204 Collection : DD/MM/2007 10:00
205 Name of Test : GENERAL CHEMISTRY
206 Reported : 12/11/2007 14:00
207 Confidential : N
208 Test Category : R
209
210 Normal Result :
211 Requested Tests: MBI
212 RequestComplete: N
299
301
301 GENERAL CHEMISTRY SPECIMEN: SERUM
301
301 Sodium 137 mmol/L (136 - 146) Calcium 2.22 mmol/L (2.10
- 2.55)
301 Potassium 4.0 mmol/L (3.5 - 5.2) Calc iCa 1.12 mmol/L (1.07
- 1.27)
301 Chloride 98 mmol/L (98 - 109) Phosphate 0.89 mmol/L (0.75
- 1.45)
301 Bicarb. 25 mmol/L (20 - 33) T.Protein 71 g/L (60 - 82)
301 Albumin 39 g/L (35 - 50)
301
301 Urea 3.3 mmol/L (2.5 - 8.0) Alk.Phos 53 U/L (30 - 120)
301 Creat. 66 umol/L (50 - 95) Bilirubin 8 umol/L (< 25)
301 eGFR > 60 mL/min/1.73m2
301 GGTP 13 U/L (< 50)
301 Urate 0.20 mmol/L (0.15 - 0.45 AST 12 U/L (< 41)
301 Glucose 4.8 mmol/L (see below) ALT 12 U/L (< 51)
301 LD 121 U/L (50 - 280)





regards


Chris

Re: open, convert, then import list of files

Hi Ursus,
>
> the structure of the PIT files is not at issue at this point. My approach
> is:
>
> 1. open each individual pit file in FileMaker, and save as a FM file
> 2. Import each converted PIT FM file into a single PitImporter FM file
> 3. do the parsing
> (4. push the parsed data from the intermediate (2/3) importer/parser file,
> into a main patient dtaabase
>
>
> This all works, one by one.
> What I need to do now, is to be able to automate the opening and
> conversion of Pit into FM files, and also to generate list of FM files
> that can be used to feed step 2
>
>
> I have looked at Troi File plugin, which will do step 2, although the
> client is not keen on commercial plug-ins. I am hoping for a
> vba/applescript approach to this.
>

There are free plug-ins like the File Toolbox (Mac os9 and windows)
http://www.geocities.com/SiliconValley/Network/9327/plPLUGs. html

Depending on what you want to be done as a pre-process I would not look into
VBA. First The VB Object for filemaker is very limited, to the point of
being an invalid set. Integrating is hell. You could also use any good
text-editor, just to remove line-feeds, returns as a preprocess. Then
filemaker will treat it as one large chunk of data. Also PowerGrep (windows)
or any other flavor of Grep would be a good choice.

Without plugins I think I would create a dummy table, with one field only.
Import the pit file. Let a script loop thorugh the records and collect all
the date needed in a variable then dump the contents in the file you are
going to work with.

>
> The object of the excercise is to parse out the numeric value for each
> chemistry result (Bicarb 25; TProtein, 71...) and push that into a main
> database that records all patient info (operation, visits, operation data,
> tests...).
>

Once the data is in a fmp file I think this filtering could easily be set
up. The number of result types will be limited, even if there a several
hunderds, it would be easy enough the post-process inside fmp.

Keep well Ursus

Re: open, convert, then import list of files

In article , Chris Brown
wrote:

> Helpful Harry wrote:
> > In article <4736ef25$0$3898$dbd43001@news.wanadoo.nl>, "Ursus"
> > wrote:
> >
> >>> I have a server directory that recives patholology reports (i .pit format)
> >>
> >> Without knowing how pit is formatted your questions can't really be
> >> answered.
> >>
> >> If it is anything like plain text, tab- or comma delimited. you could
> >> import a list of all files in a certain directory into a textfield
> >> from there it wouldn't be to difficult to set-up an import into an empty
> >> table, do some massaging en redirect the contents to the main file.
> >>
> >> But without further details from your incoming pit and your target fmp
> >> file it is difficult to go into all this deeper.
> >
> > I'd also question the creation of separate FileMaker files - that's
> > going to quickly bloat the system. It would probably be better to have
> > separate RECORDS in one database.
>
> Hi Harry,
>
> Pit files are just text files, they have line numbers 0001-999, many of
> the line numbers can be repeated
>
> the separate FM files are a temporary construct. Each pit file can open
> into FM, creating an FM record for each text row in the pit, then the FM
> replicate is saved, and used as a data source into a single
> Importer/Parser FM file. The converted PIT.fm7 is then redundant

Why not simply import the .pit text files into the "Importer/Parser"
database directly and skip having the separate FileMaker PIT.fm7
files?? Unless these files are being used for something else, that
entire step in the process seems to be redundant.

- Import .pit files into a "Paser" database

- Parse new records

- Import / transfer needed data to the main database



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: open, convert, then import list of files

>>
>> the separate FM files are a temporary construct. Each pit file can open
>> into FM, creating an FM record for each text row in the pit, then the FM
>> replicate is saved, and used as a data source into a single
>> Importer/Parser FM file. The converted PIT.fm7 is then redundant
>
> Why not simply import the .pit text files into the "Importer/Parser"
> database directly and skip having the separate FileMaker PIT.fm7
> files?? Unless these files are being used for something else, that
> entire step in the process seems to be redundant.
>
> - Import .pit files into a "Paser" database
>
> - Parse new records
>
> - Import / transfer needed data to the main database

When FM opens a PIT file, it forces saving the converted file as a new
FM file; the same as opening an Excel worksheet. Or is there something I
have been missing all this time (highly possible)?

Have taken another tack. A MySQL db has been constructed using Navicat,
the pit imported directly into that, so the Parser can
import/interrogate that

Re: open, convert, then import list of files

Ursus wrote:

>
> There are free plug-ins like the File Toolbox (Mac os9 and windows)
> http://www.geocities.com/SiliconValley/Network/9327/plPLUGs. html

have downloaded will check it out



>
> Depending on what you want to be done as a pre-process I would not look into
> VBA. First The VB Object for filemaker is very limited, to the point of
> being an invalid set. Integrating is hell. You could also use any good
> text-editor, just to remove line-feeds, returns as a preprocess. Then
> filemaker will treat it as one large chunk of data. Also PowerGrep (windows)
> or any other flavor of Grep would be a good choice.

Would still be a deconstruct/reconstruct process; and require processing
of each PIT. The PIT opens in TextWrangler btw. Stay away from VBA!

>
> Without plugins I think I would create a dummy table, with one field only.
> Import the pit file. Let a script loop thorugh the records and collect all
> the date needed in a variable then dump the contents in the file you are
> going to work with.
>


Import as Records into a field works, specify a single PIT file, Import
(text files) can use Specify All Available, and each line becomes a
record. That's fine. This can't be done for a folder, as there are no
specify options for the import, and FM returns 'can't find file of the
type, message. So each PIT has to be specified, i.e manually imported.

A colleague built a solution yesterday, using MySQL to import all the
PIT files, so my FM Parser can interrogate that directly.

I also almost have a scripted Terminal approach to building a file list
that would return the file list from a directory; so that would replace
the need for the Troi plugin. Now only necessary on my dev Mac really.





>> The object of the excercise is to parse out the numeric value for each
>> chemistry result (Bicarb 25; TProtein, 71...) and push that into a main
>> database that records all patient info (operation, visits, operation data,
>> tests...).
>>
>
> Once the data is in a fmp file I think this filtering could easily be set
> up. The number of result types will be limited, even if there a several
> hunderds, it would be easy enough the post-process inside fmp.
>
>

Yes, I have all that aspect of it working. It had been the getting all
the source data in, in a fully automated way, that has been the problem.


regards


Chris

Re: open, convert, then import list of files

In article , Chris Brown
wrote:

> >>
> >> the separate FM files are a temporary construct. Each pit file can open
> >> into FM, creating an FM record for each text row in the pit, then the FM
> >> replicate is saved, and used as a data source into a single
> >> Importer/Parser FM file. The converted PIT.fm7 is then redundant
> >
> > Why not simply import the .pit text files into the "Importer/Parser"
> > database directly and skip having the separate FileMaker PIT.fm7
> > files?? Unless these files are being used for something else, that
> > entire step in the process seems to be redundant.
> >
> > - Import .pit files into a "Paser" database
> >
> > - Parse new records
> >
> > - Import / transfer needed data to the main database
>
> When FM opens a PIT file, it forces saving the converted file as a new
> FM file; the same as opening an Excel worksheet. Or is there something I
> have been missing all this time (highly possible)?
>
> Have taken another tack. A MySQL db has been constructed using Navicat,
> the pit imported directly into that, so the Parser can
> import/interrogate that

Rather than Opening the .pit files directly in FileMaker, you should
use File -> Import in the Parser database to read in the .pit file's
data.

In fact, with the newer versions of FileMaker you could do all of that
from within one database file by having a separate table to import the
..pit files into, parse them and then pass the data to the main table.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: open, convert, then import list of files

Helpful Harry wrote:

>
> Rather than Opening the .pit files directly in FileMaker, you should
> use File -> Import in the Parser database to read in the .pit file's
> data.
>
> In fact, with the newer versions of FileMaker you could do all of that
> from within one database file by having a separate table to import the
> .pit files into, parse them and then pass the data to the main table.
>


yes, but that is only one file at a time, it will not import a folder of
pit files, where as a folder of FM files can be imported.

However, I can now loop import one at a time, the pit files, as I have
a simple shell script using ls | pbcopy to copy a directory file list
to the clipboard, and paste it into a global.


Perform AppleScript [ Native AppleScript: do shell script "ls
/users/me/desktop/pit_source | pbcopy" ]
Paste [ AAA::g_List ]
[ Select ]



the final script creates th file list and imports the converted FM files:

import - files using shell list

Perform AppleScript [ Native AppleScript: do shell script "ls
/users/me/desktop/pit_source | pbcopy" ]
Paste [ AAA::g_List ]
[ Select ]
Set Variable [ $_n; Value:1 ]
Loop
Set Variable [ $_path; Value:"file:PIT_source/" ]
Set Variable [ $_file; Value:GetValue ( AAA::g_List ; $_N ) ]
Set Variable [ $$_IMP; Value:$_Path & $_File ]
Import Records [ Source: “$$_IMP”; Target: “AAA”; Method: Add; Character
Set: “Mac Roman”; Field Mapping: Source field 1 import to AAA::PIT ] [
No dialog ]
Set Variable [ $_n; Value:$_n +1 ]
Exit Loop If [ $_n > ValueCount ( AAA::g_List ) ]
End Loop


Each pit text line imports as a new record (need to add a filename
field and replace after each import)
And this method also works for pit. So importing a directory of files
can be fully automated...




Chris