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

Question

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!

Excel_import_example_file.xlsx

txnelson · Answer

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( "\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 );

Jeff_Perkinson · Answer

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?

KPietro · Answer

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]