How to add datavalue into database from text file - Forum - OpenEdge Development - Progress Community

How to add datavalue into database from text file

 Forum

How to add datavalue into database from text file

This question is answered

Hello, everyone!

I have the strong problem. I want to add data from file into my created database. But I din't undestand quite normal, how to do it.

I have in database simple table Owner, that consist fields  Owner.Name, Owner.SurName, Owner.Telefon, Owner.E_mail.

And I have simple text file with data (ownerData.txt):

Mike;Park;7655548622;mike.park@gmail.com
Anna;Novak;34554123234;anna.novak@gmail.com

I want to write this value into database, I use code:

DEFINE VARIABLE oname  NO-UNDO LIKE Owner.Name.
DEFINE VARIABLE osurname NO-UNDO LIKE Owner.SurName.
DEFINE VARIABLE otelefon  NO-UNDO LIKE Owner.Telefon.
DEFINE VARIABLE oemail  NO-UNDO LIKE Owner.E_mail.

INPUT FROM ownerData.

FOR EACH Owner:
  IMPORT DELIMITER ";" oname, osurname, otelefon, oemail.
  DISPLAY oname osurname otelefon oemail.
END.

INPUT CLOSE.


But it didn`t work. OpenEdge Developer Studio told, that problem with line in code, where is delimiter.

I don't resolve this problem so long time.

Anyway, how to easy add data value from file into database using procedure and method?

Thank you for you help!

Verified Answer
  • There are a couple of problems I can see, for one, the 'INPUT FROM' doesn't look right, the other issue is that you have commas in your 'IMPORT' statement, which is what the thing is complaining about (notice how the error says 'Unable to understand after -- "DELIMITER ; oname", what that is saying is that something after that statement is confusing it (in this case the comma).

    On a working one that I have, I do

    INPUT FROM VALUE ("filename").

    In your case, that would be

    INPUT FROM VALUE ("ownerData.txt") (you may need to give it the full path if it isn't in your propath).

    Once I have done that, I use a 'REPEAT' statement to loop through every line in the file, and 'IMPORT' them into what I want.

    In your case, you could probably get away with just doing:

    REPEAT:

     IMPORT DELIMITER ";"
       oname
       osurname
       otelefon
       oemail.

     DISPLAY oname osurname otelefon oemail.

    END.

    For an 'Import' statement (and certain other ones), I like to put each variable on its own line, just so I can read things easier.

  • Yeah, you can read it into the DB, it's how I do it for our pricefiles from supplier CSV files.

    Inside the 'repeat' you just need to do a create record statement and then set some field values.

    Once you have created the record, you could 'release' the record and then do a 'find' statement on it (assuming you have enough info to find the record afterwards.

    To find the record afterwards you would have to know what record you just did, which means still having the variables (or at least some of them).

    For example:

    REPEAT:

     CREATE Owner.

     IMPORT DELIMITER ";"

       Owner.Name

       Owner.SurName

       Owner.Telefon

       Owner.E_mail.

     oname = Owner.Name.

     osurname = Owner.SurName.

     RELEASE Owner.

     FIND Owner NO-LOCK WHERE Owner.Name = oname AND Owner.SurName = osurname NO-ERROR.

     IF AVAIL ( Owner ) THEN DISPLAY Owner.

    END.

    Obviously I have made assumptions when it comes to the 'FIND' statement since I don't know how your indexes, etc... are set up.

    This is also a very rough and dirty way, ideally you would have buffers defined so that you aren't using the default table buffer that OpenEdge provides, and you would probably have more error checking (maybe make it a transaction so it all has to be read successfully from the text file or none of it goes in, etc...), but that should give you ideas to help.

All Replies
  • There are a couple of problems I can see, for one, the 'INPUT FROM' doesn't look right, the other issue is that you have commas in your 'IMPORT' statement, which is what the thing is complaining about (notice how the error says 'Unable to understand after -- "DELIMITER ; oname", what that is saying is that something after that statement is confusing it (in this case the comma).

    On a working one that I have, I do

    INPUT FROM VALUE ("filename").

    In your case, that would be

    INPUT FROM VALUE ("ownerData.txt") (you may need to give it the full path if it isn't in your propath).

    Once I have done that, I use a 'REPEAT' statement to loop through every line in the file, and 'IMPORT' them into what I want.

    In your case, you could probably get away with just doing:

    REPEAT:

     IMPORT DELIMITER ";"
       oname
       osurname
       otelefon
       oemail.

     DISPLAY oname osurname otelefon oemail.

    END.

    For an 'Import' statement (and certain other ones), I like to put each variable on its own line, just so I can read things easier.

  • Oh, thanks a lot for your answer! It's works!

    Could you tell me: it's big diference for data value  - I read into database data from simple text file or cvs file?

    How I can control, that it's show (display) data from database, not temporate variable value?

  • Yeah, you can read it into the DB, it's how I do it for our pricefiles from supplier CSV files.

    Inside the 'repeat' you just need to do a create record statement and then set some field values.

    Once you have created the record, you could 'release' the record and then do a 'find' statement on it (assuming you have enough info to find the record afterwards.

    To find the record afterwards you would have to know what record you just did, which means still having the variables (or at least some of them).

    For example:

    REPEAT:

     CREATE Owner.

     IMPORT DELIMITER ";"

       Owner.Name

       Owner.SurName

       Owner.Telefon

       Owner.E_mail.

     oname = Owner.Name.

     osurname = Owner.SurName.

     RELEASE Owner.

     FIND Owner NO-LOCK WHERE Owner.Name = oname AND Owner.SurName = osurname NO-ERROR.

     IF AVAIL ( Owner ) THEN DISPLAY Owner.

    END.

    Obviously I have made assumptions when it comes to the 'FIND' statement since I don't know how your indexes, etc... are set up.

    This is also a very rough and dirty way, ideally you would have buffers defined so that you aren't using the default table buffer that OpenEdge provides, and you would probably have more error checking (maybe make it a transaction so it all has to be read successfully from the text file or none of it goes in, etc...), but that should give you ideas to help.

  • I'm so sory for my silly question - I just start to study Progress and have no many best practic examples for my target. I created code follow your instructions:

    DEFINE VARIABLE oname  NO-UNDO LIKE Owner.Name.
    DEFINE VARIABLE osurname NO-UNDO LIKE Owner.SurName.
    DEFINE VARIABLE otelefon  NO-UNDO LIKE Owner.Telefon.
    DEFINE VARIABLE oemail  NO-UNDO LIKE Owner.E_mail.
    
    INPUT FROM VALUE ("ownerData.txt").
    
    REPEAT:
     CREATE Owner.
     IMPORT DELIMITER ";"
       Owner.Name
       Owner.SurName
       Owner.Telefon
       Owner.E_mail.
     oname = Owner.Name.
     osurname = Owner.SurName.
     otelefon = Owner.Telefon.
     oemail = Owner.E_mail.
     RELEASE Owner.
    
     FIND Owner NO-LOCK WHERE Owner.Name = oname AND Owner.SurName = osurname NO-ERROR.
     IF AVAIL ( Owner ) THEN DISPLAY Owner.
    END.

    I hope it's work, but I receive unexpected result. I have sequence (IdOwner_seq),
    that must authomatly create ID record. In my database, that was created in Data Dictionary,
    I aready have the Trigger procedure for my ID sequence:

    TRIGGER PROCEDURE FOR ASSIGN OF Owner.IdPerson.
    ASSIGN Owner.IdPerson = NEXT-VALUE(IdPerson_seq).
    

    End for result database didn't create next record and next sequence value, wrote that Owner is already exist
    with IdPerson 1.

    Why sequence didn't add next value for next record?

       

  • The trigger needs to be for ON CREATE OF <table> instead of ON ASSIGN
     

  • I'm so sorry, but Progress documentation suggest  ASSIGN.  

    From documentation (https://knowledgebase.progress.com/articles/Article/P80479) :

    For example, the following CREATE trigger for the Item table automatically assigns
    a unique item number using NextItemNum sequence:

    TRIGGER PROCEDURE FOR Create OF Item.
    ASSIGN Item.ItemNum = NEXT-VALUE(NextItemNum).

    If I undestand quite normal, in my project trigger should to be:
    TRIGGER PROCEDURE FOR CREATE OF Owner.IdPerson.
    ASSIGN Owner.IdPerson = NEXT-VALUE(IdPerson_seq).

    UPDATE after 15 min:
    I changed Trigger text (how I suggest), but it's not help.



  • You are misreading the article. 
     
    The ABL has both an ASSIGN database trigger (it’s a field level trigger) and an ASSIGN statement.
     
    In the article you link to, the code you want is this...
     
    TRIGGER PROCEDURE FOR Create OF Item.
    ASSIGN Item.ItemNum = NEXT-VALUE(NextItemNum).
     
    The code shown in #2 in the article simply shows how to work with sequences in your application code.
     
    Fields in a table which will get their value when a new record is created should use the TRIGGER PROCEDURE FOR Create OF <Table> code and in the trigger code you simply assign the <Table>.<Field> to NEXT-VALUE(<SequenceName>.
     
    ASSIGN triggers (TRIGGER PROCEDURE FOR Assign of Table.Field) are used to do some processing when application code actually assigns a value to the field.  Putting your code in such a trigger will not work because it won’t be executed (i.e. your ABL code, where you do the create and assign of field values, isn’t assigning anything to the sequence field.
     
    Since you are new, please note that I work in Tech Support at Progress and have about 23 years of experience in the ABL.  So ... in essence ... just trust me. <smile>
     
    Brian
     

  • Dear Brian, I`m so sorry that you think that I offended you with my doubts - of couse you are proffy, what why I asked:

    why you suggest right tech solution and it must work, but I didn't caught working solution - of cause, I do something wrong.  Please, if for you it not hard, explain me my mistake.

    First of all I changed my trigger. It's now look like:

    TRIGGER PROCEDURE FOR CREATE OF Owner.IdPerson.
    ASSIGN Owner.IdPerson = NEXT-VALUE(IdPerson_seq).

    And now I add in my project next code:


    /* Fragment 1 begin */
    DEFINE VARIABLE iCurrentOwnerNum AS INTEGER NO-UNDO.
    ASSIGN
        iCurrentOwnerNum = CURRENT-VALUE(IdPerson_seq).
    FIND FIRST Owner NO-LOCK WHERE Owner.IdPerson = iCurrentOwnerNum NO-ERROR.
    /* Fragment 1 end */

    /* Fragment 2 begin */
    DEFINE VARIABLE oname  NO-UNDO LIKE Owner.Name.
    DEFINE VARIABLE osurname NO-UNDO LIKE Owner.SurName.
    DEFINE VARIABLE otelefon  NO-UNDO LIKE Owner.Telefon.
    DEFINE VARIABLE oemail  NO-UNDO LIKE Owner.E_mail.
    
    INPUT FROM VALUE ("ownerData.txt").
    
    REPEAT:
     CREATE Owner.
     IMPORT DELIMITER ";"
       Owner.Name
       Owner.SurName
       Owner.Telefon
       Owner.E_mail.
     oname = Owner.Name.
     osurname = Owner.SurName.
     otelefon = Owner.Telefon.
     oemail = Owner.E_mail.
     RELEASE Owner.
    
     FIND Owner NO-LOCK WHERE Owner.Name = oname AND Owner.SurName = osurname NO-ERROR.
     IF AVAIL ( Owner ) THEN DISPLAY Owner.
    
    
    END.
    /* Fragment 2 end */
    
    

    Why Fragment 1 work without any problem (I hope - it show info: Procedure complete), but Fragment 1 and Fragment 2
    didn't work correctly together.
    Now I have the result that was before. And I don't understand quite normal - why?



  • Trigger procedURE FOR CREATE OF Owner.IdPerson should be
    Trigger procedure for create of Owner.
    Check manual:https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dvref/trigger-procedure-statement.html



    Sendt fra min iPad

    29. des. 2017 kl. 20:09 skrev miha56155136 <bounce-miha56155136@community.progress.com>:

    <ProgressEmailLogo-png_2D00_150x42x2-png> Update from Progress Community
    <avatar-png_2D00_70x70x2-png>
    miha56155136

    Dear Brian, I`m so sorry that you think that I offended you with my doubts - of couse you are proffy, what why I asked:

    why you suggest right tech solution and it must work, but I didn't caught working solution - of cause, I do something wrong.  Please, if for you it not hard, explain me my mistake.

    First of all I changed my trigger. It's now look like:

    TRIGGER PROCEDURE FOR CREATE OF Owner.IdPerson.
    ASSIGN Owner.IdPerson = NEXT-VALUE(IdPerson_seq).

    And now I add in my project next code:
    /* Fragment 1 begin */
    DEFINE VARIABLE iCurrentOwnerNum AS INTEGER NO-UNDO.
    ASSIGN
        iCurrentOwnerNum = CURRENT-VALUE(IdPerson_seq).
    FIND FIRST Owner NO-LOCK WHERE Owner.IdPerson = iCurrentOwnerNum NO-ERROR.
    /* Fragment 1 end */

    /* Fragment 2 begin */
    DEFINE VARIABLE oname  NO-UNDO LIKE Owner.Name.
    DEFINE VARIABLE osurname NO-UNDO LIKE Owner.SurName.
    DEFINE VARIABLE otelefon  NO-UNDO LIKE Owner.Telefon.
    DEFINE VARIABLE oemail  NO-UNDO LIKE Owner.E_mail.
    
    INPUT FROM VALUE ("ownerData.txt").
    
    REPEAT:
     CREATE Owner.
     IMPORT DELIMITER ";"
       Owner.Name
       Owner.SurName
       Owner.Telefon
       Owner.E_mail.
     oname = Owner.Name.
     osurname = Owner.SurName.
     otelefon = Owner.Telefon.
     oemail = Owner.E_mail.
     RELEASE Owner.
    
     FIND Owner NO-LOCK WHERE Owner.Name = oname AND Owner.SurName = osurname NO-ERROR.
     IF AVAIL ( Owner ) THEN DISPLAY Owner.
    
    
    END.
    /* Fragment 2 end */

    Why Fragment 1 work without any problem (I hope - it show info: Procedure complete), but Fragment 1 and Fragment 2
    didn't work correctly together.
    Now I have the result that was before. And I don't understand quite normal - why?

    View online

     

    You received this notification because you subscribed to the forum.  To unsubscribe from only this thread, go here.

    Flag this post as spam/abuse.

  • Hello everyone!

    I checked my code and tested it and I found out, that programm didn't see sequence file in database directory (WRK) -

    I don't figure out how it may be, file with sequence is present, programm display next value of sequence

    (now in database only 1 record, and sequence varriable iCurrentOwnerNum = CURRENT-VALUE(IdPerson_seq) equil 2),

    and can show name file of sequence, but told - "sorry, I can't find file with sequence". Why?

  • Try the manual:

    You do that in the dictionary tool when setting up the database...

    Sendt fra min iPad

    1. jan. 2018 kl. 18:25 skrev miha56155136 <bounce-miha56155136@community.progress.com>:

    Update from Progress Community
    miha56155136

    Hello everyone!

    I checked my code and tested it and I found out, that programm didn't see sequence file in database directory (WRK) -

    I don't figure out how it may be, file with sequence is present, programm display next value of sequence

    (now in database only 1 record, and sequence varriable iCurrentOwnerNum = CURRENT-VALUE(IdPerson_seq) equil 2),

    and can show name file of sequence, but told - "sorry, I can't find file with sequence". Why?

    <sequence_5F00_didn_2700_t_5F00_find1-png_2D00_320x240-png>

    <sequence_5F00_didn_2700_t_5F00_find3-png_2D00_320x240-png>

    <sequence_5F00_didn_2700_t_5F00_find5-png_2D00_320x240-png>

    <sequence_5F00_didn_2700_t_5F00_find6-png_2D00_320x240-png>

    View online

     

    You received this notification because you subscribed to the forum.  To unsubscribe from only this thread, go here.

    Flag this post as spam/abuse.

  • Hello, dear Goo, unfortunately link that you send me is not suitable - I use ABL, but in link use SQL syntax - I can`t use in my projekt SQL.

      

  • You add the sequence in the database. Ref dictionarytool.. add sequence and then you can use next-value(...)

  • Miha,

    Are you available today?

    Brian