382601_en-US

liliconcom

How can I import excel sheets that will reference the Excel workbook name when they are imported to JMP

I have a bunch of excel files, about 2 years worth, which are compiled monthly. I would like to import a sheet from each workbook into JMP. Unfortunately the date timestamp is only referenced in the title of each workbook and not anywhere on the sheets that I want to import. Is there a anyway to have the workbook title imported with the sheet?

Thanks

Data TableRe: How can I import excel sheets that will reference the Excel workbook name when they are imported to JMP

The Open() function for opening Excel workbooks has an element called "Worksheets" that can be set within the open.  Below was taken from the "Source" entry for a worksheet input into JMP.  It shows the name of the worksheet to read in

     worksheets( "Sheet1" )

Open(
 "$DOCUMENTS/Discussion Group/Data wrangling .xlsx",
 Worksheets( "Sheet1" ),
 Use for all sheets( 1 ),
 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( "-" )
 )
)

Re: How can I import excel sheets that will reference the Excel workbook name when they are imported to JMP

Hi, @fionaweston.

 

Assuming you are using JSL to import the data, you could store the file name (or any portion of the file name) in a variable that could be used to populate a new column. 

 

filepath = "$SAMPLE_IMPORT_DATA/Team Results.xlsx";
file = Word( -2, filepath, "/." );<BR />
dt = Open(
 filepath,
 Worksheets( "Grouped Team Results" ),
 Use for all sheets( 1 ),
 Concatenate Worksheets( 0 ),
 Create Concatenation Column( 0 ),
 Worksheet Settings(
  1,
  Has Column Headers( 1 ),
  Number of Rows in Headers( 1 ),
  Headers Start on Row( 3 ),
  Data Starts on Row( 4 ),
  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( "Workbook Name", Character, Set Each Value( file ) );

Alternately, you can automatically place the desired information into the Excel file directly.  See this post for instructions. 

 

Hope that helps.

Re: How can I import excel sheets that will reference the Excel workbook name when they are imported to JMP

@fionaweston how are you importing these sheets? Multiple File Import has a checkbox to add file names to a column that is added to the final data table. You can create a similar data table column if you are manually concatenating files using the Concatenate platform. The file name for each imported sheet will be the title of the file which, based on your description, should include the file timestamp.

 

 

Re: How can I import excel sheets that will reference the Excel workbook name when they are imported to JMP

Jeff,

Multiple file Import has the feature I need and that you have highlighted (add a file name column, which I need because the name of each workbook contains the date ex "04/21 Metrics"). However when I use Multiple file Import will only give me back the first sheet of each workbook and I need the 3rd sheet in each of the workbooks. Is there a way that I can get the 3rd sheet from each workbook using Multiple file Import ? I have about 20 or so workbooks.

Conversely,

Using the File/Open, I can call the correct worksheet (sheet 3) from each workbook. So now I have 20 of the correct worksheet but I cannot tell which date they come from because this method does not have an option to create a source column.

 

Thanks

Fiona

Re: How can I import excel sheets that will reference the Excel workbook name when they are imported to JMP

Wendy's response shows a simple way to add the source column as the multiple file import creates.

Using Files in Directory() to get the files, and then a simple For() loop reading in one file after another, with each one adding the column like Wendy illustrated, is a very simple approach to use.

Re: How can I import excel sheets that will reference the Excel workbook name when they are imported to JMPThanks Wendy