FOPEN() or APPEND FROM 3

[linousa]

How to import data to dbf from text *.DAT file, tried "append from ? sdf", but it is confusing users with it's .txt extension. FOPEN() can't make it to append to dbf.

 

[Mike Lewis]

What is the format of the data in the file?

SDF implies that it contains fixed-length fields. That means that a given field is exactly the same length in every record. So, for example, Field 1 might be 8 characters (in every record), Field 2 might be 4 characters, and so on.

But if the fields are separated by a delimiter (typically a comma), then you need one of the TYPE ... DELIMITED options.

FOPEN() is something quite different. You use FOPEN() as the first step in a routine that reads individual lines or strings of text, and then decides what to do with them. In general, you wouldn't use FOPEN() merely to import a common file format into a DBF.

If you could tell us a little more about the data in your text file, I'm sure we'll be able to help.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[linousa]

SDF format and as I said "append from ? sdf" works perfectly fine, the only problem is that I can't change searched extension from *.txt -> *.dat, I have people selecting wrong files and so on.

 

[Olaf Doschke]

You may use GetFile() to ask the user for a file. Look out for the parameterization, at minimum lcFile = GetFile("dat"). You can examine Empty(lcFile), JustExt(lcFile) and check File(lcFile). Then APPEND FROM (lcFile) TYPE SDF

The choice of any dat file is still not the way to go, you would get a mess, if the dat format does not match, but that's a problem of its own.

Bye, Olaf.

 

[Mike Lewis]

as I said "append from ? sdf" works perfectly fine

Excuse me, but you didn't say that. You said:

tried "append from ? sdf", but it is confusing users with it's .txt extension.

Why should the fact that a file has a .TXT extension confuse the users? What has the extension go to do with the process of importing data from a text file into a table? Why should the users know or care what the extension is?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Freeman]

Using the ? in the command, the APPEND FROM command will always look for TXT files. So don't use that form of the command if you're looking for a different extension.

APPEND FROM GETFILE("DAT") SDF

Even better, store the return value of Getfile() into a memory variable and validate it before passing it to the command:

APPEND FROM (lcMemvar) SDF

 

[Olaf Doschke]

Yes, we think alike, Dan.

You may also CD into the dir of your choice. After Getfile() you may check, whether the user navigated away from the initial directory (the result value of GetFile() contains the full path to the file) and may disallow his choice. You have more control.

Using commands with ? is a hack of the language you can use as developer, I wouldn't ever use such a version of a command for end users.

Bye, Olaf.

 

[Mike Lewis]

Even better, store the return value of Getfile() into a memory variable and validate it before passing it to the command

And this has the additional advantage that you can (and should) trap the user pressing ESC to cancel the import.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Freeman]

Using commands with ? is a hack of the language you can use as developer, I wouldn't ever use such a version of a command for end users.

More specifically, using ? in a command like APPEND FROM (or USE or CD, etc.) is intended for interactive use. It's for *us*. It's not meant for end-users of finished applications.

 

[linousa]

And this has the additional advantage that you can (and should) trap the user pressing ESC to cancel the import.

How do we intercepting button clicks in FoxPro? What code for 'ESC'? Please be more informative, if you could.

More specifically, using ? in a command like APPEND FROM (or USE or CD, etc.) is intended for interactive use. It's for *us*. It's not meant for end-users of finished applications.

Why not? What should we use instead? Please be more specific.

 

[Dan Freeman]

Why not? What should we use instead? Please be more specific.

Because we cannot control it, cannot change its behavior, and cannot intercept errors when the user cancels with no selection made. Making applications that do not contain this sort of in-built error is beginner programming.

 

[Olaf Doschke]

GETFILE( ) returns the name of the file chosen in the Open dialog box or the empty string if the user closes the Open dialog box by pressing ESC, clicking Cancel or the Close button on the Open dialog box.

You can readct to that in whatever way is suitable. Eg not do the APPEND, or reask for a file. Perhaps rather take it as not only cancelling the file choice, but the overall action of importing a DAT file. But it's your choice, what you do, you have control. Isn't that nice? If using APEND FROM ? you don't have that control. And you asked for control. Now you got it.

Bye, Olaf.

 

[JRB-Bldr]

linousa - you seem confused by the good advice you are getting above.

Just to be somewhat more clear here is some sample code:

* --- Allow User to choose file to Import ---
mInputFile = GETFILE("DAT")  && Limit files shown to  [b]".DAT"[/b]  file type (or anything else)
IF !EMPTY(mInputFile)
   * --- User chose fully pathed file name ---
   * < Here You [u]COULD[/u] use FOPEN() to read the first row and ensure that the file content was formatted correctly >
   * < If you do make sure you do the FCLOSE() as well >
   * < [u]But that is not Required[/u] >

   * --- Input file data is in the correct format (or assumed to be correct) and do the Import ---
   SELECT RecipientTable
   APPEND FROM (mInputFile) SDF
ELSE
   * --- User clicked ESC or pressed CANCEL, Do Not Attempt Import ---
   * < do whatever >
ENDIF

Good Luck,
JRB-Bldr

 

[Mike Lewis]

How do we intercepting button clicks in FoxPro? What code for 'ESC'? Please be more informative, if you could.

Very simple. If the user presses ESC from a File Open dialogue, the GEFILE() function will return an empty string. Whenever you call that function, you should test for an empty string, and not carry out the relevant action if it is detected.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[linousa]

Thank you very much everyone, ended up using:

	Try
		filepath=Getfile('dat')
	Catch
		Wait Window 'No data file. Try again'
	Finally
		Clear Events
	Endtry

	If File(filepath)
			gnDataFile = Fopen(filepath,0)
	Else
		Wait Window 'No data file. Try again'
		Return
	Endif

 

[Dan Freeman]

I don't see any way that can be doing what you want.

I fear you woefully misunderstand Try/Catch, both in concept and in behavior.

        Try
		filepath=Getfile('dat')
	Catch
		Wait Window 'No data file. Try again'
	Finally
		Clear Events
	Endtry

The catch will never execute and is a waste of effort. A user pressing ESC or clicking cancel in Getfile() is not an exception. It's the normal course of operation. Catch only catches exceptions.

The Clear Events is also woefully misplaced, I suspect, unless this is an exit point for the application. It will execute EVERY TIME this Try/Catch is encountered, ending your application.

Also note that FOPEN() will never throw an exception, so it won't be trappable by Try/Catch either. Rather, it will return a negative file handle if it fails.

I would more likely do something like this:

filepath=Getfile('dat')
If Empty(filepath)
   Wait Window "No file selected"
   Return
Endif
gnDataFile = Fopen(filepath,0)
If gnDataFile < 0
   Wait Window "File cannot be opened"
   Return
Endif

Try/Catch has no play in this game.

 

[Mike Lewis]

In addition to what Dan said, I would add that 'No data file. Try again' is a poor choice of wording for your message.

If the user exited the dialogue by pressing Cancel or hitting ESC, that indicates that they don't want to perform whatever action the dialogue is prompting for, which in this case is a file import. So the correct response is to do nothing: refrain from doing the import.

If the user selected a non-existent file, a better message would be "File not found" or better "xxxx not found", where xxx is the name of the file. You should then either loop back into the dialogue, or do nothing (in which case the user is free to do whatever they did to invoke the dialogue in the first place).

But the main point is - as Dan indicated - TRY/CATCH/ENDTRY is completely inappropriate because GEFILE() does not throw an error.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

The others have given your approach a good critique - illustrating what is wrong with your approach and why.

I'll just add in a comment about your use of FOPEN()

The filepath=Getfile('dat') already ensures that the file exists or not so you don't need the FOPEN() to test for that.

Then you don't do anything with the file once you have Opened it.

And, from what was said way above, it would be the wrong thing to do if you were already assured that the file content was in the correct format and you merely wanted to Import the data into a FP/VFP data table.

The only reason to use an FOPEN() might be to read the first row of file content and ensure that it was correctly formatted for your subsequent APPEND into the data table.
Even if you did that I don't see where you do an FCLOSE() of the file which is what would follow the content format test (if it was done) prior to the APPEND.

Good Luck,
JRB-Bldr

 

[linousa]

Updated it to:

filepath=Getfile('dat')
If Empty(filepath)
   Wait Window Right(filepath,Len(filepath)-Rat('\',filepath))+" not found"
   Return
else
   Append from (filepath) sdf
Endif

If you have an ideas on how to enhance this code, please feel free to show it in the code, it's just easier to understand what are you trying to say for me, thank you.
P.S.
I know it is not possible to select multiple files in that getfile() dialog, do you know any workaround?

 

[JRB-Bldr]

That is not too far from what I suggested code-wise above.

Regardless - for one thing...

When you have EMPTY(filepath) you do NOT have:

Right(filepath,Len(filepath)-Rat('\',filepath)) <-- that equates to "" (nothing)​

and you do NOT have:

" not found"​

Instead you have File Not Selected (meaning that Cancel or Esc was clicked and therefore Nothing was selected - No PATH and NO FILENAME.

So you might be better off with a different WAIT WINDOW message.
Or if you were REQUIRING the user to make a file selection, you might use a DO WHILE loop - something like the following un-tested code.

cFileSelected = ""

DO WHILE EMPTY(cFileSelected)
   cFileSelected = GETFILE("DAT")

   IF EMPTY(cFileSelected)
      mcMessageTitle = "Some Message Box Title"
      mcMessageText = "You did not select a file";
         + CHR(13);
         + "Do you want to Exit/Quit This Routine?"
      * --- OK Button Only ---
      *nDialogType = 0 + 48 + 0
      *  0 = OK Button Only
      *  48 = Exclamation mark icon
      *  0 = First button is default
      * --- Yes/No Buttons ---
      nDialogType = 4 + 32 + 256
      *  4 = Yes and No buttons
      *  32 = Question mark icon
      *  256 = Second button is default

      nAnswer = MESSAGEBOX(mcMessageText, nDialogType, mcMessageTitle)
      IF nAnswer = 6
         * --- YES - The User Elects To Exit/Quit Without Selecting File ---
         EXIT
      ENDIF
   ELSE  && IF EMPTY(cFileSelected)
     * --- OK to Leave DO WHILE LOOP ---
      EXIT
   ENDIF  && IF EMPTY(cFileSelected)
ENDDO  && DO WHILE EMPTY(cFileSelected)

IF !EMPTY(cFileSelected)
   SELECT RecipientTable
   APPEND FROM (cFileSelected) SDF
ENDIF

Good Luck,
JRB-Bldr