topic Re: How do I extract excel filename in Discussions

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

terapin — Tue, 02 Jan 2018 16:18:48 GMT

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(

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;

Re: How do I extract excel filename

anders_bankefor — Mon, 21 Dec 2015 23:56:57 GMT

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

Re: How do I extract excel filename

ms — Tue, 22 Dec 2015 00:01:30 GMT

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), "/");

Re: How do I extract excel filename

terapin — Tue, 22 Dec 2015 16:50:55 GMT

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.

Re: How do I extract excel filename

anders_bankefor — Tue, 22 Dec 2015 19:09:14 GMT

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!