Solution: See code later in thread.

Hello!

I have regularly ASCII - files from medical instruments exported and this would like to import in Sesame file.

Problem or better the luck that the Subform where the data to be imported is that each unique field of the ASCII - file  
already stored.

Thus only two results in the ASCII - file in the Subform to the suitable data record to add.
My desire to experience as is to be solved these tasks.

Each contribution or suggestion is to be thanked you cordially.


Dr. Belhareth

Amor,

I think I know what you're asking for, but I'm not sure. Can you explain a bit further?

Also, is this a subform linking naturally or relationally?

Hello Erika,

The subform linking is relationally.

Thanks.
Dr. Belhareth

Can you clarify what you have and what you need to do with it?

Hello Erika ,

The ASCII - File is Resultate to a Parameter for a Patient with unique OrderNo ="1000"
per Example  HIV -Antikorper with Code HIV for a OrderNo "1000" has a ParameterCode ("1000HIV")
In the Subform the Field ParameterCode ="1000HIV".
In the Subform the OrderNo and the ParameterCode to OrderNo 1000  is already stored.
But the Field Resultate in the Subform is still  empty (Resultate is the that ASCII-File).

In the ASCII - File the OrderNo 1000 and the Code "HIV" exit but not the ParameterCode ="1000HIV"

Each Code and The OrderNo for this Patient is in the ASCII - file somewhere say in the 3th and 5th Position.

I see it´s not easy to describe but i will try again  and again if you like.

Thanks.

Dr. Belhareth

Dr. Belhareth,
Is the following the information you desire ?

Patient number
        1000HIV
          20/09/2007 result01
          20/09/2007 result02
          24/09/2007 result01
          24/09/2007 result02

Harley

Hello Harly,
The Result a desire per example:

Patient No 200
OrderNo  1000
AnalysisCode (unique)  1000HIV (for HIV)
AnalysisCode (unique)  1000HB (for HeptitisB)
AnalysisCode (unique)  1000CHO (for Cholesterin)

Pos. in the                                                      Item
Subform     PatientNo   OrderNo   ItemCode     Unique Code              Date & Results (must come from the ASCII-File)
1.                 200       1000         HIV              1000HIV                      20/09/2007 result01
2.                 200       1000         HB                1000Hb                       21/09/2007 result02
3.                 200       1000         CHO             1000CHO                     21/09/2007 result03
         
The ASCII - File contain the OrderNo and the ItemCode.

Thanks.
Dr. Belhareth

Can we see a line or two from the ascii file?

Hello Erika,

Hier is a ASCII - File :
Each Line is one Patient:
Waht we need to import are the Results
                    OrderNo Item1.   Resultat1   Item2   Resultat2 .. etc
02E   3 0 3   119370   GLU         85.2         TRI       132
03E   4 0 4   119775   HB            2.31
02N   1 1 1   119776   TRI         133.6
03N   2 1 2   119795   GLU         62.2       NAT    45
02N   3 1 3   119796    NAT         133       KAL    12    CHL    9   TRI    11  CHOL   225 CAL  0.83  GLU   2.4  TRI   23 FT3   2 FT4 2  TSH 1.2
03N   4 1 4   119796   CHL          92
02N   5 1 5   119797   NAT          81        KAL    5   CHL    11  TRI   1 27  CHL   262  QUI  2.82  LDH  2.0  LDL   5.6 HCG    320   AFP  21   FT3 2.1 FT4 2.7   TSH   1.1   PSA  19    FPSA 1.2
03N   6 1 6   119797   HIV      0


Thanks.
Dr. Belhareth

Can you depend on everything after the order number being an Item/Result pair?

What is the delimiter being used between the values on a line? Is it a Tab?

Erika,

The pair Order number Item/Result is UNIQUE!
The delimiter between the values it is a Tab.

Thank you.

Dr. Belhareth

Assuming that your relational key is the order number, this is neatly doable with a combination of File I/O, Split, and XResultSet. I'll try to write it up for you tomorrow, unless someone beats me to it.  

Amor,

I created a small app with a Parent form (Orders) and a child form (Results) that matched your design. The parent and child are relationally linked by the OrderNo field.

I created a separate Tools database/form with a button on it for Import Records.

The button has the following code, which works quite well. Let me know if you'd like for me to put the actual app someplace you can get to it.

NOTE: If you do not have SERVER PREINDEX RELATIONAL: OFF in your ini file, you will need to close and reopen your database to see the new child records.

NOTE: This works for relational links. If you want to do this with natural linking, you will also need to use XResultSetReparent().

[code]#include "sbasic_include.sbas"

var vCRS as Int            // Child Result Set
var vFile as Int      // Results file
var vPatient as String      // Current PatientNo
var vOrder as String      // Current OrderNo
var vLine as String      // Current Results line from file
var vVal as String      // Current value from Results line
var vLen as Int            // Length of Results file
var vTemp as Int

FUNCTION ProcessLine() as Int
var vRet as Int
var vCode as String
var vResult as String

     vRet = 0
     // Everything left in line should be code/result pairs.
     // Make a subrecord for each pair.
     While @Len(vLine) > 0
     {
           vCode = Split(vLine, Tab(1))
           vResult = Split(vLine, Tab(1))
           If (vCode <> "") And (vResult <> "")
           {
                 // Make new child record
                 XResultSetCreateNewRecord(vCRS)
                 XResultSetValue(vCRS, "PatientNo", vPatient)
                 XResultSetValue(vCRS, "OrderNo", vOrder)
                 XResultSetValue(vCRS, "ItemCode", vCode)
                 XResultSetValue(vCRS, "UniqueCode", vOrder + vCode)
                 XResultSetValue(vCRS, "DateAndResults", @Str(@ServerDate()) + " " + vResult)
           }
           Else
           {
                 // If info not paired, abandon the line with error
                 vRet = 1
           }
     }
     Return vRet

END FUNCTION

     // Create an empty child record result set by specifying crtieria that no record will match
     vCRS = @XResultSetSearch(@FN, "Orders!Results", SEARCH_MODE_AND,SEARCH_SYNTAX_QA, "!OrderNo==")
     If vCRS > -1
     {
           vFile = FileOpen("amor.txt")
           If vFile > -1
           {
                 FileSeek(vFile, 0)
                 vLen = FileSize(vFile)

                 // Read and process results file line by line
                 While (FilePos(vFile) < vLen)
                 {
                       vOrder = ""
                       vPatient = ""
                       FileReadLn(vFile, vLine)
                       If @Len(vLine) > 0
                       {
                             // Throw away first two values on line
                             vVal = Split(vLine, Tab(1))
                             vVal = Split(vLine, Tab(1))

                             // Get OrderNo from Position 3
                             vVal = Split(vLine, Tab(1))
                             If @Len(vVal) > 0
                             {
                                   vOrder = vVal

                                   // Get matching PatientNo and confirm OrderNo exists
                                   vPatient = @XLookup(@FN, vOrder, "Orders!OrderNo", "PatientNo")
                                   If @Len(vPatient) > 0
                                   {
                                         // Process rest of line into subrecords
                                         vTemp = ProcessLine()
                                         If vTemp <> 0
                                         {
                                               WriteLn("Error while processing Order: " + vOrder)
                                         }
                                   }
                                   Else
                                   {
                                         WriteLn("No matching Order record for: " + vOrder)
                                   }
                             }
                       }
                 }                  

                 FileClose(vFile)
           }
           Else
           {
                 @MsgBox("Error: Failed to open results file.", "", "")
           }
           XResultSetClose(vCRS)
     }      
     Else
     {
           @MsgBox("Error: Failed to open child result set.", "", "")
     }

[/code]

Thank you thank you Erika, you've taught me a lot today!

Please send me the App.
my Mail Adress : amor@belhareth.com

Dr. Belhareth

Amor,

Zip file attached contains the dsr, db and also the text file I used. The startup form has a button. When clicked, the button reads in and processes the text file. You may need to adjust the path to the text file in the SBasic behind the button. Let me know if you need me to explain any of the techniques.

---

Hello Erika!

I would like to thank you very much for snap and applicable example. It functioned excellent. I have today its App. example with the following tape tested however I get the following error message:
378206 KAL 75 NAT 29 PSA 19 CHL 18 NO matching order record for: 378206 KAL 75 NAT 29 PSA 19 CHL 18

The order record 378206 exists already in order form!
I believe it have with the tab problem.
I do not believe that Separator in these ASCII - File is a tabulator (only blanks as Separater!).
I have unfortunately no experience thereby!

Hier is the tested amor.txt file.

Thanks.
Dr.Belhareth

Amor,

Pasting into the Forum changes things like spaces. Can you email the test file to Support so we can see what you actually have?

Amor,

The file you sent does not appear to be a delimited file. It looks more like a fixed width file. The method to parse this is pretty much the same as I gave you, but you can't use Split. You would need to use @Mid instead to pull out each piece. In order to do this accurately, you will need the format specification for these files. This is a document that tells you exactly how many characters each piece of data is given. The people sending you these files would need to provide this to you.

Hello Erika,

I have attempt your import - demo to adapt to an A comma/quote delimited file where the DATA VALUES are separated by A comma and each VALUE is enclosed in quotes. however so far without success.
Please tell me where still errors in this code.

Thanks.

ASCII - file:
"378280","1","76","5","28","6","20","20","24","24","43","43","1.01",".01","0.89"
"378281","1","77","5","13","6","27","27","16","16","40","40","0.35",".35","0.97"
"378282","2","21","5","50","6","45","45","117","117","29","29","23.8","3.8","118"
"373472","13","0.87","21","84","23","204","204","66","66","0.3","0.3","","",""
"373473","1","94","5","29","6","20","20","16","16","0.83",".83","","",""


#include "sbasic_include.sbas"

var vCRS as Int      // Child Result Set
var vFile as Int      // Results file
var vPatient as String      // Current PatientNo
var vOrder as String      // Current OrderNo
var vLine as String      // Current Results line from file
var vVal as String      // Current value from Results line
var vLen as Int      // Length of Results file
var vTemp as Int
var vDelimiter as String


FUNCTION ProcessLine() as Int
var vRet as Int
var vCode as String
var vResult as String
var vDelimiter as String

vDelimiter = @Chr(34) + "," + @Chr(34)
     vRet = 0
     // Everything left in line should be code/result pairs.
     // Make a subrecord for each pair.
     While @Len(vLine) > 0
     {
           vCode = Split(vLine, vDelimiter)
           vResult = Split(vLine, vDelimiter)

               vCode = @Replfir(vCode, @Chr(34), "")
               vCode = @Repllas(vCode, @Chr(34), "")
                   
               vResult = @Replfir(vResult, @Chr(34), "")
               vResult = @Repllas(vResult, @Chr(34), "")              
 
     writeln("Hier ist Code: " + vCode + @NL() + "Hier das Resultat :" + vResult)

     If (vCode <> "") And (vResult <> "")
           {
                 // Make new chid record
                 XResultSetCreateNewRecord(vCRS)
                 XResultSetValue(vCRS, "PatientNo", vPatient)
                 XResultSetValue(vCRS, "OrderNo", vOrder)
                 XResultSetValue(vCRS, "ItemCode", vCode)
                 XResultSetValue(vCRS, "UniqueCode", vOrder + vCode)
                 XResultSetValue(vCRS, "DateAndResults", @Str(@ServerDate()) + " " + vResult)
           }
           Else
           {
                 // If info not paired, abandon the line with error
                 vRet = 1
           }
     }
     Return vRet

END FUNCTION
           
     // Create an empty child record result set by specifying crtieria that no record will match
     vCRS = @XResultSetSearch(@FN, "Orders!Results", SEARCH_MODE_AND,SEARCH_SYNTAX_QA, "!OrderNo==")
     If vCRS > -1
     {
               vDelimiter = @Chr(34) + "," + @Chr(34)
           vFile = FileOpen("amor.txt")
           If vFile > -1
           {
                 FileSeek(vFile, 0)
                 vLen = FileSize(vFile)

                 // Read and process results file line by line
                 While (FilePos(vFile) < vLen)
                 {
                       vOrder = ""
                       vPatient = ""
                       FileReadLn(vFile, vLine)
                                vLine = @Replfir(vLine, @Chr(34), "")
                                vLine = @Repllas(vLine, @Chr(34), "")
                                 
                       If @Len(vLine) > 0
                       {
                             // Throw away first two values on line
                             //vVal = Split(vLine, Tab(1))
                             //vVal = Split(vLine, Tab(1))

                             // Get OrderNo from Position 1
                             vVal = Split(vLine, vDelimiter)
                               vVal = @Replfir(vVal, @Chr(34), "")
                                       vVal = @Repllas(vVal, @Chr(34), "")

                                     If @Len(vVal) > 0
                             {
                                   vOrder = vVal

                                   // Get matching PatientNo and confirm OrderNo exists
                             vPatient = @XLookup(@FN, vOrder, "Orders!OrderNo", "PatientNo")
                                   vPatient = @Replfir(vVal, @Chr(34), "")
                                                                                              vPatient = @Repllas(vVal, @Chr(34), "")

                                               If @Len(vPatient) > 0
                                   {
                                         // Process rest of line into subrecords
                                         vTemp = ProcessLine()
                                         If vTemp <> 0
                                         {
                                         WriteLn("Error while processing Order: " + vOrder)
                                         }
                                   }
                                   Else
                                   {
                                         WriteLn("No matching Order record for: " + vOrder)
                                   }
                             }
                       }
                 }                  

                 FileClose(vFile)
           }
           Else
           {
                 @MsgBox("Error: Failed to open results file.", "", "")
           }
           XResultSetClose(vCRS)
     }      
     Else
     {
           @MsgBox("Error: Failed to open child result set.", "", "")
     }

Amor,

I'm having trouble advising you here. Every time you post the ASCII file, it has a completely different format. Are you producing this file yourself or is it being given to you by someone else?

Hello Erika,

Because our 8 Analyser for various investigations of different companies. Unfortunately those do not have uniform output formats.

Dr. Belhareth

wrote on Sep 27^th, 2007 at 4:37pm:

Hello Erika, Because our 8 Analyser for various investigations of different companies. Unfortunately those do not have uniform output formats.

Ah. OK. Try something like below. I had it replace the "," with ^ so the parsing isn't so difficult and you don't have to keep stripping quotes. I also adjusted the logic in ProcessLine to allow both Code and Result to be blank without an error, since that seems to occur with this file. Although, none of the values in this file look like Codes to me. They all look like results. I can't really tell though...

[code]#include "sbasic_include.sbas"

var vCRS as Int      // Child Result Set
var vFile as Int      // Results file
var vPatient as String      // Current PatientNo
var vOrder as String      // Current OrderNo
var vLine as String      // Current Results line from file
var vVal as String      // Current value from Results line
var vLen as Int      // Length of Results file
var vTemp as Int
var vDelimiter as String


FUNCTION ProcessLine() as Int
var vRet as Int
var vCode as String
var vResult as String

     vRet = 0
     // Everything left in line should be code/result pairs.
     // Make a subrecord for each pair.
     While (@Len(vLine) > 0) And (vRet = 0)
     {
           vCode = Split(vLine, vDelimiter)
           vResult = Split(vLine, vDelimiter)

           If (vCode <> "") Or (vResult <> "")
           {
                 If (vCode <> "") And (vResult <> "")
                 {
                       // Make new chid record
                       XResultSetCreateNewRecord(vCRS)
                       XResultSetValue(vCRS, "PatientNo", vPatient)
                       XResultSetValue(vCRS, "OrderNo", vOrder)
                       XResultSetValue(vCRS, "ItemCode", vCode)
                       XResultSetValue(vCRS, "UniqueCode", vOrder + vCode)
                       XResultSetValue(vCRS, "DateAndResults", @Str(@ServerDate()) + " " + vResult)
                 }
                 Else
                 {
                       // If info not paired, abandon the line with error
                       vRet = 1
                 }
           }
     }
     Return vRet

END FUNCTION
           
    // Create an empty child record result set by specifying crtieria that no record will match
    vCRS = @XResultSetSearch(@FN, "Orders!Results", SEARCH_MODE_AND,SEARCH_SYNTAX_QA, "!OrderNo==")
    If vCRS > -1
    {
          vDelimiter = "^"
          vFile = FileOpen("amor.txt")
          If vFile > -1
          {
                FileSeek(vFile, 0)
                vLen = FileSize(vFile)

                // Read and process results file line by line
                While (FilePos(vFile) < vLen)
                {
                      vOrder = ""
                      vPatient = ""
                      FileReadLn(vFile, vLine)
                      vLine = @Replfir(vLine, @Chr(34), "")
                      vLine = @Repllas(vLine, @Chr(34), "")
                 vLine = @Replace(vLine, @Chr(34) + "," + @Chr(34), "^")
                               
                      If @Len(vLine) > 0
                      {
                           // Get OrderNo from Position 1
                            vVal = Split(vLine, vDelimiter)
                           If @Len(vVal) > 0
                            {
                                  vOrder = vVal

                                  // Get matching PatientNo and confirm OrderNo exists
                                 vPatient = @XLookup(@FN, vOrder, "Orders!OrderNo", "PatientNo")
                                 If @Len(vPatient) > 0
                                  {
                                        // Process rest of line into subrecords
                                        vTemp = ProcessLine()
                                        If vTemp <> 0
                                        {
                                        WriteLn("Error while processing Order: " + vOrder)
                                        }
                                  }
                                  Else
                                  {
                                        WriteLn("No matching Order record for: " + vOrder)
                                  }
                            }
                      }
                }                  

                FileClose(vFile)
          }
          Else
          {
                @MsgBox("Error: Failed to open results file.", "", "")
          }
          XResultSetClose(vCRS)
    }      
    Else
    {
          @MsgBox("Error: Failed to open child result set.", "", "")
    }
[/code]