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

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

KPietro — Thu, 16 May 2019 22:21:52 GMT

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!

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

txnelson — Thu, 16 May 2019 22:29:13 GMT

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)

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

KPietro — Mon, 20 May 2019 14:05:32 GMT

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

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

txnelson — Mon, 20 May 2019 15:18:10 GMT

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 );

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

KPietro — Thu, 23 May 2019 21:01:13 GMT

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]

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

Jeff_Perkinson — Thu, 23 May 2019 21:35:13 GMT

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?

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

txnelson — Fri, 24 May 2019 03:08:56 GMT

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 );