Need to extract only the filename to a variable not the extension of the file

[titoneon]

Hi,
I will be saving different excel file versions on a folder, but i need to pass to a variable just the filename and not the filename extension.
the file could as an example could be:
118459.xls
or
8963.xlsx
or
whatever.XXXX
what i need is actually to pass is whatever is just before the ".XLS" or ".XLSX"
any advice very appreciated.
Thanks a lot

 

[Olaf Doschke]

JUSTSTEM() should be what you need.

eg
? Juststem("c:\folder\whatever.XXXX")
output: whatever

Bye, Olaf.

 

[titoneon]

Yes man, that is all i needed, i was trying with Left() and right(), and i know it is another one wher you can specify everytthing you wanted but not the other part, anyway thanks a lot easy, just can believe i could not find it, well very simple, i am new to vfp 9.0 so probably was on 7-9 but not do far as i know in vfp 5.0
Thanks

 

[Olaf Doschke]

Well, I wouldn't have known the term "stem" before I studied the JUSTxxx() family of functions. You can get most any part of a file name with JUSTPATH, JUSTFNAME, JUSTDRIVE, JUSTEXT and JUSTSTEM. I think they were there since VFP6.

Bye, Olaf.

 

[Mike Lewis]

I think they were there since VFP6.

Actually, they were present all the way to FoxPro for DOS, but they were only integrated into the language in 6.0. Before that, they were available in FPATH.PLB (in DOS) or Foxtools.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Olaf, Mike
Thanks for the info, i knew about the JUSTPATH, JUSTFNAME, but i JUSTFNAME, was giging me the whole path and including the filename and extension, i just wanted just to extra the filename, did not realized about "JUSTSTEM" and i did not know either, i just need to admited.

Just another question

I am using the getfile() to select from a folder an excel file but i would like the program to use that function but not to stop to make the user to select the folder and file, what should be the trick to specify a fix folder and pick the file, it is going to be only one file at a time so each tine this code run it should automaticly selelct the folder and the file w/o user interface ?
I know that have to define in variable the path so the getfile() should go there and just sinply select the file but not user interface, unless getfile() it is not the proper funtion for this case ?
Let me give you just some code here to see what i meant.

Local lcFile, lcFolder, lcTemp,lcSdf, loExcel As Excel.Application, lnSheet, lnloop, lcOldName, lcNewName, lProceed
lcFolder = "c:\excelfiles"
lcFile = Getfile("xlsx") && i need this to get the file from the default folder automaticly

If !Empty(m.lcFile) && check if not cancelled

lcTemp = Addbs(Getenv("TEMP"))+Sys(3)+".xls"
lcSdf = JUSTFNAME(m.lcFile) && This just grab the excel path and filename when the Getfile() function is issued
lcSdf = Juststem(lcSdf) && Juststem, just to get the filename w/o the extension
loExcel = Createobject("excel.application")
With loExcel
.DisplayAlerts=.F. && somewhat similar to SET SAFETY OFF of VFP
.Workbooks.Open(m.lcFile)
lnSheet = Int(Val(Inputbox("Please select the Target Sheet: 1-"+Transform(.Sheets.Count),"Select Sheet","1")))

**and so on
Thanks a million
Regards

 

[Mike Lewis]

You say you don't want the user to have to select the file; that you don't want a user interface. In that case, GETFILE() is definitely the wrong function. What GETFILE() does is to display a dialogue, which seems not to be what you want.

So how do you expect the program to know which file to use? You must have some criterion for choosing the file (other than asking the user). How do you plan to do that?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Also, regarding the code you posted:

- The line beginning with lcTemp doesn't appear to do anything. It generates a random filename, but doesn't then use that name for anything.

- The next line is redundant. You don't need to use JUSTFNAME() if you subsequently use JUSTSTEM().

- Both the lines beginning with lcSDF don't do anything. You are storing a value in that variable, but not using it in any way.

It could be that those particular lines have nothing to do with your question. If that's so, it might be better to leave them out, so that we can focus on the particular thing you are asking about.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Hi Mike,
in reality, i should allow the user interface cause if the excel file has more than one sheet, i need to allow them to select as many sheet they want to import into a cursor or table, for later to convert it to an SDF, you are correct, i think there is not way to automaticly recognize how many sheets can be in the excel file(normally should be only one) so anyway i will have to allow the users, to select what sheet# in case there are more than one.
here is the whole code

Local lcFile, lcTemp,lcSdf, loExcel As Excel.Application, lnSheet, lnloop, lcOldName, lcNewName, lProceed
lcFile = Getfile("xlsx")
If !Empty(m.lcFile) && check if not cancelled

lcTemp = Addbs(Getenv("TEMP"))+Sys(3)+".xls"
lcSdf = JUSTFNAME(m.lcFile) && This just grab the excel filename when the Getfile() function is issued
lcSdf = Juststem(lcSdf) && Juststem("c:\folder\whatever.XXXX")
loExcel = Createobject("excel.application")
With loExcel
.DisplayAlerts=.F. && somewhat similar to SET SAFETY OFF of VFP
.Workbooks.Open(m.lcFile)
lnSheet = Int(Val(Inputbox("Please select the Target Sheet: 1-"+Transform(.Sheets.Count),"Select Sheet","1")))

If Empty(m.lnSheet) && check if not cancelled
lProceed = .F.
Else
lProceed = .T.
For lnloop = .Sheets.Count To 1 Step -1
If m.lnloop <> m.lnSheet
.Sheets(m.lnloop).Delete
Endif
Next
.ActiveWorkbook.SaveAs(m.lcTemp,39) && Place it in the temp folder
.ActiveWindow.Close(.T.)
.Quit
Endif
Endwith
If m.lProceed && not Cancelled by user?
Import From (m.lcTemp) Type Xls && or XL5

* Rename Field Names to better ones
For lnloop = 1 To Fldcount()
lcOldName = Field(m.lnloop)
lcNewName = Strtran(Alltrim(Transform(&lcOldName)),' ','')
Alter Table Dbf() Rename Column &lcOldName To (m.lcNewName)
Next
Delete
Browse Normal
COPY TO 'f:\BLGFILE\'+m.lcSDF for not delete() type SDF
Close Databases All
* Clean it up, delete temp xl5 file used
Delete File (lcTemp)
Else
Messagebox("Aborted by user!",0+64,"Oppppssss!")
Endif
Else
Messagebox("Aborted by user!",0+64,"Oppppssss!

 

[Olaf Doschke]

You still don't say how an excel file should be picked without user interaction. Do you want to process all xls(x) files in a folder anyway? Then use ADIR with file skeleton *.xls? And loop the result array.

Also, my example of juststem shows, that it takes the stem of a full file name. You don't need to preprocess the GETFILE() filename with justfname before extracting the stem with juststem.

Bye, Olaf.

 

[NasibKalsi]

i think you want to pickup the file from a specific diectory.

You may need to add 3 steps(Statements) to accomplish, what you want ...
1. Save the current Default Path
2. Set new Default Path of your choice
3. Set Default Path back to original

lcOriginalFolder = sys(3) + sys(2003)       && Step 1  
lcFolder = "c:\excelfiles"
set default to (lcFolder)                   && Step 2
lcFile = Getfile("xlsx") && i need this to get the file from the default folder automaticly
set default to (lcOriginalFolder)           && Step 3

Nasib

 

[Mike Lewis]

As Olaf says, you still need to tell us what criterion you use for deciding which file to process. This is a separate issue from choosing the sheet.

Also, there's no point in posting a lot of code unless it's directly relevant to the problem. As far as I can see from the code you posted, it has got nothing to do with the way in which you decide which file to process. (And if you must post large chunks of code, for goodness sake use the [ignore]

 and

[/ignore] tags to format it.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Olaf, Mike,
i will explain the whole thing, sorry i was doing something else at the moment i was posting, i will also insert the code as you mentioned before, i have never used Code icon for that, sorry about it.

Olaf-> Do you want to process all xls(x) files in a folder anyway?
actually each individually Engineer, will create one excel file at a time(and that is each time they have finished working on a job# so they will extract their information from a drawing and create an Excel file, then export it to "C:\excelfiles" and they will save it, in their drive "c:\excelfiles" so i just want them to run the program, each time they have an excel file there, so if we will know where the file is going to be(path), i guess the getfile() won't be the function needed but still i will need to have in a variable the filename(always is going to be different), using "juststem" cause later i will need to use that variable to create a txt file, ok so having this in mind i will need something to go to that path and read the name of the excel file, so the user does not need to pick the file themselves but i just want to leave the option of if the excel file has more than one sheet, either allow them to specify which one, or the program code need know how many sheets are in that excel file, i don't know how to code, how many sheets could be in the excel file, unless the user specify as in the code exposed, i know i need some changes in this code, so that is what i am looking for, any suggestion is welcome, if still i don't explain myself correctly please let me know.

 

[Mike Lewis]

I'm sorry, Ernesto, but this is really getting quite muddled.

Can we separate out the question of picking the file from the issue of multiple sheets. In fact, for the moment let's forget sheets and Excel and the engineers and their drawings and all that stuff. Let's focus on picking the required file from the required directory.

If I've understood it right, you know the path to the directory (c:\excelfiles) but you don't know the name of the file in that directory. Is that right? And you want to find the name of the file, but you don't want to ask the engineer what the name is. Am I still on the right track?

If so, this isn't a programming problem. It's a problem in communication. The engineer knows the name of the file. The program doesn't know the name. The program needs to know the name. But you don't want to ask the engineer what that name is. Really, I don't see how you can expect us to tell you how to do that.

Or, is there some information you haven't given us? For example, will the file always be the only file in the directory? Will it always be the most recent file? Is there some other fact about the file that can be used to identify it?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Mike,
If I've understood it right, you know the path to the directory (c:\excelfiles) but you don't know the name of the file in that directory. Is that right? And you want to find the name of the file, but you don't want to ask the engineer what the name is. Am I still on the right track?
Yes

If so, this isn't a programming problem. It's a problem in communication. The engineer knows the name of the file. The program doesn't know the name. The program needs to know the name. But you don't want to ask the engineer what that name is. Really, I don't see how you can expect us to tell you how to do that.

The file, will be there, and only will be one excel file, once i have processed that file, i will delete that excel file, and leave that folder "excefile" empty for the next time a new file is created and located there to be process.
I need something to go to that folder and read the name of the existing file, how? don't know, why should i ask the Eng for that filename ?

Or, is there some information you haven't given us? For example, will the file always be the only file in the directory?
Yes

Will it always be the most recent file?
Yes
Is there some other fact about the file that can be used to identify it?
It is just an excel file, it could be an excel file from Ms excel 2003, 2007 or 2010
Thanks a lot

 

[Mike Lewis]

The file, will be there, and only will be one excel file, once i have processed that file, i will delete that excel file

Excellent. That is the piece of information we need.

So, now it is easy. You just need to do like this:

ADIR(laFiles, "c:\excelfiles\*.XLS")
lcFile = laFiles(1, 1)

lcFiles will contain the required filename. If you only want the stem, apply JUSTSTEM() to it, which you already know.

Mike






__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Thanks Mike a lot, i will give a try an get back to you if i need something else and if you can
Regards

 

[Olaf Doschke]

As I already said, use ADIR and a file skeleton "*.xls?", that's the second parameter of ADIR. More precise now:

IF ADIR(laFiles, "c:\excelfiles\*.xls?")>0
   * There are files to process, at least "c:\excelfiles\"+laFiles[1,1]
ENDIF

You don't get around asking for the sheet, still, if there is more than one.

Bye, Olaf.

 

[Olaf Doschke]

One more thing, you apparently didn't get right:

Import From (m.lcTemp) Type Xls && or XL5

* Rename Field Names to better ones
For lnloop = 1 To Fldcount()
lcOldName = Field(m.lnloop)
lcNewName = Strtran(Alltrim(Transform(&lcOldName)),' ','')
Alter Table Dbf() Rename Column &lcOldName To (m.lcNewName)
Next

1. The first line with IMPORT creates a DBF
2. You remove all spaces in field names with lcNewName = Strtran(Alltrim(Transform(&lcOldName)),' ','')
...but there will be no spaces in field names. So you do fldcount ALTER TABLES altering nothing, that rewrites each import DBF N times and will make that process take much longer. And nothing is changed.

Remember: IMPORT is generating a DBF. That DBF exists since that line. It cannot have spaces in field names, as spaces in field names are illegal, wouldn't work. IMPORT will create field names with underscores instead of spaces. If spaces wouldn't be mended automatically with IMPORT, the DBF wouldn't be generated at all, the error would occur with the IMPORT and you wouldn't be able to alter field names afterwards.

There are several more things I could address, but that part of your code would make it slow in case of larger excel sheets.

Bye, Olaf.

 

[TamarGranor]

I know the thread has gone way past this, but figured I'd post this link to an article I wrote about the JUSTx() and FORCEx() functions:

http://www.tomorrowssolutionsllc.com/Articles/Parsing and Building File and Path Names.pdf