- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
JMP14 How can i import multi excel files with defined sheet?
Dear All
there is a function of JMP14 which can import multi excel files together and stack them automatically.
But it also choose the first sheet in excel file, can i define the sheet in the excel which include more than one sheet?
thanks!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP14 How can i import multi excel files with defined sheet?
hi this is part of the script.
dir = Pick Directory( "Select a directory" );
//get all file names under the folder
files = Files In Directory( dir, Recursive );
nfiles = N Items( files );
For( i = 1, i <= N Items( files ), i++,
fullfile = dir || files[i];
dt = Open(
fullfile,
Worksheets( "Intensity" ),
Use for all sheets( 0 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
)
);
dt << New Column( "AbbCon1", character, width( 8 ), <<set each value( files[i] ) );
If( i == 1,
(dt1 = dt ; dt1 << Set name( "Tem" ))
);
If( i > 1,
dt1 << Concatenate( dt, "Append to first table" );
dt << closewindow;
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP14 How can i import multi excel files with defined sheet?
You can select the worksheet you want, when you select the file to open, and then click on the down arrow for the "Open". and select "Open Selected Worksheets".
If you choose the "Use Excel Wizard" it will allow you to select multiple sheets to open at the same time.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP14 How can i import multi excel files with defined sheet?
Hi
thanks a lot for your reply.
My questions is about new function " import multiple files" in JMP14.
I have more than 10 same structure Excel files which i want to extract some data and stack them together.
For each excel file, there are more than one sheet.
When i use this function, it also extract the data from the first sheet, how can i get the data in second sheet in each excel file?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP14 How can i import multi excel files with defined sheet?
Hi,
Is there any solution for the problem described by #juna1
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP14 How can i import multi excel files with defined sheet?
thanks, Ordon.
I already use the "for" funcition to do it now.
and give up the function"import multi files ".
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP14 How can i import multi excel files with defined sheet?
Hi juna1,
is that mean you have to import every single file from given directory manually? It's a pity there is no option to do it via "import multi files" function and jsl.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP14 How can i import multi excel files with defined sheet?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP14 How can i import multi excel files with defined sheet?
No, i use "for " loop statement to do that. it`s ok now. thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP14 How can i import multi excel files with defined sheet?
Hi @juna1
What was your final solution to this problem? You mention that you use the "loop" statement. Would it be possible to provide an example of your code?
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP14 How can i import multi excel files with defined sheet?
hi this is part of the script.
dir = Pick Directory( "Select a directory" );
//get all file names under the folder
files = Files In Directory( dir, Recursive );
nfiles = N Items( files );
For( i = 1, i <= N Items( files ), i++,
fullfile = dir || files[i];
dt = Open(
fullfile,
Worksheets( "Intensity" ),
Use for all sheets( 0 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
)
);
dt << New Column( "AbbCon1", character, width( 8 ), <<set each value( files[i] ) );
If( i == 1,
(dt1 = dt ; dt1 << Set name( "Tem" ))
);
If( i > 1,
dt1 << Concatenate( dt, "Append to first table" );
dt << closewindow;
);
);