1) PDFs in database 2) copy/paste from Excel

Panorama X
1

Hi everyone !

I am trying to figure out if Panorama X is suitable (or will be when it comes out of beta) for my project.

Two boxes that I need checked are :

  • the ability to put PDFs (a lot of them !) in the database (either in the database file, or in an external folder if there is a size limitation) and view them in a form.
    - being able to perform a search within these pdfs would be a plus (I imagine importing only PDFs with an integrated index would help).
  • the ability to copy cells (one cell, multiple cells, one or several lines or colums) directly from excel and very simply paste them in the table.

What do you guys think ?

Thank you for your answers.

Colin

2

Panorama displays images (including PDF) directly from the disk, they aren’t store inside the database itself (no reason for them to take up space in memory all the time). Only the name of the image is stored in the database, or, if the image is in a different folder than the database, the path and the name (in which case the image can be anywhere on the drive).

In the case of PDF files, only the first page is displayed in the form. We will eventually be adding a PDF Display object that will allow multi-page PDF files to be viewed directly in a form. In the meantime, you can set up a button with a one line program that will open the currently viewed PDF in Preview.

Panorama doesn’t know anything about the content of PDF files, so it cannot search the contents. However, Panorama can easily incorporate the results of any UNIX shell tool installed on your system. I seem to recall that there is at least one shell tool available that will extract the text from a PDF file. Unfortunately I don’t have a link at the moment. With a tool like that, you could automatically extract the text and store it in the database so it would be searchable.

You can copy one cell from Excel into Panorama, or you can export Excel data as a tab or comma delimited text file and bring it into Panorama, but there is currently no way to directly transfer multiple cells from Excel and paste them into Panorama.

3

You could certainly open PDFs with a procedure if you know the path and file name. I have done that several times and I do not see any limit on the number of PDFs that you could link to. I would not try to open the PDF in a form; at least I do not see a reason to try that.
I suspect you could use AppleScript to initiate a search in the PDF. I am not proficient in AppleScript, but others on this list are.
I am fairly sure that you could copy data from Excel and paste it into a database file. You would have to do something to ensure each piece of data goes into the right field and row. This could be one cell, one row, one column, or a rectangle of data, but the procedure will get more complicated the more options you want to handle.

4

Jim, I just dragged a multi-page pdf file to a WebBrowserObject with mode set to Literal Text and nothing in the Formula pane and was able to scroll through all the pages. Did I do something wrong or did I do something right?

5

Duh – you did something right. I forgot about WebBrowserObject. In fact I don’t think I’ve ever tried that.

Actually, I don’t understand what you did. Nothing in the formula pane? Not the url for the PDF file? It sounds like what you did isn’t useful for Colin’s application – he needs a reference to the PDF file in the database. But I’ll bet that can be done with the formula pane.

6

As a quick test I just dragged the file directly from the Finder into the WebBrowserObject. I just tried dragging it into the Formula pane and it worked just the same.

7

Can you share the one line program that will open the pdf in Preview please

8

Whether it actually opens in Preview will depend on whether Preview is the default application for PDF files on your machine. The line would be

openanything path

where path would be the field, variable, or formula that is being used to display it in the image display object.

9

Alternately you can use openwith to force Preview to be the application.

openwith “Preview”, path

10

Thanks, that worked perfectly. I’m a newby to this and very much appreciate the help.

11

Reading this re-opened thread, I tried to do the same what you described a year ago.

  • When I drag a multipage PDF from its location into the Web Browser object, it indeed will display, and I can scroll through all 5 pages. There is no URL entry in the Formula tab of the web browser object’s properties panel.
  • When I drag the same file into the formula pane of the Web Browser object, I get an error “Web browser URL is malformed.”
  • When I edit the object properties of the first web browser object (with the well-displaying directly dragged PDF) from “Literal Text” to “Formula”, it shows the same error message.
  • I learned that I have to escape the space characters in my file path with %20, but I still get the same error. (Same result with the unixpath function.)
  • I get the same error, when I add a “file://” prefix.
12

I noticed that once a error gets displayed in the Web Browser it is hard to get rid of it unless you close the form and then reopen it. I have been able to display local pdf files using both the Formula setting with quotes and the Literal Text setting without quotes. I had a pdf able to open all by itself but mostly it opens using Adobe Reader and I;m not sure why.

13

I continue my tests, and it seems the Web Browser object does not work well with umlauts like “ö” in the file path.

I got it to display a PDF perfectly with a path (without spaces or umlauts) like

"/Users/usershortname/Sites/RKK/Graphik/pdfs/Vorstand170901.pdf"

but it fails with

"/Users/usershortname/Documents/Mactreff%20Köln%20Infos/MacFive%20301.pdf"

I don’t need to escape space characters in the file name. This works:

"/Users/usershortname/Sites/RKK/Graphik/pdfs/Brauhaus Sion 20150413.pdf"

But the web browser object does not seem to like spaces in the folder path at all:

"/Users/usershortname/Documents/∂ iWork/∂ Pages/Kontrapunkt Konzerte/2017/Salut Salon/SaSa_Liebe_2017_Schmetterling_A1_blanko_ANSICHT.PDF"

does not work, and it does not work either when I escape the spaces in the folder names with “%20”.

14

Actually the space is not the problem in your folder path. The problem is the ∂ character which needs percent encoding when used in a url. On a site that percent encodes text I see the ∂ character is encoded as %E2%88%82. The site I found is found at: https://www.url-encode-decode.com/

15

Investigating the Panorama X percentescape( function, it will percent encode the ö character but chokes on the ∂ character (produces no output and no error message).

16

Thanks, Gary!

Yes, I only need to replace the “∂” character with “%E2%88%82”. Spaces are no problem. And with the help of the referenced website I found that “Köln” has to be coded as “K%C3%B6ln”.

17

Oh my, this has opened a whole can of worms! I did some research and put it into this bug report.

18

Found a little perl script that seems to work for utf-8 higher value characters (at least the few that I checked).

local output output=""
perl |||use URI::Escape;

$str  = uri_escape("Ф");
print "$str";
|||,output
message output

Of course to un-encode you would use url_unescape("%D0%A4")

19

I believe with these functions it could all be done in Panorama X

Of course it’s pretty cool that you can do it with Perl inside Panorama, and perhaps that is easier :slight_smile:

20

Here is what I came up with using Pan X functions:

local urlChar
urlChar=radixstr("hex",texttobinary("ﭏ"))
urlChar="%"+urlChar[1,2]+"%"+urlChar[3,4]+sandwich("%",urlChar[5,6],"")
message urlChar  // encoded character
message hextobinary(replace("%EF%AD%8F","%",""))  // decoded character

The sandwhich( function is added because the highest characters have 3 % encoded pairs rather than 2.