Stack side by side tables from excel and add header columns

[#1]

I have been struggling for 2 days pulling data from excel into JMP in the correct format.

I have hundreds of excel sheets, but the data is disorganised. There are many tables next to eachother (hundreds) in an excel sheet.

I want to stack the data across the different tables, but also make new columns with the associated header data. I have only been able to achieve this on one table, but can't replicate it across all tables, or sheets across different excel docs. The sheets also tend to have different tab names, but if i can just get it to work on one excel document that would be very good.

[#2]

Can you provide an example excel file? And if you have few different types of files, having more examples will make it easier to provide answers.

[#3]

Hi Jarmo,

Absolutely, i've attached some dummy data. There can be up to maybe 200 tables side-by-side, and i have added different worksheets within the same excel file just to show the tab names change, but actually these sheets are often in separate excel sheets within a folder. The yellow highlights are where new headers would be needed i believe, and the orange highlight is just there because in the actual data set there are a few hundred empty rows before this data starts below it.

Ideally i'd like to bring in all of the parameter (parameters 1-19, there are more when you scroll down in the excel) under a parameters header, and the values under a value header, and add columns to show that this data belongs to header1/2/3 (and new 4 ) values by adding columns as in the original post.

Ultimately the two sets of data (VCL and VL header columns) i will also be looking to save similarly with some identifier from header1/2/3 (and 4).

Let me know if you have any thought, it would be much appreciated!

Thanks

Lewis

[#4]

It won't be easy, as to be honest, this is horribly formatted data. Going to the source and fixing the data there would be the best option but it is rarely an option.

I would start by pulling the data into JMP as raw as possible, so something like this

And from this I would start building a script as this data has too many issues to deal without using JSL (you can most likely get quite far without JSL, but it with JSL it will be much easier). Fixing the first line of headers is most likely the first thing you should start with as then you can start more easily separating the "tables" and their data