Import Excel data where not all the information is found in a single row or column

[#1]

I am trying to import an ExceI data file that has the name in Row 1 but the actual data column headers start on Row 7 and data starts in Row 8 (please see attached file).  I would like to have the imported data look like this:

Name       Date               Flow-acft         Concentration-ug/L (25)   Load- metric tons (25)

S382_P    06/18/2015     90.890579         93                                    0.010426405

Does anyone have suggestions on how I can read in this type of data so that the station name is associated with the data?  Please let me know.  Thank you!

[#2]

If you use the Excel Wizard, it is a simple matter to do this. In JMP, go to
File==>Open
Then navigate to the location of the Excel spreadsheet.
Click once on the file you want to read in, to highlight it.
Then go to the lower left area of the navigation window, and click on the drop down arrow next to Open.
Select "Use Excel Wizard"
In the Wizard, you will be able to select the row that has the column headers(6)
and the row where the data start(7)

[#3]

Jim, the instructions provided are OK if all data is contained in separate columns but I have a different file layout. In the attached file, you will see that the station name appears only a single time in Row 1, while the headers start in Row 7 and the data in Row 8.  The station names are "stacked" on top of the data headers and columns.  Please let me know how I can read in the station name, then associate that station name to the appropriate header/data.

In the attached example file, data is listed for 4 stations and the names of these stations are found in the Excel file addresses: A1, I1, Q1, Y1 (highlighted in yellow).  For the first station in the file, the station name is S382_P (found in A1), and the data for that station is found in columns A - H (header starts in Row7 and data on Row8); the second station is S382_C (found in I1) and the data for that station is found in columns I - P, etc.  I would like to have the imported data to look like this when I make it a JMP datafile:

Name          Date           Flow-acft         Concentration-ug/L (25)   Load-metric tons (25)   more colums . . .

S382_P     06/18/2015    90.890579        93                                    0.010426405 

[#4]

Sorry....I misinterpreted your original question.  Below is a script that works with the sample data table you provided.  Just change the path in the Open() function to the path to your data table, and then run the script.

Names default to here(1);
dtOrig = Open(
	"<path to excel file>\Excel_import_example_file.xlsx",
	Worksheets( "example" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 0 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 1 ),
		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( "-" )
	)
);

// Create a new table with the Column names on row 7 from the original
dtResult = New Table("Final");
dtResult << New Column("Name",Character);
col=1;
While(col>0,
	dtResult << New Column(dtOrig[7,col]);
	If(left(dtOrig[1,col+1],5)=="Name:",col=-1);
	col=col+1;
);

// Now Populate the Table
For( col = 1, col <= N Cols( dtOrig ), col++,
	If( Left( dtOrig[1, col ], 5 ) == "Name:",
		dtResult << Add Rows( 1 );
		resultCol = 1;
		Column( dtResult, resultCol)[ N Rows( dtResult ) ] = Trim( Substr( Column( dtOrig, col )[1], 6 ) );
	);
	resultCol = resultCol + 1;
	Column( dtResult, resultCol)[ N Rows( dtResult ) ] = Num( Column( dtOrig, col )[8] );
);

close( dtOrig, nosave );

[#5]

Thanks . . . but the script works if I have only 1 line of data. But it doesn't seem to work with multiple lines of data. Sorry, I sent a short file as an example but the data files I work with have multiple lines of data. I attached the longer version of the file to this email (pls see worksheet "example"). It would be great if you could assist me with reading in multiple lines of data.
[cid:image001.png@01D51188.A1E017A0]

[#6]

It looks like you might have attached an image, but for some reason it didn't work. Can you go back and edit your post to try to attach the image again?

[#7]

If the multiple lines you are referring to, are additional lines after line 8, that are of the same structure as line 8, then the following script will work

Names Default To Here( 1 );
dtOrig = Open(
	"<Path to File>\Excel_import_example_file.xlsx",
	Worksheets( "example" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 0 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 1 ),
		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( "-" )
	)
);

// Create a new table with the Column names on row 7 from the original
dtResult = New Table( "Final" );
dtResult << New Column( "Name", Character );
col = 1;
While( col > 0,
	dtResult << New Column( dtOrig[7, col] );
	If( Left( dtOrig[1, col + 1], 5 ) == "Name:",
		col = -1
	);
	col = col + 1;
);

For( theRow = 8, theRow <= N Rows( dtOrig ), theRow++, 
// Now Populate the Table
	For( col = 1, col <= N Cols( dtOrig ), col++,
		If( Left( dtOrig[1, col], 5 ) == "Name:",
			dtResult << Add Rows( 1 );
			resultCol = 1;
			Column( dtResult, resultCol )[N Rows( dtResult )] = Trim( Substr( Column( dtOrig, col )[1], 6 ) );
		);
		resultCol = resultCol + 1;
		Column( dtResult, resultCol )[N Rows( dtResult )] = Num( Column( dtOrig, col )[theRow] );
	)
);
Close( dtOrig, nosave );