Four Js Customer Forum

I want to know if there is some way of copying data from a spreadsheet and pasting it en bloc into a genero screen array. When I try it it only pastes a single cell. Backend program is a simple INPUT ARRAY without any BEFORE FIELD, AFTER FIELD, BEFORE ROW, AFTER ROW, &c.

You'll need to state what client, what version etc you are using.

I am pretty sure that 10+ years ago before I joined 4Js, what you described work, as we looked to use this as a means for Accountants to determine their budgets etc in Excel, and then copy and paste their account codes and values into an INPUT ARRAY,  or similarly for data migration purposes.  The key being that the INPUT ARRAY has to be vanilla with no code to do NEXT FIELDs etc.  Any validation can only be in the AFTER INPUT.

A quick test using GDC on my Mac suggests that it pastes into the next field correctly ie handles the ASCII(9), but doesn't do the next row ASCII(13)correctly.  I'll have to have a longer play on some old versions and see if my memory is correct.  Using GBC it looks like ASCII(9) is pasted as a literal tab

You should also note that with GDC you could use the cbget front-call to read the clipboard buffer and base.StringTokenizer using ASCII(13) and ASCII(9) e.g.

DEFINE s STRING
DEFINE tok1, tok2 base.StringTokenizer
DEFINE line STRING

    CALL ui.Interface.frontCall("standard","cbget",[], s)
    LET tok1 = base.StringTokenizer.createEXt(s,ASCII(13), NULL, TRUE)
    WHILE tok1.hasMoreTokens()
        DISPLAY "New Row ..."
        LET line = tok1.nextToken()
        LET tok2 = base.StringTokenizer.createExt(line,ASCII(9), NULL, TRUE)
        WHILE tok2.hasMoreTokens()
            DISPLAY tok2.nextToken()
        END WHILE
    END WHILE

so you could get something that will work if you were prepared to add an ON ACTION pastespecial, and explicitly parse the clipboard into the array variable



Reuben

Quote from: Reuben B. on June 27, 2018, 12:47:56 PM
You'll need to state what client, what version etc you are using.

Hi Reuben, thanks for the reply. Unfortunately we're using the HTML client & Genero version 2.50. It seems the cbget option doesn't work with the HTML client?

Quote from: Mark W. on June 27, 2018, 01:35:53 PM

Quote from: Reuben B. on June 27, 2018, 12:47:56 PM
You'll need to state what client, what version etc you are using.

Hi Reuben, thanks for the reply. Unfortunately we're using the HTML client & Genero version 2.50. It seems the cbget option doesn't work with the HTML client?

Correct, as documented http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_frontcalls_standard.html.  Historically Web Browsers don't allow javascript to access the clipboard.

Problem solved by doing the following:--

1. Paste data from spreadsheet into a text box.
2. Store data from text box in a string variable.
3. Use string tokenizers to separate cells and lines (ASCII (9) between cells; new line ASCII (10)).
4. Store data in program array.

Quote from: Reuben B. on June 27, 2018, 12:47:56 PM
You'll need to state what client, what version etc you are using.

I am pretty sure that 10+ years ago before I joined 4Js, what you described work, as we looked to use this as a means for Accountants to determine their budgets etc in Excel, and then copy and paste their account codes and values into an INPUT ARRAY,  or similarly for data migration purposes.  The key being that the INPUT ARRAY has to be vanilla with no code to do NEXT FIELDs etc.  Any validation can only be in the AFTER INPUT.

A quick test using GDC on my Mac suggests that it pastes into the next field correctly ie handles the ASCII(9), but doesn't do the next row ASCII(13)correctly.  I'll have to have a longer play on some old versions and see if my memory is correct.  Using GBC it looks like ASCII(9) is pasted as a literal tab

You should also note that with GDC you could use the cbget front-call to read the clipboard buffer and base.StringTokenizer using ASCII(13) and ASCII(9) e.g.

Code (genero) Select Expand

DEFINE s STRING
DEFINE tok1, tok2 base.StringTokenizer
DEFINE line STRING

   CALL ui.Interface.frontCall("standard","cbget",[], s)
   LET tok1 = base.StringTokenizer.createEXt(s,ASCII(13), NULL, TRUE)
   WHILE tok1.hasMoreTokens()
       DISPLAY "New Row ..."
       LET line = tok1.nextToken()
       LET tok2 = base.StringTokenizer.createExt(line,ASCII(9), NULL, TRUE)
       WHILE tok2.hasMoreTokens()
           DISPLAY tok2.nextToken()
       END WHILE
   END WHILE

so you could get something that will work if you were prepared to add an ON ACTION pastespecial, and explicitly parse the clipboard into the array variable



Reuben

Re-reading this, reason the above example didn't work as intended was I was using ASCII(13) and not ASCII(10).  Replace ASCII(13) with ASCII(10) in the example

Readers should also note that technique using StringTokenizer does not handle case where the delimiter is inside a quoted string and you want to ignore it the delimiter in that case.

Quote from: Reuben B. on November 19, 2019, 04:17:50 AM

Quote from: Reuben B. on June 27, 2018, 12:47:56 PM
You'll need to state what client, what version etc you are using.

I am pretty sure that 10+ years ago before I joined 4Js, what you described work, as we looked to use this as a means for Accountants to determine their budgets etc in Excel, and then copy and paste their account codes and values into an INPUT ARRAY,  or similarly for data migration purposes.  The key being that the INPUT ARRAY has to be vanilla with no code to do NEXT FIELDs etc.  Any validation can only be in the AFTER INPUT.

A quick test using GDC on my Mac suggests that it pastes into the next field correctly ie handles the ASCII(9), but doesn't do the next row ASCII(13)correctly.  I'll have to have a longer play on some old versions and see if my memory is correct.  Using GBC it looks like ASCII(9) is pasted as a literal tab

You should also note that with GDC you could use the cbget front-call to read the clipboard buffer and base.StringTokenizer using ASCII(13) and ASCII(9) e.g.

Code (genero) Select Expand

DEFINE s STRING
DEFINE tok1, tok2 base.StringTokenizer
DEFINE line STRING

   CALL ui.Interface.frontCall("standard","cbget",[], s)
   LET tok1 = base.StringTokenizer.createEXt(s,ASCII(13), NULL, TRUE)
   WHILE tok1.hasMoreTokens()
       DISPLAY "New Row ..."
       LET line = tok1.nextToken()
       LET tok2 = base.StringTokenizer.createExt(line,ASCII(9), NULL, TRUE)
       WHILE tok2.hasMoreTokens()
           DISPLAY tok2.nextToken()
       END WHILE
   END WHILE

so you could get something that will work if you were prepared to add an ON ACTION pastespecial, and explicitly parse the clipboard into the array variable



Reuben

Re-reading this, reason the above example didn't work as intended was I was using ASCII(13) and not ASCII(10).  Replace ASCII(13) with ASCII(10) in the example

Readers should also note that technique using StringTokenizer does not handle case where the delimiter is inside a quoted string and you want to ignore it the delimiter in that case.

Disregard my last comment, my original example was OK.  The buffer content uses  ASCII(13), ASCII(10) as the row delimiter.  Either ASCII(13) or ASCII(10) would effectively delimit the Excel row, although both equally suffer if the character used as delimiter is inside a quoted string in the Excel cell (we can't instruct the tokenizer to ignore this).  As this is more likely to be an ASCII(10), then using ASCII(13) as per my original comment is probably the better option.