cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
terapin
Level VI

How do I extract excel filename from a user-selected file?

Folks,

 

I can't seem to figure out how to extract the original file name of an Excel workbook that a user selects.  After selecting the appropriate file as shown below, the actual file name I get is the worksheet name (sheet 1), not the original file name (test_data.xls). I'm ultimately trying to rename the originally selected excel file and need the file name, not the opened worksheet name, to accomplish this. Any ideas how I can accomplish this?

 

dt = Open(

  Pick File( "Select Excel File", "$dir_rawdata/", {"All Files|*"}, ),

  Worksheets( "1" ),

  Use for all sheets( 1 ),

  Concatenate Worksheets( 0 ),

  Create Concatenation Column( 0 ),

  Worksheet Settings(

  1,

  Has Column Headers( 1 ),

  Number of Rows in Headers( 3 ),

  Headers Start on Row( 1 ),

  Data Starts on Row( 4 ),

  Data Starts on Column( 1 ),

  Data Ends on Row( 0 ),

  Data Ends on Column( 0 ),

  Replicated Spanned Rows( 1 ),

  Suppress Hidden Rows( 1 ),

  Suppress Hidden Columns( 1 ),

  Suppress Empty Columns( 1 ),

  Treat as Hierarchy( 0 )

  )

);

 

fname.old = dt << Get Name;

2 ACCEPTED SOLUTIONS

Accepted Solutions
anders_bankefor
Level III

Re: How do I extract excel filename

Hi,

I've managed to extract excelfile name using the word() function:

for example:

Excelfile = pick file ("Select your CSV File(s)", "", {"JMP Files|csv", "All Files|*"}, 1, 0, "", "multiple" );

For ( [logic],

  SampName = Word( -1, Excelfile , "/" );

  ExcelName = Word( 1, SampName, "." );

  file contents = Load Text File( Excelfile );

//use the file cotents to phrase through your rows

)

the ExcelName will give you the name of your excelfile

cheers

View solution in original post

terapin
Level VI

Re: How do I extract excel filename

Thanks folks for your suggestions,

I was playing around and realized I needed to define a data table from the file selected in the Pick File function: dt.select = Pick File( "Select Excel File", ...... Once I did that I was easily able to parse out the file name using the Word function. 

MS, thanks for the clever way of extracting the file name from the Source property.  That was a really clever and learning about the use of -1 Word was very helpful.   

View solution in original post

4 REPLIES 4
anders_bankefor
Level III

Re: How do I extract excel filename

Hi,

I've managed to extract excelfile name using the word() function:

for example:

Excelfile = pick file ("Select your CSV File(s)", "", {"JMP Files|csv", "All Files|*"}, 1, 0, "", "multiple" );

For ( [logic],

  SampName = Word( -1, Excelfile , "/" );

  ExcelName = Word( 1, SampName, "." );

  file contents = Load Text File( Excelfile );

//use the file cotents to phrase through your rows

)

the ExcelName will give you the name of your excelfile

cheers

ms
Super User (Alumni) ms
Super User (Alumni)

Re: How do I extract excel filename

The file name is part of the "Source" script. I think this works (assuming POSIX path):

fname.old = Word(-1, Arg(dt << Get property("Source"), 1), "/");

terapin
Level VI

Re: How do I extract excel filename

Thanks folks for your suggestions,

I was playing around and realized I needed to define a data table from the file selected in the Pick File function: dt.select = Pick File( "Select Excel File", ...... Once I did that I was easily able to parse out the file name using the Word function. 

MS, thanks for the clever way of extracting the file name from the Source property.  That was a really clever and learning about the use of -1 Word was very helpful.   

anders_bankefor
Level III

Re: How do I extract excel filename

Thats a good idea, it all depends on what your script should do. Personally I had the need of reading in several excel files at once, therefore I stored the files into a list and phrased them using for-loops.

Excelfile = {}

Excelfile = pick file ()...

best of luck!