Data Layout Conversion (Advanced Rows to Column ?)

Hi,

I have a large CSV (comma delimited) which has data laid out in rows and each row contains about 10,000 separate inputs. This data is an output from a Simulink Simulation where variables have been saved on to the workspace saved as a .mat file then converted into csv (using a tool my colleague wrote). This tool was a modification from a script provided by mathworks therefore quite complex to modify to save the data in the format I need.

Ive attached a spreadsheet with this post which contains the format the data currently is (input) and the format I need it to be in (output). As its an output from simulink some data are multidimensional arrays therefore not all the data can be converted straight from rows to columns. I have used the "Paste Special" and transposed the data for now, but I will need to do this repeatedly therefore looking for a better solution i.e. a vbscript that can do this for me.

Any advice/solution would be appreciated.

Thanks.

Edit: Forgot to mention, the data might always not be in the same format. For e.g. sometimes the data might just be in the following format without any multidimensional arrays..
Header 1
1,2,3,4,5...
Header 2
3,4,5,6,7..
Header 3.. and so on.

Where you have Header 1, Header 2, etc., what do these headers really look like?

These header can be anything as they are whatever I set the Simulink workspace variables as.

Currently they are I_ResolverValidity or I_<xx> for Inputs and O_MonitorFailed or O_<xx> for Outputs. If it helps they can be fully set as a string...

I should have also mentioned the input are all integers. Boolean are represented as 1 & 0s.

I was thinking of character comparison for each row and depending on the character type, decide which row to begin the column from ? That way I could decide if its a header (first row & x column) , array count [0] , [1] ... etc (second row & x column) and finally integer 1,-2,3.. etc to begin from third row and x column. Hope this makes sense.

It makes sense, but I'd like to see some more realistic examples.

I'd like to help you with this but, if I work with what you have provided (even with an explanation), and it doesn't work with your real life data, you're on your own.

I don't want to go through the process twice.

Regards, TMS

Maybe :

Please Login or Register  to view this content.

Originally Posted by karedog

I have modified my code (correction to one line of code), please check it out. I have test with your sample files at post #1 and #6, and to me, it seem to work very well.

Works with my sample csv as well now. Will use it on the actual data and report back. Quite confident, it'll work.

Cheeers!

Alternative.

This will read the data directly from cvs file.

Please Login or Register  to view this content.

I don't know ... you turn your back for 5 minutes

Looks like you are in capable hands so I will bow out gracefully.


Regards, TMS

Always run (new, untested) macros on a copy of your data, not the live file ... or make a backup copy of the live file first.

Please Login or Register  to view this content.

Regards, TMS

You're welcome. Thanks for the rep.