Importing a Text File

Panorama X
1

Hello Everybody,

Sigh.

Pan X seems to fight me at every turn. Don’t know why it should do so because I love its ancestor, Panorama 6.

Here’s my problem. I wrote a macro to import a text file. The macro uses the fileload( command, with the path and file name within parentheses. The text file to import was generated by Panorama 6 using the Export menu. It looks OK with a text editor program. But when I run the macro, I get this error message

The file “myfile.txt” could not be opened because there is no such file.

I made sure there was no typo in the path and file name. I even imported the text file manually (File-Import) with the macro recorder to see what its fileload command looked like. Exactly the same as mine. I even copied and pasted the fileload( command into my macro to eliminate any chance of a misprint. But Pan X still insists there is no such file.

I noted that someone else in Nov '17 had similar trouble with a csv file. He also insisted that his file existed, but was unrecognized by Pan X.

Jim, you responded to his topic saying you would like to track down the source of the error. Were you successful in doing so? I didn’t see any follow-up.

Regards,
Vic

2

Text import is working well for me. I am using the importtext statement in combination with the fileload( function: importtext statement

3

You haven’t given much detail to go on. What does your code actually look like?

In any case, if Panorama says there is no such file, I am confident that there is an error in the path or filename. Since we don’t know what your code does, perhaps the problem is not even in the section of the code you think it is in.

I didn’t see any follow-up.

From memory, that person didn’t follow up with any additional details. Sometimes people figure out their problem and then don’t communicate further.

4

You might try FolderContents( to see the contents of the folder you are pointing at.

5

Jim, in an effort to help you with Pan X as much as I can, here are some further details. Maybe I can help prevent others from running into this problem.

The code I wrote, which gives the error message, is:

importtext fileload(“/Users/vicdifranco/Data/Financial/
Investment Folder/Portfolio.txt”),“ExistingData”,“Replace”,
“AddFields”,“No”,“ReArrange”,“”+tab()+import()

When the above code is activated, the Error Wizard window appears with “fileload(: The file “Portfolio.txt” couldn’t be opened because there is no such file”

Now here is the code the procedure recorder wrote:

importtext arrayrange(lftocr(binarytotext(fileload(“/Users/vicdifranco/Data/Financial/Investment Folder/Portfolio.txt”))),2,-1,cr()),“ExistingData”,“Replace”,“AddFields”,“No”,“ReArrange”,“”+tab()+import()

The FIRST time I ran this code, the file data was successfully imported into the database and replaced all existing data as it should. Note the fileload parameter is identical to my version. BUT, and this is really weird, when I ran the SAME procedure a second time (and third, and fourth, etc.), an EMPTY Error Wizard window opened. I include a copy of the error window attached below. The macro did import the text data, but the macro was interrupted by the error window with no error shown.

I generally try to solve things myself rather than burden the forum, but this one is too bizarre for me. By the way, the code I wrote (the first one shown above) is successful in other Pan X macros for other database imports of text files. That’s why I was so sure this would work. The other DBs use the exact same code, with only the path and textfile name different, and everything works as expected.

Untitled
Untitled706×424 10.4 KB

6

What happens if you open the Formula Workshop and try this formula?

filesize("/Users/vicdifranco/Data/Financial/Investment Folder/Portfolio.txt")

Is this in a regular, named, procedure? How did you activate it, thru the Action menu? Or is this “anonymous” associated with a field, or a graphic object?

Which code – the code you wrote, or the recorded code?

If the recorded code works, why did you need to copy it into your macro? That makes me wonder if your macro has something else going on that you aren’t telling us about.

7

1- The function

filesize("/Users/vicdifranco/Data/Financial/Investment Folder/Portfolio.txt")

returns 321082 in the Formula Workshop window.

2 - The macro runs from the Action menu.

3 - I was referring to the recorded code (the second one in my note). The first code - the one I wrote - never runs completely. It generates the Error Window indicating Pan X cannot find the file.

4 - I only copied and pasted the auto-recorded fileload( function to make sure I hadn’t entered a typo in my code. I wanted to make sure the fileload parameter which worked was the exact same as the one that didn’t work. And I can assure you there is nothing else going on in my macro. The macro consists of ONLY that import command, followed by a RTN! Here is the Error Window. I run either of the two commands by commenting-out the other.

I told you it was weird.

Regards,
Vic

Untitled
Untitled.png705×457 37.9 KB

8

I looked into this a bit further and found that the error message is being generated by Apple’s code (specifically it is being returned by the NSData dataWithContentsOfURL: method). The filesize( function uses a different apple method.

I’ll bet if you try this formula in the Formula Workshop, you’ll get the same error.

length(fileload("/Users/vicdifranco/Data/Financial/Investment Folder/Portfolio.txt"))

At this point I am thinking that there is some sort of permission issue, and that for some reason you do not have permission to read the file.

9

Hello Jim,

Something strange is happening for sure, but it’s not what you’ve come up with yet.

Below are some screen shots. The length( function does not generate an error, but gives the same result as the filesize( function. And the sharing and permissions window shows that I have full read/write Privilege.

I am still running OS 10.11.6. Is that important? I am not experiencing any odd or unexplained behavior in any other applications (Excel, Word, Omnigraffle, Pan 6, etc.) So it’s not as if my Mac is suffering from a near breakdown. By the way, I opened the Portfolio.txt file in Excel. It looks fine, and has the same number of rows as there are records in the original Pan 6 DB that generated the text file.

Regards,
Vic

image
image.png749×227 46.7 KB

image
image.jpg667×800 101 KB

10

Ok, you have access then.

FYI, just looking at that window isn’t enough to prove you have full read/write Privilege. The restriction could be on an enclosing folder, rather than the file itself. Or, more unusually, there could be an ACL (Access Control List) that is causing the restriction. I’m not sure how to check that other than using the terminal.

However, that is not the problem, since the fileload( function is working in the Formula Workshop.

Perhaps try deleting the procedure completely, and then re-create it from scratch? Maybe first try this procedure

let idata = fileload("/Users/vicdifranco/Data/Financial/Investment Folder/Portfolio.txt")
message length(idata)

If that works, then try this:

let idata = fileload("/Users/vicdifranco/Data/Financial/Investment Folder/Portfolio.txt")
importtext idata,“ExistingData”,“Replace”,“AddFields”,“No”,“ReArrange”,""+tab()+import()
11

Hello Jim,

Thanks so much for helping me with this problem. If it isn’t solved, I cannot ever update my Pan X Portfolio.pandb file. So it is important.

I entered the above command into a new macro, and it returned a message 321082. So that works.

When I entered this into a new macro, the macro ended with a blank Error Wizard window. The same blank window I indicated in an earlier post. So still no cigar.

By the way, just to eliminate as many possibilities as I can, I ran Apple’s Disk Utility program to make sure my hard drive was OK. No change to the observed behavior.

Can it be that I’m the only one who has run into this problem? And as I wrote earlier, I have used the exact same command (with only the fileload( path parameter changed) in other Pan X DBs, and they work perfectly. So it’s not a Pan X coding issue. Or if it is, it is a very subtle one.

Regards,
Vic

12

In the second line of the code sample there are mixed curly quotes. Try this:

importtext idata,"ExistingData","Replace","AddFields","No","ReArrange",""+tab()+import()
13

Hello KJM,

That made no difference.

14

No Jim. That doesn’t work either.

15

I am wondering about the “Rearrange” part of your code, because the fileload part seems to be proven to work.

If you want to fill the import data not in the leftmost column, but starting with the second column, wouldn’t it be enough to write:

tab()+import()

instead of

""+tab()+import()

Maybe the use of importcell() in the rearrange formula would work better? (I don’t know how many columns you have to import.)

Or you could consider rearranging the columns in your database. Then you would not need the “Rearrange” option during the import at all.

16

That indicates that the file is being found and loaded. So in spite of any words in the error, the problem is either in the data itself or in the ability of importtext and/or your options to deal with it.

What if you try:
let idata = fileload(“/Users/vicdifranco/Data/Financial/Investment Folder/Portfolio.txt”)
Clipboard = idata

Then you can paste it into TextEdit or something to confirm that it is loading the data and scan for any obvious anomalies. Assuming that it works okay that far, you can start experimenting with just one option at a time to see if one or all fail. For instance, simply:
importtext idata,“ExistingData”,“Replace”

You could also try:
newdatabase
importtext fileload(“/Users/vicdifranco/Data/Financial/Investment Folder/Portfolio.txt”)

And see what happens

17

Instead of Clipboard = idata, I would use displaydata(idata), commenting it out as necessary.

18

Hello Everybody,

Thanks to all who responded with suggestions to explain and solve this import issue. Most of the recommendations revolved around checking the status or format of the import text file.

But the text file itself is not, and never was, the issue. The text file was generated by exporting the Pan 6 version of the database; the file can be opened by any text editor; the file opens correctly with Excel; and the file can be imported MANUALLY by Pan X with the menu item “Import”. It is only the macro failure, with the Error Wizard window, that is the problem.

And to top it off, when I run the macro (with its failure), Pan X can no longer quit properly. I must do a Force Quit to exit the program. Something very wrong indeed.

The whole point of this was to upgrade my Pan 6 version of the Portfolio database to Pan X. I have to make changes to many of the Pan 6 procedures to be compatible with Pan X. Once all those changes are made, along with some form changes, I need to import the current data (I will still be working with the Pan 6 version) to bring the file up-to-date. I don’t want to have to do this manually every time.

There is also the issue of confidence. Do I want to rely on a database which gives an unknown and inexplicable error when running a macro which seems to work on other DBs?

Jim, do you have any final suggestions on solving this macro import error. I may have to put Pan X aside for a while.

Best regards,
Vic

19

I didn’t post anything further because the other users posted exactly what I would have posted. I gather you have decided that their suggestions are not relevant, but I believe they are, especially the suggestions by Jim Cook. I wouldn’t have any further suggestions without knowing what the results of trying those suggestions were.

I’d offer for you to send your files to me but presumably this is something which cannot be duplicated due to the specific locations on your computer. But I’m willing to try if you want.

20

One other point that occurred to me, when you use the Import menu item, Panorama is actually still using the fileload( function and importtext statement. Not similar code or related code, it is actually using these exactly. In other words, there is no difference between running this “manually” or via code, it is actually being done with code either way.

Some time ago I asked you why you didn’t simply use the recorded code. You never replied, but I am still wondering. If this works “manually”, then the recorded code must also work, because when it is done “manually”, it is actually recording the code, running it, and then throwing the recording away.